Convert Excel Input Data that are in multiple sheets to Output CSV Data

Help Studio
, ,
1

Hi team,

I have an existing RPA process that uses one excel file and reads only one sheet from that file at a time. Here is the process folder zipped.

Test.zip (200.8 KB)

However, using similar RPA process, I need to read multiple excel files from a folder, read multiple sheets from each excel file in that folder and then process, and generate CSV files. One input excel file (whether it has multiple sheets or just one sheet) is expected to have one output CSV file.

FYI - some files may have just one sheet, others might have multiple sheets.

Attached, sample input data for this new process that is trying read multiple files and read multiple sheets in each file.

Input_Data.zip (19.0 KB)

Please let me know if you’ve any questions.

Thanks a lot!

2

Hi,

How about the following sample?

image
image1241Ă—3712 316 KB

Sample
Excel_to_CSV.zip (46.7 KB)

Regards,

3

Hi @Yoichi

This’s fantastic. It worked perfectly fine.

Do you want me to create a separate topic (please let me know if you do) as I have two follow up questions?

  1. If you see in the prior post, I mentioned that "One input excel file (whether it has multiple sheets or just one sheet) is expected to have one output CSV file.". What I meant by that is to have only one CSV file generated per one input file. For example, let say an input excel file has data in multiple sheets (Sheet1, Sheet2, Sheet3), rather than having separate CSV files, I want to have one CSV file. In short, one input excel file generating one CSV file.

  2. This question is regarding to the design while using Reframework for the same process. How would you envision the process’s steps when using Reframework, preferably using DataRow for transaction items?

image
image638Ă—764 74 KB

Please let me know if you have any questions.

Thanks a lot!

4
  • What I meant by that is to have only one CSV file generated per one input file. For example, let say an input excel file has data in multiple sheets (Sheet1, Sheet2, Sheet3), rather than having separate CSV files, I want to have one CSV file. In short, one input excel file generating one CSV file.

Then you need to read each sheet and then Merge Data Table into a finalDT. Once all sheets have been read and merged into finalDT, then write finalDT to CSV.

  • This question is regarding to the design while using Reframework for the same process. How would you envision the process’s steps when using Reframework, preferably using DataRow for transaction items?

I wouldn’t try to use REFramework for this. It’s poorly designed, especially for a use case like this. Your requirements are fairly simple and using REF would greatly overcomplicate things.

5

It’s necessary to merge datatable inside ForEachExcelSheet and write it to workbook after the loop.
How about the following?

Excel_to_CSV_v2.zip (47.0 KB)

This question is regarding to the design while using Reframework for the same process. How would you envision the process’s steps when using Reframework, preferably using DataRow for transaction items?

As this process uses multiple loops and not iterate datarow in outer loop, it’s not very good to set datarow as TransactionItem, I think. It may be good to set file name.

Regards,

6

Hi Yoichi,

It does not look like this is generating the CSV files as expected. May I know the role DT_Input datatable variable in the process after you created a new DT_temp datatable variable?

7

Sorry, i had mistakes regarding logic.
Can you try the following sample? It’s necessary to add variable for merged datatable for each sheet.

Sample
Excel_to_CSV_v3.zip (46.1 KB)

Regards,

8

Hi @Yoichi

Thank you. It is working as expected.

1 Like
9

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