Convert xlsx. or xls files to csv files in a Sharepoint Document Library

[KayBains]

Hi

Would anybody have a template or know how one would convert xlsx. or xls files to csv files in a Sharepoint Document Library.

When a xlsx or xls file is uploaded into a Shareppint Document Library - it is converted to a csv file

[tom_riha · Accepted solution]

Hello @KayBains ,

there's an action 'Create CSV table' in Power Automate that will convert any array into a csv format.

You should 'List rows present in a table' (the original Excel file must have the data inside a table) to get all the rows from the Excel file, 'Convert CSV table' to convert the output from the 'List rows...' into a csv format, and then create a file from the data provided by the conversion.

[Heliant]

what if you do not have table in xlsx. how you going to proceed? And also what if the file is in sharepoint?

[RobFischer]

This only works with .xlsx files. There is no way to work with a .xls file in microsoft power platforms and they offer no way to convert with flow. This needs to be addressed!

[Anonymous]

Hi all,

I tried this and it converted the xlsx to csv, however about 200 of the records in the original xlsx were missing in the csv output.  My Excel data are formatted as a table.  Any help would be appreciated!

[tom_riha]

Hello @Anonymous ,

try to go to the 'advanced options' in the 'List rows in a table' action and put some number in the 'Top Count' field, e.g. 1000. I think the action will return only 100 rows by default.

[Anonymous]

Thanks so much!  I will give this a try in a few minutes.  Per the action, the default is "all" but maybe that's not accurate.

[Anonymous]

Hi

For me converting excel to csv works if i create table in excel but while conversion it add 2 new columns 

@OData.etag

ItemInternalId

How should i remove them before writing the output file to my azure storage.

Regards

Shilpi

[Woody0]

I believe Microsoft will eventually stop supporting .xls format, as Excel began using the .xlsx format with Excel 2007. It doesn't make sense to invest development dollars in legacy formats, so I wouldn't look for a native Microsoft .xls connector any time soon.  That being said, there are many legacy systems still putting out the .xls format, and most of those do NOT support using Excel Tables as many OOB connectors require.

There are several commercial providers that have single step actions to convert; I have been using one of them for a couple years now to convert .xls to .csv with very good results.  Some providers include Plumsail, Encodian, and Cloudmersive.  Most of these you can find by just scrolling through the list of available actions when building your flow.

[gr962x]

Hello,
It works, however it is only copying 256 lines in the created .csv file. Any idea why and how to resolve it?

[szefco]

If you don't have table in excel file, then the solution is (answered was powered by Chat GPT 🙂 ) 

To convert an XLSX file into a CSV file using Power Automate (formerly known as Microsoft Flow), you can follow these steps:

1. Begin by creating a new flow in Power Automate and select "Automated - from blank" as the flow trigger.
2. Add a "Get file content" action and configure it to get the contents of the XLSX file you want to convert.
3. Next, add a "Compose" action and set its input to the output of the "Get file content" action. This will convert the XLSX file content to a binary format that can be manipulated in subsequent steps.
4. Add a "Create file" action and set its "File name" and "File content" properties. For the "File name" property, you can use an expression to concatenate the original file name with the ".csv" extension (e.g., "myFile.xlsx" becomes "myFile.csv"). For the "File content" property, set it to the output of the "Compose" action.
5. Finally, save and test your flow to ensure that the XLSX file is successfully converted to a CSV file.

Note that depending on the size of the XLSX file, you may need to add additional steps to split the data into manageable chunks before converting it to CSV

[RTbmart]

Hi @gr962x,

I just encountered this same issue and found a solution from OptimalBI:

Late response, but hope this helps!

[atreyu]

Hello @szefco ,
Your solution does not work for me : when opening the .csv file, nothing is encoded correctly, see screenshot (I have not opened this file with Excel prior, here how it does behave on notepad++ :

When opening on Excel (web or Desktop), i have an error stating this is not a right format but the data is still there :

Here is my flow: 

Because we will be feeding an app with the .csv format, it has to be pristine.

@Heliant , did your find another solution ?
Do you have any other idea on how to convert an Excel file without table from xlsx to csv format ?

Thanks a lot

[brtkurt]

@atreyu I am facing the same issue is there any update on that issue? 

How did you figure it out?

[atreyu]

Hey !
I'm sorry, it's been a really long time but here is what we could achieve :
We had to incorporate a table into the CSV.
Yhe one I'm showing you is the test that worked, in production it's a more complicated flow, I did not want to explain everything here 😄

So, here is the little step by step :

1. Trigger When a file is created. No need to explain, it is my source of data
2. Then Initializing a variable for the table name (could be anything other than a variable, really),
3. copy the file, just to have history if anything goes aloof.
4. After that, we create a table in the original .xlsx file. I gave this table name the variable. Here is the Excel formula I used to determine the Table range :

=OFFSET('ColumnName'!A1,0,0,SUBTOTAL(103,'ColumnName'!$A:$A),10)

Be careful : we could do this because the xlsx file structure is always the same. The number of lines changes but not the number of columns.
Then :

1. We list the rows in the table we just created
   
   
   
2. We use the action "Create a csv table" from "Data operations" connector and we give in the "From" field the following formula: outputs('List_rows')?[body/value] 
   It allows you to get rid of any "Foreach" step.
3. Finally, you create a new File under SharePoint : the "File name" field will be any name you want with the ".csv" extension behind it, and the "File content" field will be the output of the "Create a csv table" action.
   
   That's how I done it, it is perfectible, but it worked with those tests 😉
   
   Have fun flowing !

[brtkurt]

Thanks!! It solved my problem