Add New Column Values To A Datatable using existing Column Values And Change Format Of The Entire Column Values to Number (Using Linq - Both The Case)

I have a excel with 10000 records, My requirement is to do following using LINQ. Please help me in this. Suggest only faster way (LINQ), for each data row will be very slow.

Data Table

Q1 : I want to add a column name Header4 with value AAAAAA0123456 , BBBBBB1234567 etc. I want to achieve using LINQ.

Q2: I want convert Header2 values to Number (while reading from excel it is in String format). I want to achieve using LINQ.

@loginerror @Palaniyappan @Manish540 - Any help will be appreciated.

@vickydas @ppr @shubhiyadav - Any help will be appreciated.

Hi @Ashish6541 ,

Add Header4 Column using Add Data Column activity & create new output Data Table.

Q1 (LINQ) :
(From d In dt_Input.AsEnumerable
Let a = d(0).ToString + d(1).ToString
Select dt_Output.Rows.Add(d(0), d(1), d(2), a)).CopyToDataTable

Q2 :
Use below activity to convert Data Column type :

Hope this is helpful. :smiley:

Hi @Shraddha_Gore - Thank you for the response.

Yes, this will work. I just want to make it bit dynamic. Now we have only three column, so it is easy to add one more column using Add method. But I have datatable with 96 Columns, where it is difficult to add every column name as part of the code. So is there any other ways to achieve it?

This is market place activity (Balareva), which I am aware. Is there a way using linq it self we can achieve it.

@nikhil.s @desineediaditya @Kotla_Gunasekhar - Any help appreciated.

there are several techniques for shortening the explicit column refeencing e.g. by using the entire itemarray or parts from it (e.g. take, skip). But for this we do need the details from the datacolumn structures like 1-96 is to use, 97 is concat of Y,X…

ususally we can do like following:

  • clone the datatable: dtData2 = dtDataClone
  • add a datacolumn with corresponding datatype, colname: Header2_1
    run following LINQ

dtData2 =
(From d in dtData.AsEnumerable
let cc = Convert.ToDouble(d(Header2))
let ra = d.ItemArray.Append(cc).toArray
Select dtData2.Rows.Add(ra)).CopyToDataTable

  • move the Column Header2_1 with an invoke method activity:

    • dtData2.Columns(Header2_1) | SetOrdinal | Parameter dtData2.Columns(Header2).Ordinal
  • delete old Datacolumn with delete datacolumn activity

As an alternate to the conversion LINQ maybe using an expression for the new added datacolumn can be checked as well