Answered

Combining Spreadsheets.

(0) Share

Report

Posted on by SwingeyP

A folder contains multiple spreadsheet files. They are named by date. I want to load the data of the first file and add a column which contains the date filename of the file. Repeat for all files but appending the data each time to create one spreadsheet. Can anyone give me any pointers on how to do this please?

Categories:

Dataflows

All responses (3)

Answers (1)

Verified answer

EricRegnier 8,714 Most Valuable Professional on at

Like (0)

Report

Re: Combining Spreadsheets.

Hi @SwingeyP,
Do you want to load the combined spreadsheets into CDS? If so, you can use out-of-the-box Dataflows
with the Excel connector to extract, transform, create the entity and load the data or load the data into an existing entity in CDS. At the transform step you can combine the two spreadsheets.
More on Dataflows: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/create-and-use-dataflows
Joel CustomerEffective 3,224 on at

Like (0)

Report

Re: Combining Spreadsheets.

PowerQuery can get all the data from files in a folder (and dataflows use powerquery) but it doesn't show the file name
https://www.myonlinetraininghub.com/power-query-get-files-from-a-folder
You could add the filename as a column in the spreadsheet.

see alos https://community.powerbi.com/t5/Desktop/Import-Excel-files-from-Folder-automatically-for-every-month/td-p/164587
David Jennaway 716 on at

Like (0)

Report
Re: Combining Spreadsheets.

I'm not sure CDS will help much here, as the data import process will only work off the contents of the file, and there's no direct way to link the records to the filename (which you'd need to populate the column with the date filename.
I'd otherwise use either of these approaches:
Use Excel Macro code to load the files, populate the column with the date filename, and may as well combine the data into one spreadsheet at the same time
Use SQL Server Integration Services (SSIS) if you have a license. You can iterate through the files using a foreach loop