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.
Thank you so much for this very handy flow @juresti! One question, I'm trying to only return data from the script where a certain column contains data.
E.g.
A B C
1 cat
2 X dog
3 X mouse
4 hamster
Where the script would only pass rows back to Power automate where column B contains 'X'. I think that I may need to alter the script line:
return results.filter((value,index,array) => value[0] != "");
But am totally lost 😅
Hello @andrehamon
It is good to know you found this useful.
You are correct, that line needs to be modified for your return criteria.
This is how:
Value[0] means the value in column 1 or column A.
So Value[1] would be column 2 or column B and so on.
Simply set Value[?] of your column and equal to the value you are looking for.
Column B contains an X
Value[1] = "X"
Note that this is an equals evaluation, so it must be an exact match, no spaces or anything else in the column.
Amazing, was testing with data that used spaces instead of blanks so when I tried Value[n] != "" it wasn't working. Value[n] != " " has solved the issue!
how to get column size of csv file in office script??
Hi @Development
To get the length of a column specify the row then the column and get the length.
The line would look like this using my example:
let column_size = results[0][0].length;
The array can be confusing but it reads like so where 0 is actually row 1 or column 1:
results[row][column]