Convert CSV Files to Excel (xlsx format) in Power Automate

Learn how to leverage Power Automate's out of the box actions & Office Scripts to convert CSV documents into Excel (xlsx).

Code for the Office Script:
[starts]----------------------------------------------
function main(workbook: ExcelScript.Workbook, lineCSV: string[]) {
const alllines = lineCSV;
let counter = 1;
for (let line of alllines) {
counter++;
}
}
}
[ends]----------------------------------------------

Follow the steps in this link if you don't see the "Automate" tab:

Please note that Office scripts will time out if you have more than 1000 rows to insert.

[PowerGI]

Code for the Office Script:

function main(workbook: ExcelScript.Workbook, lineCSV: string[]) {
let selectedSheet =
const alllines = lineCSV;
let counter = 1;
for (let line of alllines) {
if (line.includes(", ")) {
let items = line.split(", ");
selectedSheet.getRange("A" + counter+":G"+counter).setValues([[items[0], items[1], items[2], items[3], items[4], items[5], items[6]]]);
counter++;
}
}
+ counter),
}

[elizabethforney]

This works great for a large csv file and is much more efficient than using update a row in a table or add a row into a table. Thank you!!

[aadelaboobacker]

Thankyou so much for this idea, it works A lot faster than add row into a table in Power Automate.
Even though we can use concurrent control and speed up the row addition, it constantly bumps into retries (maybe because two parallel actions are trying to input data into same row in the table)

Appreciate your help!!

[guyin]

remarkable work! thanks for sharing! can't wait to have Automate in Excel enabled by my admin !

[solodev]

Thanks for sharing this. It’s a very efficient/creative process.

[josefinagaccio]

I have an error when I run the script: Line 5: "alllines is not iterable", do you know how to solve it?

[simonec]

Is it possible to do the same flow without reading csv from attachment but from a folder?

[erinann]

What if you have dynamic columns. I have a file that can come with 10 columns or max 50 columns. Is there a way to do columns dynamically as well?

[LucasDoseck]

This has worked for me for over a year and last week something changed and now my first row shows the header column a and then the first column of the second row in column b

[rodrigonkws]

Thanks a million, it helped me a lot!!! Awesome!!

[pradipta]

Great video to help ..but i suspect if the input content have multiple line ( contain new line inside text of individual item). In this cae it will work ? Beacse you are splitting each line with Newline

[gregoirepesenti]

hi, my csv is on one column, with delimiter, can we do the same ? thanks

[guyboswell]

Hmmm!?? Seems like it should work. The header row creates OK except first column gets a leading [" and last column gets a trailing \r" and I don't get any data rows added. No error message. Any ideas?

[techguru]

Hi, This is really helpful, but the issue with me is the last column in csv file from SAP contains blank spaces it's not performing the desired action, when I remove that column manually and trigger the flow it's working perfectly.

Any solution to ignore last column or space from CSV file? Thank you in advance!!

[jessicaroche]

This process works well for me with one exception. I have one column that the data as a comma and it throws the rest of the cell off because of it. There are double quotes surrounding the info that contain the additional comma. How do I do an additional split for just the one column, it is always in the same position but i have no other delimitator. Its basically a location "city, state" value.

[diegoinfante]

Estoy muy agradecido, muy buena solución.

[ProrokMeow]

Hi, thank you for very helpfull video!

What can I change in the office script to use it for different files?
For example 1 file contains 2 columns and 2 file have 20 collumns, how I can do only required number of columns?

[roshak]

Thank you so much, this was super helpful!!! however for some reason my characters have quotation marks at the beginning and end of every cell, like they were a part of the string. Would you know how to remove them?

[aaronczupryn]

Did anyone get a 504 BadGateway error? I am getting one, and the research I have done says it could be that the data set is too large. How big of a file can this handle?

[Naveenkumar-dbpj]

Hi, after converting csv to excel I need to append the converted excel to a master excel through power automate. But while appending each time it leave a blank row for every run and the coloumn no.7 is always empty. Is there anything need to change in code. Please let me know asap. Thanks