Skip to content

Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Problems
  • Triggers
  • Application specific solutions
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Resources
Menu

Quick way to export SharePoint list to Excel file using Power Automate

Posted on January 20, 2021April 14, 2021 by Tom

“I’m trying to automate export of SharePoint list into an Excel file, but Power Automate takes ages to finish. How can I make it faster?”


Exporting SharePoint list items into an Excel file can be very time consuming. You must loop through all items and add each of them as a new row into the Excel file. Especially if you have big lists with thousands of items it can take hours. So, is there a faster way?

Create .csv file instead

You can create .csv file instead of an Excel file. CSV file has simpler format than an Excel file, but it can be opened in the Excel application in the same way. And due to its simple format the list can be exported much faster than into an Excel file. Power Automate has even a dedicated action to create a .csv file from an array. It’s called ‘Create CSV table’ and the input must be an array. Then you can select if you want to export all columns (Automatic) or only some of them (Custom).

power automate export list excel csv

The example below will process all items returned by the ‘Get items’ action and create a .csv file with only 3 columns.

power automate export list excel csv

The output from the ‘Create CSV table’ action can be then used as a content for a new .csv file.

Summary

You should ask yourself if you really need an Excel file. It’s much easier, and faster, to create a .csv (comma-separated values) file in Power Automate. Instead of adding rows one by one into an Excel table, you can process the whole array with all items at once.

But if .csv is not an option and you need an Excel file, then there’s no solution to make the export faster. Power Automate can add the rows only one by one and that operation takes some time.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

9 thoughts on “Quick way to export SharePoint list to Excel file using Power Automate”

  1. Sumit says:
    October 18, 2021 at 6:47 pm

    Hi, Thanks for your post.

    But is there any step I can replace the csv exported file with new file. I dont want multiple files to be saved . Everyday there should be new exported file with updated data.
    Currently my flow gets failed because the csv file with same name already exist.
    Please suggest.

    Reply
    1. Tom says:
      October 20, 2021 at 7:41 pm

      Hello Sumit,
      take a look on this older article: https://tomriha.com/how-to-update-not-replace-an-existing-file-in-power-automate/. If the creation fails, continue with update of the existing file.

      Reply
  2. Barend says:
    February 10, 2022 at 3:21 pm

    Hi there, great post!

    Any idea how I can export the values only for managed metadata fields?

    Tx in advance.

    Reply
    1. Tom says:
      February 13, 2022 at 6:29 pm

      Hello Barend,
      the managed metadata field value contains the term in format Value|TermID. As such, you’ll need the split(…) expression to remove the TermID part and keep only the Value, e.g.
      split(item()?['ManagedMetadataColumnInternalName/Value'],'|')[0]

      Reply
  3. Stephanie says:
    February 17, 2022 at 9:00 pm

    You’re a life saver…. I was struggling with how to put an .xlxs file on sharepoint.. I found many other videos but nothing did what I wanted to do… with your suggestion that we use a .csv file instead, it became so simple.. Thank you.

    Reply
  4. Kamil says:
    March 6, 2022 at 10:27 pm

    I have followed the above instruction but I keep getting an error while testing the flow: “The ‘from’ property value in the ‘table’ action inputs is of type ‘String’. The value must be of type ‘Array’.”

    Any ideas how this can be fixed? I just cannot simply change it as the data I am trying to save onto csv file is retrieved from FORMS outcome.

    Reply
    1. Tom says:
      March 9, 2022 at 8:02 pm

      Hello Kamil,
      the solution is intended to export a whole SharePoint list into .csv file. If you’re trying to save Forms responses into an Excel file you can use the ‘Add a row’ Excel action.

      Reply
  5. Wayne says:
    April 28, 2022 at 10:43 am

    Hi Tom,
    Thanks for your sharing.
    My problem is that I can’t find the “DisplayName” for the person field value in the Dynamic Content, so the csv file returned with whole “@odata.type” content. How can I get the correct DisplayName returned in the person column?
    Thanks a lot in advance.

    Reply
    1. Tom says:
      May 3, 2022 at 8:02 pm

      Hello Wayne,
      if there’s no DisplayName then I guess it’s a multiple people picker column – you’ll have to use the solution described here: https://tomriha.com/export-multiple-person-or-group-column-into-csv-table-in-power-automate/

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Now available:

The Ultimate Power Automate expressions cheat sheet
Equip yourself with the tool to translate your thoughts into Power Automate expressions!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

Hello and welcome!

My name is Tom and I'm a business process automation consultant and Microsoft MVP living in the Czech Republic. I’ve been working with Microsoft technologies for almost 10 years, currently using mainly Power Automate, SharePoint, Teams, and the other M365 tools.

I believe that everyone can automate part of their work with the Power Automate platform. You can achieve a lot by "clicking" the flows in the designer, but you can achieve much more if you add a bit of coding knowledge. And that's what this blog is about.

To make the step from no-code Power Automate flows to low-code flows: using basic coding knowledge to build more complex yet more efficient flows to automate more of your daily tasks.

  • Format results of date calculation in SharePoint columnJune 29, 2022
  • Why is your Power Automate flow creating duplicatesJune 26, 2022
  • How to create a unique identifier in your Power Automate flowJune 22, 2022
  • How to assign custom SharePoint permission level with Power AutomateJune 19, 2022
  • Remove permissions from a specific SharePoint user with Power AutomateJune 15, 2022

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

© 2022 Let's POWER Automate | Powered by Superbs Personal Blog theme