How we can Combine All .CSV File into One Excel File but the name of Sheet is not Fixed

Help Studio
, ,
1

We have Multiples .CSV Files and Each CSV Files have only One row. And we want to Combine all the Row data into One Excel Sheet.

Sheet name is not fixed, it is different for Every Sheet (.CSV file name and the Sheet name is same)

How we can Combine data into one Excel Sheet ?

image

image
image1528Ă—747 47.4 KB

2

@Ajinya_jorwekar

  1. For each file in folder
  2. Read csv activity with currentfile as filename and assign a datatable dt
  3. Use merge datatable activity and give the source as dt and destination as finaldt
  4. After the loop use write range activity with the target excel file and finaldt as datatable

Hope this helps

Cheers

3

Step 1: Build A Datatable With Exact Headers That You Need Eg. Dt_Final
Step 2: Read CSV Into Datatable in For Each Loop Eg. dt_CSV_output
Step 3: Add Merge Datatable in the Loop. You need to give values as Source datatable as dt_CSV_output & destination as Dt_Final

4

@Ajinya_jorwekar

i think the sheet name is same the filename withoutextension,if so follow below steps

use build datatable activity and create all the columns names to and create output as dt_final
assign activity

directory.getfiles(“Folderpath”)

use for each activity

inside use assign activity
sheetname=path.getfilenamewithout extension(currentItem)

below use read csv file activity

below that use merge datatable activity

cheers

5

Hi @Ajinya_jorwekar

  1. Use For each File in Folder activity → Loop all the files inside the folder

  2. Read the CSV file inside the loop

  3. Use Text to column activity

Check out the XAML file

17.10.2023_Forum_1.xaml (13.1 KB)

Regards
Gokul

6

Hi @Ajinya_jorwekar

Follow this workflow steps:

Use the Assign activity to create a variable that will store the combined data. You’ll use this variable to accumulate the data from each CSV file.
And Create a variable with an initial value of an empty DataTable.

Use For Each Activity to iterate through the list of CSV files.
Inside the for each loop

Use the “Read CSV” activity to read each CSV file. Specify the CSV file path.

Use the Build DataTable activity to create a DataTable with the same structure as your CSV files. This structure should match the number of columns and their names in the CSV files.

Use the Add Data Row activity to add the row of data from the CSV file to the DataTable you created in the previous step.

After processing each CSV file you need to combine the DataTables into one.

Use the Merge DataTable activity inside the For Each loop to merge the DataTable from the current CSV file with the CombinedData variable.

use the “Write Range” activity to write the CombinedData DataTable to an Excel file. You can specify the Excel file’s path and sheet name.

Cheers…!

7

@Ajinya_jorwekar

Use this Linq

(From file in Directory.GetFiles(“PathToCSVFolder”, “*.csv”)
Let dt = File.ReadLines(file).First().Split(","c).Select(Function (x) x.Trim()).ToList()
Select dt).Aggregate(Function (acc, row) acc.Concat(row).ToList()).ToList()

8

Thank you @Shiva_Nikhil and all buddies

1 Like
9

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