Answered

Question about exporting SharePoint list to CSV in OneDrive/SharePoint (missing records)

(0) Share

Report

Posted on by katemeng

Hi gurus, I want to export SharePoint list data to CSV file for further data analysis. There are 132 records on the SharePoint list. After using the below flow:

The records in CSV stopped at the 100th row started all over again:

How can I adjust the flow to export the full SharePoint list? Appreciate your help!

Categories:

Using flows

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

annajhaveri 8,531 Most Valuable Professional on at

Like (1)

Report

@katemeng There is a default limit of 100 items for “Get Items”. You can change the limit by turning on pagination in Get Items action settings and specify number of items you need

Was this reply helpful? Yes No
katemeng 12 on at

Like (0)

Report

Thanks @annajhaveri for your reply! Followed your advise I checked the "Get items" settings and enabled the Pagination to a larger number as threshold. After that I run the flow again, this time all 132 records are on the CSV file, but they appear twice:

The file ends with 266 rows (should be 133 only). Do you know how to fix that?
I also noticed the threshold limit is 100000. I have a larger list with about 200,000 records, do you know are there ways to export that to a CSV file?
Thanks again!

Was this reply helpful? Yes No
annajhaveri 8,531 Most Valuable Professional on at

Like (0)

Report

c@katemeng in the file content parameter, you have concat expression as well as the output of Create CSV Table, why do you have that concat expression?

Was this reply helpful? Yes No
katemeng 12 on at

Like (0)

Report

Hi @annajhaveri there are Chinese characters in the list, and will be messy code after exported. So I followed Solved: Create a .csv file that uses UTF-8 character encod... - Power Platform Community (microsoft.com) to add concat expression in the content. Now I understand the correct to be put is either "Output" or "concat". If I put both, I'll get twice 🙂

The final question is how to export list with more than 100000 records?

Was this reply helpful? Yes No
annajhaveri 8,531 Most Valuable Professional on at

Like (1)

Report

@katemeng thats a tricky one, Get Item has limit of getting 5000 items, to fetch more than that, you need to use pagination, there are few blogs out there on how to get more than 5000 items from SharePoint list in flow, you can refer to below reference links. Please note if you need to get 10000 records, in a single flow run the flow performance will get affected, its always recommend to apply filter to Get Items to get only those items that you need to process.
https://alextofan.com/2019/08/22/how-to-get-more-than-5000-item-from-sharepoint-online-in-flow/
https://www.tachytelic.net/2020/04/get-more-5000-items-sharepoint-power-automate/

Was this reply helpful? Yes No