How to read Excel files which contain merged cells

Help Activities
, , , , ,
1

Hi,

I would like to know how to read excel file in UiPath Studio. The Excel file contains merged cells. Example of the input excel file is shown below.

image
image1062×148 8.91 KB

After reading successfully, I would like to filtered those student IDs who completed courses. Filter data table where Completion % column value is 100

In addition, I also want to remove Completed columns and finally saved those data in to Excel files with “processed_file.xlsx”.

The output file should be look like below figure -

The image shows a spreadsheet with course completion details for students A0002 and A0003, including course names, start and completion dates, and completion percentages, highlighted in different colors for Python Programming and Maths. (Captioned by AI)
The image shows a spreadsheet with course completion details for students A0002 and A0003, including course names, start and completion dates, and completion percentages, highlighted in different colors for Python Programming and Maths. (Captioned by AI)923×123 6.9 KB

Can any one can help this challenge?

Thanks.

2

Can you share excel file ?

3

CourseData_Examples.xlsx (10.5 KB)

4

Hi @peter.tun

Can you try below query

dtFiltered = (From row In dtInput.AsEnumerable()
              Where row("Completion %").ToString() = "100"
              Select dtInput.Clone().LoadDataRow(New Object() {
                  row("Student ID"),
                  row("Course Name"),
                  row("Course Start Date"),
                  row("Course Completed Date"),
                  row("Course Start Date (Maths)"),
                  row("Course Completed Date (Maths)"),
                  row("Completion %")
              }, False)).CopyToDataTable()

Regards,

5

Hi @lrtetala

I would like to how to read that excel file in UiPath Studio?

Thanks.

6

Hi @peter.tun

Please find attached workflow
MergeCells.zip (64.7 KB)

This image shows a spreadsheet with student IDs and the course details for Python Programming and Maths, including start dates, completion dates, and completion percentages. (Captioned by AI)
This image shows a spreadsheet with student IDs and the course details for Python Programming and Maths, including start dates, completion dates, and completion percentages. (Captioned by AI)1146×682 20.9 KB

If you found useful please mark as solution

Happy Automation

7

Hi @Akash_Javalekar1 ,

Thanks for your help. I found some arrangements mistakes in Sheet1.

Under PythonProgramming column there should be only 2 sub columns “Course Start Date” and Course Completed Date".

Similarly, under Maths column, there should be only 2 sub columns “Course Start Date” and “Course Completed Date”

Lastly, Completion % column is a separate column. It should not be under Maths.

Could you help me correct those mistakes?

Thank you.

8

Hi @peter.tun got it let me check