Skip to main content
Power Automate
    • Connectors
    • Templates
    • Take a guided tour
    • Digital process automation
    • Robotic process automation
    • Business process automation
    • Process Mining
    • AI Builder
  • Pricing
  • Partners
    • Blog
    • Documentation
    • Roadmap
    • Self-paced learning
    • Webinar
    • Business process and workflow automation topics
    • Overview
    • Issues
    • Give feedback
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • User groups
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Microsoft Power Automate Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power Automate
    • General Power Automate Discussion
    • Using Connectors
    • Building Flows
    • Using Flows
    • Power Automate Desktop
    • Process Mining
    • AI Builder
    • Power Automate Mobile App
    • Translation Quality Feedback
    • Connector Development
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Galleries
    • Community Connections & How-To Videos
    • Webinars and Video Gallery
    • Power Automate Cookbook
    • Events
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Community Engagement
    • Community AMA
    • Community Blog
    • Power Automate Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power Automate Community
    • Galleries
    • Power Automate Cookbook
    • Save XLSX Excel all worksheets as csv

    Save XLSX Excel all worksheets as csv

    05-14-2021 10:36 AM - last edited 04-17-2023 13:05 PM

    juresti
    Continued Contributor
    6550 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    juresti
    juresti Continued Contributor
    Continued Contributor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    Save XLSX Excel all worksheets as csv

    ‎05-14-2021 10:36 AM

    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

    02.PNG

     

    Make a new script and name it as you like

    03.PNG

     

    Open the script and you will see an empty main function

    04.PNG

     

    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

    05.PNG

     

    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

    06.PNG

    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.

    10.PNG

     

     

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

     

     

    My Sample Run

     

    Capture.PNG

     

     

    save_as_xlsx_to_csv_sheetnames_20230417194451.zip
    Labels:
    • Labels:
    • Automated Flows
    • Scheduled flows
    • Solutions
    Message 1 of 17
    6,550 Views
    2 Kudos
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    • « Previous
      • 1
      • 2
    • Next »
    juresti
    juresti Continued Contributor
    Continued Contributor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-14-2021 11:03 AM

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

    Message 2 of 17
    5,766 Views
    0 Kudos
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-21-2021 05:36 AM

    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.

    Message 3 of 17
    5,375 Views
    0 Kudos
    Reply
    juresti
    juresti Continued Contributor
    Continued Contributor
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-22-2021 05:26 AM

    @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.

     

    01.PNG

    Message 4 of 17
    5,349 Views
    0 Kudos
    Reply
    DublinOH_User
    DublinOH_User Advocate II
    Advocate II
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-23-2021 11:51 AM

    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

    Message 5 of 17
    5,320 Views
    1 Kudo
    Reply
    juresti
    juresti Continued Contributor
    Continued Contributor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-30-2021 11:54 AM

     

    @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.

    30.PNG

     

    31.PNG

     

    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.

    32.jpg

     

    33.PNG

     

    34.PNG

     

    35.PNG

     

    36.PNG

     

    create file part of file name

    37.jpg

     

    create file part of file name

    38.jpg

     

    create file file content

    39.jpg

    just a null in function

    40.PNG

    Message 6 of 17
    5,245 Views
    1 Kudo
    Reply
    Danalber
    Danalber Advocate I
    Advocate I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-18-2021 04:27 AM

    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?

    Danalber_0-1637238448453.png

     

    Any help is appreciated.
    BR

    Message 7 of 17
    4,969 Views
    1 Kudo
    Reply
    juresti
    juresti Continued Contributor
    Continued Contributor
    In response to Danalber
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-18-2021 07:48 AM

    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.

     

    Message 8 of 17
    4,955 Views
    0 Kudos
    Reply
    juresti
    juresti Continued Contributor
    Continued Contributor
    In response to juresti
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-18-2021 07:56 AM

    It may be case sensitive which would be

     

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

     

    is what I originally had posted.

    Message 9 of 17
    4,951 Views
    1 Kudo
    Reply
    Danalber
    Danalber Advocate I
    Advocate I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-18-2021 08:38 AM

    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.

    Danalber_0-1637253283751.png

     

    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

    Message 10 of 17
    4,945 Views
    0 Kudos
    Reply
    • « Previous
      • 1
      • 2
    • Next »

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign up free
    • Sign in

    Browse

    • Templates
    • Connectors
    • Partners

    Downloads

    • Mobile
    • Gateway

    Learn

    • Documentation
    • Learn
    • Support
    • Community
    • Give feedback
    • Blog
    • Pricing

    • © 2023 Microsoft
    • Contact us
    • Trademarks
    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Terms & conditions
    Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices