Write dataTable to excel with condition

[fk48]

Hi everyone,

I’m new to UIpath and would like to know how I can achieve adding rows in an excel table with a lot of records in it.

I have a filtered dataTable I want to insert after the last record of the sheet, so I used the following approach and it worked quite fine:

One column of the sheet is a unique ID. Now I only want to add rows, which are not part of the existing records. It would be logical to me to just add another filter to my ExtractDataTableFiltered table, to check if the ID of the record is already found in the existing excel file. But how could I achieve that?

For now, I only have this filters applied on the Filter Data Table activity:

Many thanks in advance!

[Sudharsan_Ka]

Hi @fk48

Can you send a sample input file here?

Regards
Sudharsan

[pavithra_pavi]

Hi @fk48 , Firstly welcome to the community.

As per my understanding, you have data in datatable and you want to add these data to an excel. ?

So to do this, use activity called “Appended Range” you can use any 1 of them based on your preference

The activity requires few inputs from you like : file name, sheetname and your datatable that you want to add

[fk48]

Hey, thanks for your message.

I know how to add the data from my dataTable in excel.

Im retrieving a table from the web and have it as dataTable in Uipath. In this table and in my excel sheet I have a column called ID. Then I only want to add the records in excel, which are not yet in my excel file. I would need to consider a condition, that I only add a record which ID is in my excel sheet yet.

[fk48]

Hi,

sure, this would be the original excel file
OriginalSheet.xlsx (8.7 KB)

And this would be the structure of the table Im retrieving
NewSheet.xlsx (8.8 KB)

Now I would only want to import the rows with the IDs 6,7 and 8 to the originalSheet because they are not part of the original sheet yet.

[pavithra_pavi]

@fk48 ,

Noted!!, so, have you tried using JOIN datatable ?

Here is link which has more information about : Story line of Join Data Table

There are multiple way you get get solution

1. using for each activity - this will be a time consuming if you are dealing with many records.
2. using join activity

• try to use FULL JOIN, which will return rows present in both datatable.

DT 1 : Excel data
DT2 : exported data
DT3 : final result

• Fetch all rows which has COLUMN ID “Blank”.
  Dt3 = DT3.select(“[ID]=‘’”).copyTodatatable()

you now have a datatable containing rows which are not in “excel” sheet

Hope this helps!

[Sudharsan_Ka]

HI @fk48

Checkout this xaml

Unique.xaml (9.4 KB)
NewSheet.xlsx (8.3 KB)
OriginalSheet.xlsx (8.7 KB)

Hope this Helps

Regards
Sudharsan

[fk48]

Thanks for your help!

What I missed mentioning is, that there are more columns in the real data. I would need the records of the original sheet not to be deleted because there are more columns in it.

When performing this activity, I guess the system would just take one duplicate and delete it, not matter in what table it was? Or would ist only delete records of the new table?

[Sudharsan_Ka]

The data will be deleted only from the datatable the data in the excel(Original) will be available there in the sheet @fk48

If you dont want the data to be deleted in the datatable too means
you can create new datatable value and clone the main dt

Unique.xaml (9.8 KB)

Check on the above updated xaml

Regards
Sudharsan

[fk48]

A join action seems like what I’m looking for. Since its gonna be a lot of records, it’s probably the most performant way to go as well.

But how would I perform the last step and why selecting blank ID? Every row has an ID but I only want to get the “new” IDs which are not part of the old excel records.

[fk48]

I’ve tried this solutions but keep getting an error: Merge Data Table: ID and .ID have conflicting properties: DataType property mismatch.

[Sudharsan_Ka]

Can you share the flow screenshot and the variable panel screenshot?

@fk48

[pavithra_pavi]

@fk48 ,

After you perform FULL JOIN, you will get data as shown below. Here there are blank cells in column ID as well you will get data which belongs in both the table,

the RED color data needs to be removed to get the expected outputs
To do this, You need to use “Filter datatable”

Attached zip contain code
JoinDataAndFilterdata.zip (21.1 KB)