Answered

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

Like (1) Share

Report

Posted on 15 Jan 2021 17:27:14 by KayBains

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

All responses (14)

Answers (1)

brtkurt 7 on 09 May 2024 at 09:39:56

Like (1)

Report

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

Thanks!! It solved my problem
GF-14081615-0 62 on 03 May 2024 at 15:28:58

Like (1)

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

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 :
Trigger When a file is created. No need to explain, it is my source of data
Then Initializing a variable for the table name (could be anything other than a variable, really),
copy the file, just to have history if anything goes aloof.
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 :
We list the rows in the table we just created

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.
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 7 on 03 May 2024 at 15:12:14

Like (0)

Report

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

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

How did you figure it out?
GF-14081615-0 62 on 25 Aug 2023 at 14:56:14

Like (1)

Report

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

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
RTbmart 20 on 25 Aug 2023 at 12:57:11

Like (0)

Report

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

Hi @gr962x,

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

Late response, but hope this helps!
gr962x 2 on 16 Feb 2023 at 18:32:53

Like (0)

Report

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

Hello,
It works, however it is only copying 256 lines in the created .csv file. Any idea why and how to resolve it?
ImAPilot 4 on 15 Jun 2022 at 14:27:36

Like (0)

Report

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

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.
Community Power Pla... on 30 May 2022 at 07:44:56

Like (1)

Report

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

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
Community Power Pla... on 12 Aug 2021 at 19:45:36

Like (0)

Report

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

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.
tom_riha 10,085 Most Valuable Professional on 12 Aug 2021 at 19:24:13

Like (0)

Report

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

Hello @tabrams ,

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.