Filter Excel and sav in different Sheet

Help Studio
, ,
1

I want to filter the column of “Industry” and need to write it into different Sheets.

For Example, I want the particular Revit family Name type “1”and"2" and write it into a new Sheet name (with the family name).

I used the Filtered data table Activity but when i run the program the two sheets are empty. I wil attach the sample file can anyone help me on it.thanks
Main.xaml (12.0 KB)
Split.xlsx (16.9 KB)

Excel1
Excel11840×825 220 KB

xaml1
xaml1456×821 36.8 KB

2

Hi @vignesh.maruthappan

→ Use Read range workbook activity to read the excel and store in a datatable called Input_dt.
→ Use assign activity to filter the Revit family Name with 1 by using LINQ Expression.

- Assign -> DT1 = (From row In Input_dt
                   Where row("RevitFamily Name").toString.Equals("1")
                   Select row
                       	).Copytodatatable()

→ Use assign activity to filter the Revit family Name with 2 by using LINQ Expression.

- Assign -> DT2 = (From row In Input_dt
                   Where row("RevitFamily Name").toString.Equals("2")
                   Select row
                       	).Copytodatatable()

→ Use write range workbook activity to write the DT1 in Sheet name 1.
→ Use another write range workbook activity to write the DT2 in Sheet name 2 in same excel.

Check the below workflow for better understanding,
Regex_Practice.xaml (20.3 KB)

Regex_Practice
Regex_Practice847×794 53.3 KB

Check the below output file,
Split.xlsx (19.1 KB)

Hope it helps!!

3

Hi @vignesh.maruthappan
Your Errors

  1. You have used wrong column Number in the Filter Data Table. instead you can use Name of the column: RevitFamilyName

    image
    image1920×285 12 KB

  2. In the RHS of filter data table you have to use CDbl(CurrentRow(0).ToString.Trim). Because the table column RevitFamily Name is of type Double.
    image

  3. In the Write Range workbook activity you wrote CurrentRow.toString which would create only one sheet System.Datarow something like this. You have to Write CurrentRow(0).toString to write 2 and 1 in sheet names.
    image

Split.xlsx (18.9 KB)
ExcelSplit.xaml (12.0 KB)

Here’s the attached working workflow of what you asked for.

Thanks

4

thanks and works great.

1 Like
5

thanks for the SolutionWorks good. Great

1 Like
6

I have one more doubt. If i need to save as the sheet to new excel with the revit family name. What should i do?sry i am new to ui path.thanks

7

You can write the workbook path as “/” CurrentRow(0).toString+“.xlsx”

8

Like this?
image

9

please place a " quote after UI\

Something like this
“C:\Users\vmaruthappan\Downloads\UI"+currentRow(0).ToString+”.xlsx"

1 Like
10

is it because of system activates updates? I have done so but. there is a compilation error.
image

11

“C:\Users\vmaruthappan\Downloads\UI\"+currentRow(0).ToString+”.xlsx"

Use this and close studio once and reopen it and run the code

1 Like
12

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