Merge multiple cvs files into separate Excel spreadsheets

(0) Share

Report

Posted on by pg2020

Hello, everyone,

I would like to combine several csv files from a sharepoint in one excel file on a sharepoint.

To do this, a separate worksheet would have to be created in a single Excel file for each individual csv file.

So the final result would be one xlsx File with one worksheet per csv file.

The name of the worksheets should be the name of the csv files without the .csv suffix.

The content in the csv files is separated with commas and must then be separated in the Excel file. (Text to columns).
The automation can be scheduled once a day. It can overwrite the xlsx file.

I found the Power Automate "Encodian" module. It looks promising and already offers many possibilities in the free version.

But I'm worried about data protection in Germany.

Can I solve my problem with Power-Automate or do I need Encodian Flowr for this?

Or can be Power BI with Power query ONLINE can be used for this process?

Thanks and Regards

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (1)

pg2020 44 on at

Like (1)

Report
Copy link

Link copied!

Hello, everyone,
I studied the following link:
https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/convert-csv

I've already managed to convert the CSV files into xlsx files. However, I don't want to have individual xlsx files.
I want all csv content in a single Excel file in separate worksheets.

The Result i want to have:
The Worksheets should be alphabetically sorted 🙂

Was this reply helpful? Yes No
pg2020 44 on at

Like (1)

Report
Copy link

Link copied!

I found the next link:

https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/combine-worksheets-into-single-workbook

Got it to work One time.

But the flow runs so long. 10 minutes for 3 xlsx Source files 😞
And then never again.

What can be wrong there?

This script catches the xlsx from the output and adds them to one workbook

EDIT:

Got it:
There was too much other stuff in subfolders.
I must not have subfolders or other files than .xlsx in the "output" folder.

Script ran 20 seconds for merging 3 xlsx files

Was this reply helpful? Yes No
pg2020 44 on at

Like (0)

Report
Copy link

Link copied!

Im getting mad 😞

The Flow is really slow.

I build it on a new Tenant.
First run was 30 seconds.

Second run 5 Minutes and than aborting.

Changed nothing. I dont get it.

Is it because of licensing?
Tried it with Automate Free and a Office E3 license

Do i need a full license to get Performance?

The data is so small. 3 xlsx Files with maximum 3 rows. Just for testing.

Was this reply helpful? Yes No
Verified answer

pg2020 44 on at

Like (0)

Report
Copy link

Link copied!

I solved it now (really 😄 )
The Problem was the Tutorial from MS was wrong.

The solution can you read Here:
https://powerusers.microsoft.com/t5/Building-Flows/Running-a-script-is-hit-and-miss/m-p/1829116/highlight/true#M201953

Was this reply helpful? Yes No