Convert several Excel files (*.xlsx) to CSV files with the same name and sheets

Help Activities
, , , ,
1

Hi everyone,

I have a case, where I have to copy/convert all Excel Files in one folder to CSV files. The sheets and names should remain in the CSV file. Currently every Excel file contains one sheet, but every sheet has a different name.

I have seen the solutions for similar cases, but no one with exact this case and I am wondering whether anybody had made this experience.

Any help is appreciated.

Attached a xaml file for reference.

Excel to CSV.xaml (10.1 KB)

Thanks a lot in advance and kind regards,
Anastasia

1 Like
2

Hi @Anaskra ,
Convert Excel to CSV you can flow Step
-for each file in folder
-read range to get data table
-write csv to convert to CSV file
Regards,
LNV

1 Like
3
  • For Each File in Folder (with filter *.xlsx)
    ** Excel Process Scope
    *** Use Excel File (source file is from For Each File in Folder property)
    **** For Each Sheet
    ***** Read Range
    ***** Write CSV (filename generated based on sheet name)
4

Hi @Anaskra

=> Use for each file in folder activity to iterate the files in the folder. In the filter by option give like “*xlsx”.
=> Inside for each Insert the Use excel file activity and pass the CurrentFile.toString in the Path of file.
=> Inside for each activity take an assign activity to store the file name create a variable called FileName (String Datatype).

  • Assign → FileName = CurrentFile.Name (Which stores the excel file name)
    => Take for each excel sheet activity to iterate the sheets in the excel. Output - CurrentSheet
    => Inside for each excel sheet activity insert the Read range activity to read the CurrentSheet data and store in to a datatable variable called dtExcelData.
    => After Read range activity place the Write CSV activity to write the datatable data to csv file.
    In Write from field give the dtExcelData Variable.
    In Write to what file give the Path of the csv file and give the name of excel name to csv.
    Path - “C:\Users\Downloads"+FileName+”.csv"

In the Path give the csv path. FileName is the variable which stored the Excel file name.

Check the below workflow for better understanding.

Browser_Practice
Browser_Practice909×1868 141 KB

Hope it helps!!

1 Like
5

Actually, let’s change this to avoid repeatedly executing the Excel Process Scope:

  • Excel Process Scope
    ** For Each File in Folder (with filter *.xlsx)
    *** Use Excel File (source file is from For Each File in Folder property)
    **** For Each Sheet
    ***** Read Range
    ***** Write CSV (filename generated based on sheet name)
1 Like
6

Hi @Anaskra

Is this solution is working for you. If yes let me know and Make mark it as solution to close the loop.
If getting any issues with it then we are ready to solve the issues.

Happy Automation!!

7

Hi everyone @postwick @Luong_Nguyen1 @mkankatala , thank you all for the valuable input!

I could combine your inputs and the following process worked for my case:

  • Assign Files = Directory.GetFiles(SourceFolder, “*.xlsx”)
  • For Each
    → item in Files
    → assign FileName = Path.GetFileNameWithoutExtension(item.ToString)
    → Excel Application scope for item → Read Range FileName (output: DataTable)
    → Write CSV from Data Table to TargetFolder+FileName+“.csv”

The only issue I am facing is that this isn’t working for huge excel files. Does anybody know how to solve that?

Many thanks and best regards
Anastasia

1 Like
8

Okay @Anaskra

In the 3rd point instead of using Excel Application scope use Read Range workbook activity. The output is same is for both activities. Try with this.

Hope you understand!!

9

Hi @mkankatala , I tried it - It takes a bit longer, but it works!

Thank you very much!

1 Like
10

Thank you @Anaskra

Happy Automation!!

11

Quick tip, you don’t have to create a variable and assign the GetFiles out put to it. You can just put your GetFiles expression directly into the For Each. This is simpler and eliminates the need for the extra variable and Assign activity.

1 Like
12

Thanks for the hint! I will try it

13

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