Title: Import CSV File 2.0
Description: This flow allows you to import csv files into a destination table and uses office script for parsing the data.
This is my version 2.0 of csv import and is much simpler and smaller than my original csv import.
You may need office 365 for business to make office scripts available.
You could create a JSON output to access the columns dynamically. In this flow we simply begin to write our data columns by accessing them via column indexes (apply to each actions).
You can easily access the columns in the for each loop by using the column indexes . . . [0],[1],[2], etc...
Column 1 will be index [0], column 2 will be index [1], and so on.
Detailed Instructions:
The only thing to modify is the get file content and trigger and the return data rows loop (apply to each).
Import the package attached below into your own environment.
You need to change the action where I get the file content from, such as sharepoint get file content, onedrive get file content.
When you change the get file content action it may remove any references to it, this is where it belongs though.
This var file content is important as it converts your file content into readable text data as it is assigned to the variable.
You will also need to create the office script within a blank excel file on a sharepoint site.
Questions: If you have any issues running it, most likely I can figure it out for you.
Anything else we should know: You can easily change the trigger type to be scheduled, manual, or when a certain event occurs.
You may end up with quotes or other characters within columns after the parsing. These can be replaced while processing each column for writing.
Use the replace formula while writing a column.
Replace(searchedString, “replace this”, “with this”)
Creating the office script:
Open a blank file in excel online
Go to Automate
All scripts
New script
In the script window paste this code making sure to replace all existing code.
//Code begin----------------------------
function main(workbook: ExcelScript.Workbook, csvdata: string[][]) {
// initialize the 2D array
let results: string[][] = new Array(); // rows array
// loop each row from the input array
for (let i = 0; i < csvdata[0].length; i++) {
// new row array
let columnarray: string[][] = new Array();
// split into column array
columnarray.push(csvdata[0][i].split(RegExp("(?:^|,)((?:[^\",]|\"[^\"]*\")*)")));
// add split columns array to rows array
results.push(columnarray[0].toString().replace("\r","").slice(1, columnarray[0].lastIndexOf(",")).split(",,"));
}
// removes rows
results.shift(); // headers row
console.log(results);
// omit blank lines on return data
return results.filter((value,index,array) => value[0] != "");
}
//Code end------------------------------------------
Then save the script.
The flow is now ready to run. Change the trigger to your preference.
The file must be a CSV file format.
