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.
Hello @SagarSB , Check the Status Column Duplicate in Excel file. If there is duplicate, remove and read the excel again.
Try clearing the variable dt first (dt = new DataTable() or with Clear Data Table activity) before reading Excel into it.
@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?
@SagarSB , Try to read range upto 1st status and process the Flow.
Other wise read the range without header and pass the index value
Instead of " Status" provide index , for example Postion-1 , A=0, B=1, C=2,…Z=25.
Hi @SagarSB ,
Could you check with the below Component in Marketplace :
It will read the Excel data and change the duplicated column name automatically.
@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
Due to security reason can not use external package. Thank you for your input.
@SagarSB Replace Status with index number
dt.Select(“Subject=‘XYZ’”).ToList.ForEach(Sub(x) x(3)=“Spike In cases”) for Status at D column
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: