Save XLSX Excel all worksheets as csv 03-23-2023 12:10 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]

@Danalber 

Dan, I'm going to say it is one of your compose steps that is off.

compose sheets: 

split(variables('stringData'),']],[[')

compose worksheet data - ** the reason the rest of it is on another line is because that adds a return character. 

If you imported the flow from this post, the return should already be in place.

This is the text if you need to copy it, be sure the return is in your function:

replace(replace(replace(item(),'],[','

'),'[',''),']','')

[Danalber]

Hi @juresti ,

You did it again! 🙂 Thanks a lot, it is working now. 👍

It was my "Replace...." step, I just had one line, but as you said the line break is important. Now I have multiple rows in my CSV file, as it should be.

Took me a while to find a good flow to convert XLSX to CSV, thought it would be easier but you always needed these bloody tables inside the Excel file. 🙂

Hope your post will help many more people, great job.

Have a pleasant weekend.

BR from Germany

[maxpower45255]

Here are some tips that might be helpful to others:

1- if you need to save a single worksheet in the workbook, use the below if script and pass in the SheeName as parameter. 

function main(

  workbook: ExcelScript.Workbook,

  SheetName: string

    ) 

  {

    // This code saves an Excel's worksheets as separate CSVs

    // declare variables

    let wbook = workbook;

    // 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 {

          let currentSheetName = sheets[i].getName();

          if (currentSheetName == SheetName) {

            //Since we're not saving all the sheets, we must store the sheet data in the first position (0).

            //items[i] = sheets[i].getUsedRange(true).getTexts();

             items[0] = sheets[i].getUsedRange(true).getTexts();

          }

        }

        catch (any) { }

    }

    // return the worksheets array

    return items;

}

2- To get the line feed properly, define a "Compose LineFeed" action and set its value to a blank line (hit enter in the body), then change the formula as in #3

3- The "Compose Worksheet Data" formula should be set to:  

replace(replace(replace(replace(replace(item(),'],[',outputs('Compose_LineFeed')),'[',''),']',''), '{"result":', ''), ':}', '')

I just got this working for a bot I'm developing. If you have questions, let me know. 

[dvuramesh]

Hi @juresti , is there a way to save the CSV with the filename_sheetname instead the random value?

[juresti]

Hello @dvuramesh ,

Thanks for suggesting adding the sheetname instead of random value.

I have updated the attached flow and the office script text as well so that sheetnames are used instead of random numbers.

Be sure to update both your office script and replace the new flow.

It should just plug and play like the other one.

[PhilBruton]

Hi @juresti

Thanks for sharing this great solution for automating conversion of workbooks to csv !

I couldn't get the package to import but followed your steps and have this working nicely, as part of a wider process for creating user accounts in AD out of our HR system and issuing them out.

One thing I can't seem to understand though, my csv file has all the correct columns and rows of data, but with one extra piece of data in column A, after my rows of data, saying "Sheet1"

Does anyone get this? Is there a point in the flow steps or office script, that's adding this in?

Phil

[juresti]

@PhilBruton To me it sounds like the office script is adding that last row with just sheet1 in the first column.

If it is adding it to all your csv files, there must be something extra in the office script, or possibly the flow. Look in the flow and office script searching for appearances of Sheet1.

I'll need to run this flow and find out what I get, I don't remember looking down at the last rows to see the output.

[kees]

@juresti , @PhilBruton,

I had the same issue and found that the return function of the office script states :

    // return the worksheets array

    return [items, sheetnames];
}

Deleting ", sheetnames" should fix it by returning only the items

[juresti]

@kees The flow needs the sheet name so it names each csv file according to the name that is if you are using it as is.

@PhilBruton I have checked the flow again and no where in the flow does it mix the sheet name with the data while processing it.

Only when it creates the file is when sheet name is used in the flow.

Also the script does not mix the sheet name array with the data items array at all.

I would double check both the flow and script to see if sheet1 was typed manually or referenced in a variable.

And if you want to share screen shots that would help.

Thanks.

[mbayne]

Getting the following error when importing the ZIP file:

Flow save failed with code 'MultipleErrorsOccurred' and message 'The dynamic operation request to API 'excelonlinebusiness' operation 'GetSingleScript' failed with status code 'NotFound'. This may indicate invalid input parameters. Error response: { "status": 404, "message": "Script not found. It may have been unshared or deleted.\r\nclientRequestId: c65b4bdc-5a64-41e2-9053-0a82c0e52406", "error": { "message": "Script not found. It may have been unshared or deleted." }, "source": "excelonline-cus.azconn-cus-001.p.azurewebsites.net" };The dynamic operation request to API 'excelonlinebusiness' operation 'GetSingleScript' failed with status code 'NotFound'. This may indicate invalid input parameters. Error response: { "status": 404, "message": "Script not found. It may have been unshared or deleted.\r\nclientRequestId: c65b4bdc-5a64-41e2-9053-0a82c0e52406", "error": { "message": "Script not found. It may have been unshared or deleted." }, "source": "excelonline-cus.azconn-cus-001.p.azurewebsites.net" }'.

This is my first time using PowerAutomate, not sure how to fix this. I have a 350+ sheet excel file that I need to split into individual CSVs for each sheet and this flow would be really helpful. Thanks!