CSV to Excel Power Automate and Office Scripts Any File Encoding - Free | Fast | Easy

Let me show you how you can use a Microsoft Office Script to convert your CSV into Excel and further extend it to handle Mac/Unix file encodings and implement a table for later use in your Cloud Flow. No need for a premium or 3rd party connector. Much easier than using select/compose and apply to each. No need to worry about quote encapsulated values as the Microsoft Team have handled this with a regular expression.

Microsoft Script Example

How to Create a New Excel File using Compose

00:00 Intro
00:44 Quick exploration of CSV's with different file encodings
01:26 Build our script using sample Microsoft Office Script
02:50 Building our Cloud Flow based on 3 sample files
06:09 Don't forget your file get file content!
07:01 Save, explanation of Logic and Test
08:30 Our Excel files but with Encoding problems demonstrated
09:06 Extend the script to handle file encodings and add a table
12:16 Looking at the newly converted CSV to Excel with encodings handled
13:00 Querying an excel file with the Table inserted during the Office Script Action
14:25 Summary and close

Additional Script for File Encodings (**NOTE** replace string GREATER with angled bracket as YT will not allow it in desc)

// Split each line into a row.
}

Additional Script for adding a table to your Excel File

// Get the first worksheet
// Create a table using the data range.

My previous attempt at converting CSV to JSON using Select/Compose and Apply to Each and an explanation of file encodings:

[MohammedKhan-rzgz]

Excellent workaround. This is a cleaner and faster way versus going through api keys for third-party services and the drama that comes with them. Worked beautifully for me

[indhumathi]

Too good, i cannot believe it would be this simple, i tried parsing CSV and write to excel and that took hours and this one less than a minute

[DavidWilsonNZ]

Awesome video. Very clear. Thank you.

Now... I've done excel.new and Excel has fired up in the browser window. I don't have the Automate tab... Is there something I need to enable or have the organisation license. I do have Automate scripts and they do run etc - so there's an organisation license for automate (however this works).

[robsongdev]

Awesome! You just unlock a new world of possibilities in my mind

[zachariecortes]

Thanks a lot for this very useful video ! This helped me so much !!!

[kaylapeixoto]

Hi Damion, I'm trying to convert an .xls file to .xlsx using Power Automate. I followed this tutorial earlier with great success so I'm hoping that you can point me into the right direction for this conversion as well.

[OlivierTravers]

Nicely done! It would be interesting to look at the timestamps of the generated files to see how fast this ingestion works.

[zaraaxon]

Thanks for the video really helpful. Is there a way of adding a header line to the Excel worksheet before adding a table?

[jdduf]

Thank you for this solution - my flow is failing at the last step with the following error message: Office JS error: Line 38: Range setValues: The argument is invalid or missing or has an incorrect format. Line 38 of the script is this: range.setValues(data); Any ideas as to what needs to change in my dataset, my csv, etc. to get this running? It's almost there! :)

[villog]

Hello. I get an error in run script step.

"message": "The file format is not recognized. Please check that the selected workbook is valid.\r\nclientRequestId: 497850f7-2d83-488a-9d37-b80828d66655",
"logs": null

[IvanLijnDiensten]

Hi Damien, this worked - after some sweat - just as promised, thanks a lot! One problem I run into and can't quite solve: we use the flow to save csvs from email to a Sharepoint location. Those csvs are (if I am not mistaken) Linux and sometimes contain LF-linebreaks within double quotes. This messes up the csv and resulting xlsx because it creates new lines where they shouldn't be. Would you know of any way to remove those line breaks within double quotes as to prevent this? Thanks already!

[VitoAD]

Just discovered your channel. We are going to start using Power Automate soon. I followed your instructions but the script failed to populate the new excel file. I noticed that the script on the link is different than the one you used. I created a new script using the code that you used. With that code the Flow work and the excel file was populated. The only problem now is that all my cell data has " " around them which didn't happen on your xlsx file. Any tips on fixing the script? Thanks

[sivagnanamc]

I tried similar steps for csv files in sharepoint. Value variables are not available in list folders in sharepoint, similarly filename without extension due to fact sharepoint stores info with extension.
I tried get file properties and filtered query for csv to get csv files only and selected body/value in loop but operation fails

could you put new video to do csv to excel conversion in sharepoint

[smarttausf]

Hello Sir,

This is really interesting, I have 5 cols and 1400 rows in the csv. It worked for me. only thing is, I wanted to append the rows in an existing excel file where I have previous data. So, added some codes:

let tbldata = sheet.gettable(‘data’)
let rowCount = tbldata.getrowcount() +1

One the range:
let range = sheet.getRangeByIndexes(index + rowCount, 0, 1, data[0].length)

this changes should work easily. but these small modifications give me a bad request of Gateway timeout.

how come this appending to existing file is that difficult instead of creating a new file?

[bconnaughton]

@DamoBird365, is there a way to do this when the CSV files are stored in Sharepoint rather than Outlook? There doesn't seem to be a way of getting the filename without extension on sharepoint.
Cheers

[Argentinian]

I have a CSV file with approximately 31 columns and 2700 records.
The file has the following characteristics:

Windows format (CR LF)
UTF-8-BOM encoded
It is structured as follows:
"Personal Name", "Enterprise ID", "Work Order", "External ID Number", "Size Code"

I made an identical copy of your Power Automate flow and copied the script, but it doesn't do anything at all. It keeps running the script for 40 or 50 minutes until it fails.

I need help, please, even if it's paid :(

[dancelmarieminoza]

very helpful video and was able to export the csv files. however, is there any way to email the excel file as an attachment? it doesn't seem to work if I select the "current item" as an attachment.

[uypdtwp]

Hi sir, this is a very brilliant solution on PA! But it doesn't work when the CSV is at 1mb or more. Do you have the same problem? :)

[IshwariyaSainathan]

Hi Damion, Thank you so much. I created the flow using your video and it all works fine but the thing is according to my requirement the csv file may or may not get gernerated for the current day depenind on data availability, in case if csv file is not generated, the "get file content" runs infinitely. I am new to power automate.Any help would be much appreciated

[facun_OK]

Many thanks for this great video and knowledge sharing! I have tested this script and works great. However I would like to know if there is a way to apply this solution with csv files with +10k records as it seems this solution handles small files. I believe there is a limitation in Power Automate for cases like this but is there any known solution for situations like this? Thanks in advance.