Save XLSX Excel all worksheets as csv 05-14-2021 10:36 AM - last edited 04-17-2023 13:05 PM

[juresti]

This flow saves all xslx excel files and each worksheet in the files as csv files.

Updated to save files named as filename + worksheet name.

Choose the source folder and then choose the destination folder and run or schedule the flow to run.

Explanation

If you work with excel online there is no option to save as csv. You would have to open each file on the desktop and save each worksheet as csv.

This flow will do this for you all online and it will even save each worksheet in the xlsx file as csv.

It is very simple to use.

It will require you to first make an office script.

Making the Office Script

Open excel online with a blank file or any xlsx file.

The format must be xlsx for the office scripts to be available.

Go to automate

Click all scripts

Make a new script and name it as you like

Open the script and you will see an empty main function

Copy this code inside the main function

// declare variables
let wbook = workbook;
// the workbook filename
let sheetnames = new Array({});
// worksheets in the work book
let sheets = wbook.getWorksheets();
// array of items
let items = new Array({});

// get each worksheet data set
for (let i = 0; i < sheets.length; i++) {
// try catch - errors when a sheet is blank and other possible errors
try {
items[i] = sheets[i].getUsedRange(true).getTexts();
sheetnames[i] = sheets[i].getName();
}
catch (any) { }
}

// return the worksheets array
return [items,sheetnames];

Save it and now you can close excel

Note: The office script is not embedded into the file you opened. Opening the file is the only way I know to get to the office scripts.

Prepare your flow steps

There are a couple of steps in the flow that you need to configure so it works in your environment.

You will also need Excel (Business) because Excel (OneDrive) does not offer the same flow actions.

Source files location

This probably has to be sharepoint location since the script runs out of sharepoint.

Choose your sharepoint site or change the action. If you change the action, you will need to put the "body" back in place in apply to each file loop action.

Update the run script action

Choose your script name.

The file ID should be available from the list files action.

This step should not be modified. It is made to save each worksheet in the xlsx file with the same file name appended with sheet name. So when you have multiple worksheets they will all have the same file name with each sheet name.

Note: If any of the fields in the actions disappear during import, I can provide them for you.

My Sample Run

[juresti]

solved saving as csv from excel online all worksheets all xlsx files in a folder.

[Anonymous]

Hi @juresti , thanks for the useful flow. I am getting errors upon importing it due to the script, even though I had created one already. Could you upload screenshots of the script itself so we could recreate it? Thanks.

[juresti]

@Anonymous  This is my screenshot of the script. The office script. It may be something else causing an error if it is while importing the zip file.

[DublinOH_User]

Hi @juresti 

Would you please open the action steps and share another screenshot or screenshots so we can view how the 'Initialize' string data and related actions are defined?
This is part of a process where we need to save excel with dynamic tables as CSV before we can use the tables created.

Appreciate the help as I am newer Flow user.
DublinOhio_User

[juresti]

@DublinOH_User 

Here are the definitions of all the actions in case they got lost during the import:

the folder where your xlsx files are stored.

the body is from list files in folder. run script will be applied to the location where your files are stored using each file Id.

create file part of file name

create file part of file name

create file file content

just a null in function

[Danalber]

Hi,
Great jobs, finally a good solution to convert XLSX to CSV.
Thanks a lot.

Just one question, I cannot get the "Result" of the "Run Script" function. I only get the "Body" to choose. Any idea why? Something special to consider?

Any help is appreciated.
BR

[juresti]

Hello @Danalber 

You have to manually type it in the formula bar. I have the formula showing on my screenshot.

body('Run_script')?['Result']

Note that Run_script is the name of my excel run script step.

[juresti]

It may be case sensitive which would be

body('Run_script')?['result']  

is what I originally had posted.

[Danalber]

Hi @juresti ,

thank you very much for your quick reply. This really helped, in your pictures it looked like you can just select the "result", I was able to enter it manually although I had to put it in a function to make the flow accept it.

After I found another error in my "split" function it seems to work pretty much. I only have one issue left, in my CSV file I only get one row with all the data. I am missing the line feed, is this an issue with the script or do I need to fix it in the flow?

E.g.:

"Month_Year","Messpunkt ET-LDU Pulse","UC Total (Mio. Pulses)","QTY Tubes","Jan-21","DEGOE_W2_M_343_LDU-Endtest_Pulses","7155,41","52","Feb-21","DEGOE_W2_M_343_LDU-Endtest_Pulses","11377,77","67","Mar-21","DEGOE_W2_M_343_LDU-Endtest_Pulses","9920,45","72",....

Thanks again and have a great day

BR Daniel