Download multiple csv files, merge them and then convert to a single excel file

Help
1

Just starting out with UI Path and was given this challenge from my manager. He wants me to open a file folder which contains a bunch of csv files. Grab the latest csv files, merge then together, then save as an excel spreadsheet.

I know I can download each file individually by using the Read CSV activity and giving it a file path and doing each file individually and then you can merge the all the csv files with the Merge activity and export as a data table and then convert it to an excel spreadsheet after the merge.

However, the number of files in the folder will continually update, so I need to grab the most current ones in the folder, then merge them and convert them into one excel sheet.

What I’ve been trying to do is grab all of the files using:

Directory.GetFiles("C:\Users\pKosten\Documents\UiPath\excelMerge\montly_reports/","*.csv")

Then use a For Each activity to loop through each of the files. This is where I get stuck though.

In the for each loop, do I create the data table first or read the csv file first, then output it as a data table? Or in the for each should I read each csv, then write and merge the csv and then convert it to an excel spreadsheet?

Any help on the direction I should use would be greatly appreciated.

Thanks in advance

1 Like
2

Hi
Welcome to UiPath community
You were almost done
— as we need to merge them together we need to have a destination datatable, I.e., a fixed place where we need to merge all the datatable together
— so for that create a datatable using a activity called Build datatable activity where mention the column names as per the csv and get the output with a variable of type datatable and name it Final_dt
—next to this to make sure that the Final_dt has only the table structure without any data at the initial stage use a activity called clear datatable activity where the mention the input as Final_dt
— now use a for each loop activity and pass the output variable of Directory.GetFile method
—inside the loop use a Read CSV activity and pass the file path input as item (the variable from the for each loop as it has the value of the file path obtained from the GetFiles method)
And get the output with a variable of type datatable and name it out_dt
—now its time to merge
— while we are still inside the loop use a merge datatable activity and in the source property mention as out_dt and in the destination property mention as Final_dt
This will get merged and Final_dt atlast will have all the records merged together

Hope this would help you
Kindly try this and let know for any queries or clarification
Cheers @platinumgulls

5 Likes
3

Sorry for the delay in response.

Yes, this is exactly the kind of direction I needed, thank you so very much!

4

Hi @Palaniyappan,
I am also trying for same for merging excel as you above mentioned but getting error

image
image1366×768 78.5 KB

1 Like
5

Fine
Both the datatable should have same set columns
—same order of columns
—same datatype of columns and that’s the issue
Kindly change them to equal set of datatype

Cheers @smita.mobifly

6

getting exception

image
image1366×768 79 KB
,
column name, data table both are same

1 Like
7

Kindly check with the column size, column where it is in order, column type
That is what is even mentioned in the error buddy
Cheers @smita.mobifly

8

in my build datatable colume order
[S.No,Year,Month,Accounting Document No.,Accounting Document Date,Document Type,Taxability,Supply Type,Nature of Recipient,GSTIN/UIN of Recipient,Name of the Recipient,Invoice/Debit Note/Credit Note/Receipt Voucher/Refund Voucher (No.),Invoice/Debit Note/Credit Note/Receipt Voucher/Refund Voucher (Date),Invoice/Debit Note/Credit Note/Receipt Voucher/Refund Voucher (Value),Line Item,Total GST Rate,Taxable Value,Case ID,Remarks,For MIS 1,For MIS 2,For MIS 3,For MIS 4,For MIS 5,For MIS 6,For MIS 7,For MIS 8,For MIS 9,For MIS 10,File Name , ]

excel column order are [S.No,Year,Month,Accounting Document No.,Accounting Document Date,Document Type,Taxability,Supply Type,Nature of Recipient,GSTIN/UIN of Recipient,Name of the Recipient,Invoice/Debit Note/Credit Note/Receipt Voucher/Refund Voucher (No.),Invoice/Debit Note/Credit Note/Receipt Voucher/Refund Voucher (Date),Invoice/Debit Note/Credit Note/Receipt Voucher/Refund Voucher (Value),Line Item,Total GST Rate,Taxable Value,Case ID,Remarks,For MIS 1,For MIS 2,For MIS 3,For MIS 4,For MIS 5,For MIS 6,For MIS 7,For MIS 8,For MIS 9,For MIS 10,File Name

9

may be there is extra column created into build data table

image
image1366×768 76.3 KB

1 Like
10

@Palaniyappan
I am just learning UI path, I tried the same steps and could run the program without any error, but how do i see the content in final_dt

11

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.