Search and copy data from an excel file to another

Dear all,
i’m new using UiPath and am developing a bot for excel activities.
I have 2 excel files.One file (database.xls) consist of a database containing all data collected during the year (lot numbers,…).
The other (batch nr.) contains a list of lot numbers for which i have to search and copy some specific data i need from the database, in order to perform a data analysis.

I already managed to perform the search part in the database using filter activity. But I could not manage to copy the data i need yet.

Can you please help ? Do you have suggestions how to perform that search and copy function ? perhaps using another method.
I’m not an expert in programmation at all.

You can find attached my Uipath file and both excels files.

Thank you in advance

You can use Excel Application Scope with Read Range to read data from Database.xlsx sheet first. and store in a DataTable1
Then using another Excel Application Scope activity outside the above scope activity, read data from batch nr…xlsx sheet and store in DataTable2. Loop using for each row on DataTable2 you can use Lookup Data Table activity to search for your element in the data table - the CellValue output can be used to update your batch nr…xlsx sheet using Write Range activity.

Refer this link

Dear @kaderms,
many thanks for your answer.

I followed your explanation and managed to find my values using the Lookup Data Table activity and to copy them in my excel file. Could you please check it and give me your input ?

One more question: if I want to search and copy the results from another column of the database.xlsx should i use a separate Lookup Data Table activity for each column in which i want to search and copy data ?

Thanks

@Pinetti which column data u need to copy from database excel?

@Manjuts90
all these: from AA to AF
IPC TA Höhe [mm]
IPC TA Einzelgewicht Kontrolle [mg]
IPC TA Gew. 10 Tabs [g]
IPC TA Härte [N]
IPC TA Abrieb [%]
Yield TA [%]

You can get the RowIndex output from the Lookup data table activity.
Using the row index you can get any other column value from the data table like
DT1.Rows(row_index)("IPC TA Höhe [mm]").ToString
DT1.Rows(row_index)("IPC TA Einzelgewicht Kontrolle [mg]").ToString
…etc.

Do check the row index value if the lookup value is not in the datatable, for error handling.

@Pinetti i have created the solution. Check the file once.
PQR.zip (27.2 KB)

Hi @kaderms, @Manjuts90
thank you both for your inputs. These are working very well :grinning:

Can you please tell me how to create variables out of Column Headers ?
I can read the Column Headers using For each item in Datatable.Columns and would like to create a new variable with each Column Header that is read.

Can you please help ?

Thanks in advance

You can use a dictionary variable and store your dynamic variables and values into it. That’s the easiest way that I have come across to use dynamic variables at run time.
Quick guide to using dictionaries. Pay attention to the highlights.

Hi @kaderms

I tried another way, I put the targeted column names -for which I want to search the associated values in the database.xlsx file - in a new sheet of the Batch Nr…xlsx file.

The problem i’m facing is that the cellvalue variable of the Lookup data table activity remains empty. I do not understand why…
I attached the corresponding files, could you please have a look on it ?

Thanks in advance

You got confused between the Lookup Column - ColumnName and Target Column - ColumnName
Here is a quick view of how Lookup Datatable works

Indeed, stupid mistake, thank you for the explanation :sweat_smile:

Now that the data are properly collected, I could notice that they are all stored in a same column.
I would like to store the data from each Database column in the corresponding column I create in the Data Table DT3

I tired many things but really did not find the appropriate solution yet :sweat:

Any Idea ? :thinking:

that is because you are adding only one item in your DT3. So it gets added into one column.

Rather than that you can do ArrayRow like

{DT1.Rows(row_index)(“IPC TA Höhe [mm]”).ToString, DT1.Rows(row_index)(“IPC TA Einzelgewicht Kontrolle [mg]”).ToString, … , … , …} etc

Follow this

@kaderms That worked perfectly :slight_smile:

However, I’m now facing a new issue:
If I have Lookup Values in the Batch nr. file for which the Lookup activity does not find a matching value in the Lookup Column, the row_index value becomes -1 and that generates an error during the Add data row activity: There is no row at position -1

Is there a way to fix that problem and make the row_index variable to resume counting and avoid getting the -1 value ?

For instance: the last matching value was performed for row_index =6. During the next For Each loop, no matching value is found between Lookup Value in Batch nr. file and Lookup Column of Database.xlsx file. So the row_index value should become -1. Can we force it to become 7 ? Or do you suggest another way to fix it ?

RowIndex output from LookupDataTable activity is the row number at which your lookup value is available, which you have stored as row_index
if lookup value is available - row_index would be the row number where your lookup is available
if lookup value is not available - row_index would be -1
Ideally your failure is like in the line below, where -1 is not a valid row for any datatable.

if your intent is to get only items that are available in the database sheet, then you can use an If activity to check if the row_index value is -1, if yes, ignore. if it is not then continue with getting the corresponding values and entering into the datatable needed.

if you also want to know what all items are not available in the database sheet, you can do the same thing as above, but in the true condition for If activity, you can either enter this record (for which lookup is not available) into another data table for your reference later. Or you can enter it into the same data table with values as {“NA”,“NA”,“NA”…etc} other than

Thank you :slight_smile: i missed the logic but the If function clearly solved the problem.

May I ask you something more?
I will have to search data in different database. Therefore the number of parameters for which i need to collect these data will vary from one database to another.

For instance:
If I have to collect data for 6 parameters my Add data row activity will look like this:
{DT1.Rows(row_index)(variablesDictionary(1)), DT1.Rows(row_index)(variablesDictionary(2)), DT1.Rows(row_index)(variablesDictionary(3)), DT1.Rows(row_index)(variablesDictionary(4)), DT1.Rows(row_index)(variablesDictionary(5)), DT1.Rows(row_index)(variablesDictionary(6))}

but if i have more parameters, or even less, i will have to modify the that field manually and add or delete code.
Is there a way for me to define the number of parameter for which i will collect the data (when I launch the Robot for example) and then automatically generate the exact number of entries between my brackets ? {…,…,…,…,…,…,…,…,…,…} or {…,…,…}, etc