Converting excel file in one drive to PSV or CSV format file

[mahmud9935]

Dear community,

I have maybe simple question.  I need to build a flow which coverts excel file in one drive into the PSV or CSV file. Any ideas or connectors on how to do this. I found one solution in cloudmersive, but I think solution must be simple without involving 3rd party cloud services. maybe shall I code the shell file which runs on a schedule to make this conversion? Please share your experience and ideas, will be much appreciated.

Many thanks

Mahmud

[efialttes · Accepted solution]

Hi!

If you have Excel data store as table format in OneDrive, you can use 'List rows present in a table' action block, then assign its output as the input to "create CSV table".

Now, you can add  'Create file' action block, assign 'Create CSV table' output as its Content.

Hope this helps

[v-bacao-msft]

Hi @mahmud9935 ,

I have tried the following method to convert excel file to .csv. But the generated file does not seem to meet our expectations.

If you want such a feature to be supported by Power Automate，please consider voting for the following idea:

https://powerusers.microsoft.com/t5/Power-Automate-Ideas/Convert-a-CSV-file-to-Excel-format-using-Mi...

The method provided by @efialttes is a good option, please take a try and feel free to let us know if you have any questions about this method.

Best Regards,

[mahmud9935 · Accepted solution]

@efialttes  Your solution is brilliant. It works!

[mahmud9935]

HI @efialttes ,

This solution for me still incomplete. The file creates only the last row of the table, and other rows are skipped. For instance, if I have the table with 10 rows, the csv file is created only with 1 row from the last row taken from excel table. Any ideas?

Many thanks

Mahmud

[efialttes]

Hi again!

I am afraid the problem in you current flow design is the following: 'Apply to each' iterates all over your excel rows, in each iteration a row is processed and, a file is created. THis means if you have N rows, you are creating N files each of them with one single excel row converted into CSV. Since the File Name is always the same, in each iteration it overwrites the previous file file.

You can replace Apply to Each, and use"Create CSV table" action block instead. THen, assign "Create CSV table" output as the File content input on your 'Create file' action block

https://powerusers.microsoft.com/t5/Building-Flows/Filter-Query-and-CSV/td-p/200172

Hope this helps

[mahmud9935]

Hi @efialttes ,

Thank you for solution provided. It works to return csv file from excel table. But still my output content contains two strange columns in the csv file , called dataetag and ItemIntenalId which I dont need in the file. How to skip those two columns?

Many thanks

Mahmud

[mahmud9935 · Accepted solution]

Hi Everyone,

Finally I had found the solution how to convert excel file to any delimited format file. Below are the screenshots. The concept is to convert first the excel file into CSV format, later use the replace function to change the comma to any delimited character, I have used pipe character in my case. You can use any indicated in value section in "DelimitedChar" step below. Many thanks to all for your contributions.

Kind regards

Mahmud

[efialttes]

Hi again!

you can customize the Columns provided by "Create CSV table" output by playing with its 'Column' input, switching from 'automatic' to 'custom'. Then you can add whatever columns you need, keeping these two out of the result. You can see an example here:

https://powerusers.microsoft.com/t5/Building-Flows/Create-CSV-Table-Multiple-Rows-and-attaching-CSV-...

Finally, thanx for marking this topic as solved! THis way other community members facing a similar problem can find a solution faster. Please also remember you can also click on 'Thumbs up' in every answer from this current thread you found valuable... or even in all of them 😉

Thanx!

[Supersan123]

Hello Guys, I needed a flow to get my spreadsheet and generate a csv to be placed on ondedrive but I don't know why my flow generates the csv only up to line 255 and my excel has 680 lines, did this happen to you? how to solve for him to get all the lines? My csv contains data until 03/05/2020 but my spreadsheet contains data until today