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:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (1)

David Jennaway 716 on at

Like (0)

Report
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

Was this reply helpful? Yes No
Joel CustomerEffective 3,224 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

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

Was this reply helpful? Yes No