Linq - Updating column in Datatable by accessing it by column index

[SagarSB]

Hi All,

I am using below Linq Query to update datatable and it is working as expected.

Query: dt.Select(“Subject=‘XYZ’”).ToList.ForEach(Sub(x) x(“Status”)=“Spike In cases”)

But the problem is I am unable to read excel with Add header property checked as it is throwing exception as “Status column is already belongs to this datatable”

Hence reading the excel without header and due to that unable to use above linq query with column name.

Could you please help me to understand the way to use Linq Query on datatable by using Col Index instead of Column Name.

Thanks…

[Gokul_Jayakumar (Gokul Jai)]

Hello @SagarSB , Check the Status Column Duplicate in Excel file. If there is duplicate, remove and read the excel again.

[EevaHanninen (EevaH)]

Try clearing the variable dt first (dt = new DataTable() or with Clear Data Table activity) before reading Excel into it.

[SagarSB]

@Gokul_Jayakumar Thank you for quick response. The file contains two status column but can not remove it as it it is standard input file and all columns needs to be there.

Can we make change to Linq query to tackle this?

[Gokul_Jayakumar (Gokul Jai)]

@SagarSB , Try to read range upto 1st status and process the Flow.
Other wise read the range without header and pass the index value

@SagarSB

dt.Select(“Subject=‘XYZ’”).ToList.ForEach(Sub(x) x(“Status”)=“Spike In cases”)

Instead of " Status" provide index , for example Postion-1 , A=0, B=1, C=2,…Z=25.

[supermanPunch (Arpan)]

Hi @SagarSB ,

Could you check with the below Component in Marketplace :

Novigo Solutions-Auto Rename Duplicate Column Name - RPA Component | UiPath...

It will read the Excel data and change the duplicated column name automatically.

[SagarSB]

@Gokul_Jayakumar How to pass index in Linq Query?

Option Strict On disallows implicit conversions from ‘String’ to ‘Double’. At line 1
getting above error if I use Index

[SagarSB]

Due to security reason can not use external package. Thank you for your input.

[Gokul_Jayakumar (Gokul Jai)]

@SagarSB Replace Status with index number

@SagarSB

dt.Select(“Subject=‘XYZ’”).ToList.ForEach(Sub(x) x(“Status”)=“Spike In cases”)

dt.Select(“Subject=‘XYZ’”).ToList.ForEach(Sub(x) x(3)=“Spike In cases”) for Status at D column

[EevaHanninen (EevaH)]

Try reading Excel without headers and use this:

dt.AsEnumerable().Where(Function(x) Convert.ToString(x(0)) = “XYZ”).ToList.ForEach(Sub(x) x(2) = “Spike In cases”)

I tried it with sample datatable. If you read Excel without headers it should create the datatable headers as Column1 (index 0), Column2 (index 1) etc, and the actual headers are the first row in datatable, right?

Script will update Column3 which is index 2.

Output of the dt before and after the script:

[SagarSB]

Thank you @EevaHanninen

[SagarSB]

Thank you @Gokul_Jayakumar

[kumar.varun2 (Varun Kumar)]

Hi @SagarSB

You can also try this approach provided by @ppr in the blow post

How to handle read range if an excel contains two same columns names? - Help / Activities - UiPath Community Forum