Compare columns between two excel files and write values accordingly

[Orchestrator_Practice]

Hello UiPath Team,

My challenge. I have an Excel Template File that I want to write data to.
dt_Template: User ID, User Name, Address, Company, University: are the header names.

Another Excel file will be read, but the columns are not in the same order and not all columns will be in the file.
dt_One : University, User ID

How to read the column headers from second datatable, compare to the first, then write the information from dt_One to the appropriate column in the template?

Thank you

[Anil_G (Anil Gorthi)]

@Orchestrator_Practice

Please try like this

First read both the tables into datatable dt1 and dt2 using with headers options

Now to get column names of dt2 use vararray = (From dc In dt.Columns.Cast(Of DataColumn) Select dc.ColumnName).ToArray()

Now use for loop on dt1.Columns and change type argument to datacolumn

Inside loop use if condition with Not vararray.Contains(currentitem.ColumnName)

On the then side use delete data column activity and give the column as currentitem.ColumnName and datatable as dt1

After that use for loop on vararray and give a varible for index property in the for loop

And inside it use setordinal and use the index and currentitem as column name to re arrange the column

Check thsi method

How to Change the Ordinal of DataColumn

How to change the ordinal or position of the DataColumn to the specified ordinal or position? Is it possible to reorder Columns? If yes, please share a sample script. Issue/Query: How to change the ordinal or position of the DataColumn to the specified ordinal or position? Resolution steps: The above scenario is achievable with the help of Set Ordinal method. Sample Excel/ DataTable Data: Consider the below data [image] Launch UiPath Studio and create a sample process. Ensure to have …

In input argument pass the varibdex and in the targte object pas dt1.Columns(currentitem)

Now the final table will look like the second table…you can directly use write range

Cheers

[Yoichi]

Hi,

Hope the following sample helps you.

Sample20230613-1L.zip (14.5 KB)

Regards,

[Orchestrator_Practice]

In the assign activity with nRow, getting error that datarow cannot be converted to datatable. Also, the for each workflow is not in the downloaded sample.

[Orchestrator_Practice]

Thank you. Trying to work through this. Actually, I do not want the final table to look like the second table. The goal is for the data to be written into the template format each time and for the bot to be able to read data in columns whether they match the template format or not

[Orchestrator_Practice]

@Anil_G

(From dc In dt.Columns.Cast(Of DataColumn) Select dc.ColumnName).ToArray()

I’m wondering during the If condition if the current column is a match, it could write the data from DT1 to the Template?
However, trying to figure out the error in the If statement first

[Anil_G (Anil Gorthi)]

@Orchestrator_Practice

You wrote something like currentitem.column etc

Ehich looks wrong

It is looping on rows…and not columns

Cheers

[Yoichi]

Hi,

In my environment, it works and no lack of activities in the above uploaded sample. Can you check if there is error of dependencies in project panel? And/or try to load and run again?

Regards,

[Orchestrator_Practice]

When I unzip and load, it shows this:

[Yoichi]

Hi,

Which version of Studio do you use?

Regards,

[Orchestrator_Practice]

2023.4.2 is the version I use

[Yoichi]

Hi,

It’s same version…
I just re-created this project as the following. Can you try this?

Sample20230613-2L.zip (14.7 KB)

Regards,

[Orchestrator_Practice]

Thank you. Appreciate your help