Unanswered

Extract data from multiple Excel files to single CSV

(0) Share

Report

Posted on by richzip

Hello, I would like to set up a flow that pulls data from multiple Excel files on Sharepoint, and copies that data to a single CSV file. Ideally, when triggering the flow, the user would specify a month, and it would only extract data where the "month" column equals the specified month. And, the resulting CSV file name would contain the month, to differentiate it from previous month files.

Thank you for any assistance!

Example Source Tables:

Resulting Output, when user selects month of 202304 (nothing is extracted for ID 456789, since month 202304 is not in that table)

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (0)

Michael E. Gernaey 53,315 Super User 2025 Season 2 on at

Like (0)

Report

hi @richzip

Unfortunately you didn't provide data on how the person is passing or selecting dates, so that we can understand which trigger to use for instance.

Can you please provide more detail on how the user interacts and triggers this flow.

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

Was this reply helpful? Yes No
ManishSolanki 15,091 Super User 2025 Season 2 on at

Like (0)

Report

Hi @richzip

Please also specifies the expected number of inputs excel files.

Thanks

Was this reply helpful? Yes No
richzip 30 on at

Like (0)

Report

@FLMike @ManishSolanki thank you both for your reply! I think I figured most of this out. I have the flow set up with a manual trigger, including an input value. Under the "list rows present in a table", I added the input value as a Filter Query.

However, I may want to set up a 2nd input value that further filters the records. When setting that up as an addition to the Filter Query, I got an error that I can only enter a single eq value. So instead, I tried setting up a "condition", where the PSID column from my source files equals the 2nd input value. None of the columns in my source data were selectable under Dynamic Content, so added ['PSID'] instead - that doesn't appear to work.

@ManishSolanki each time this is run, there will be ~200 files

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (0)

Report
This is how I would build the flow.

However, if there was a LOT of files/rows and I had access to premium connectors, I'd probably do it differently so as to perform a bulk update to Excel.

See full flow below. I'll go into each of the actions.

Manually trigger a flow is setup similar to yours where the person running the flow can enter the Month.

Get files (properties only) will retrieve all the Excel files. I believe this will only return the first 100 files by default. If you have around 600 then you should change the Top Count to ensure it gets all files. In my example I've set it to 2000.

To create the new Excel file where the combined output will go - I've created an Excel file to use as a Template with the Table and columns already created. This way we can get the content from that blank Excel file and create a new file based on it.

Get file content using path gets the content from the Excel template.

Create file uses the content from our Excel template and appends the Month as part of the file name.

Apply to each file iterates over each of the files.

List rows present in a table retrieves the rows from the current Excel file we are iterating over. I used the Identifier for the current file. Note that I've also set a Top Count here as the List rows present in a table action will only retrieve the first 256 rows by default (I'm not sure how many rows you have in your Excel files). Also note that I've set a filter to only bring back rows for the specified Month.

Apply to each row iterates over each row in the current Excel file.

Add a row into a table adds each of the rows returned into the Excel file we created. Because the Table name is dynamic we need to add the data dynamically. Note that I've also added the Sick and Vacation values for the Total using the add formula. It assumes there is always a value entered for Sick and Vacation, even if just 0.

{ "ID": @{item()?['ID']}, "Month": @{item()?['Month']}, "Sick": @{item()?['Sick']}, "Vacation": @{item()?['Vacation']}, "Total": @{add(float(item()?['Sick']), float(item()?['Vacation']))} }

Was this reply helpful? Yes No
richzip 30 on at

Like (0)

Report

@Grant Thank you very much! One other question. I added another input to the trigger (Analyst), as well as a matching column in the source files. Since the "list rows" step only allows a single eq value, I instead added a "filter array" step immediately after "list rows". The flow ran, and filtered out the Month (from the "filter query" in "list rows), but it didn't filter for just the requested Analyst.

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (0)

Report

Not sure what the issue is here. The Filter array looks like it's using just this months data and then filtering down further on Analyst. I'd check the output of Analyst in both the output of List rows present in a table and the Manual trigger parameter to ensure they are what you expect.

Was this reply helpful? Yes No
richzip 30 on at

Like (0)

Report

@grantjenkins It did use just the requested month's data, but it did not filter to just the requested analyst. The input is for Analyst = Rich, but it also extracted records with Analyst "John".

Was this reply helpful? Yes No