Hi Team,
How to convert multiple CSV files(with different into single .xlsx file.)
Apprecaite your help!!
Check if this can help
https://forum.uipath.com/t/how-to-create-generate-xml-out-of-datatable/8642
Hi @chandrakala.productanalys ,
There is another way of doing it without UiPath and can be done using Power query,
This way gives you more control than the traditional approach
This might not be an immediate solution for you, but it might be a long term solution
Please follow the below link to understand more
Combine CSV files in Power Query
High Level
- Create the power query
- Provide the parameters such as file path
- Execute the query to know the result
- Save it as a template
- Use this template excel file to merge the csv file as needed
-Srini
All the .CSV file—> Excel(.xlsx) with different sheets.
Check out the below zip file.
BlankProcess15.zip (215.9 KB)
Place all your .csv file in the Input Folder.
Input Folder:
There is an Output Folder where your .xlsx file will be created at runtime.
Output Folder:
Make sure not to delete the Macro_Utility_Workbook.xlsm file. This contains the macros code to merge the excels.
Hope it helps!!
Regards
- for each file in folder with *.csv as filter
- Inside loop use read csv and then write range in read csv use
currentfile.FullNameand save it to datatable dt and in write range give the filename you need and sheetname ascurrentfile.Name>Split("."c)(0)- this renames each sheet with the csv filename, and datatable as dt
cheers
All csv has same data format(same headers)?
and you want all csv in single excel sheet of workbook?
Hi @chandrakala.productanalys ,
You want merge to 1 sheet
you can use a int32 variable
read each file CSV to get data table, index of start cell is the end of prev data
regards,
yes,i want to merge in single excel sheet.
There is no need to keep all 30 CSV files. Once they are imported into a datatable using the ‘read range csv’ activity,new file downloading should either replace or delete the existing CSV file.
You need not read all the 30 CSV files into a datatable. Without reading the CSV files also you can merge all 30 CSV files into one single Excel file.
Just place your 30 CSV files in the input folder of provided code.
You can see that a output Excel will be created within a workbook with different sheets in Output Folder.
Regards
Hello @chandrakala.productanalys
Please try these steps :
1 . Create a String array variable , and read all the CSV files from the path , and store it in the same variable
2 . Loop through each item in the variable and use Read CSV activity and store it in the datatable
3 . Use a Merge Data Table activity inside the loop . The result of this will be a table containing data from all the CSV files
4. Outside of the loop use Write Range activity and feed the merged table as input datatable.