Columns data to move another Columns in linq

[Shyam_Pragash]

Hi

As , i try insert excel data into sql server using Bulk insert activity in UiPath… i having some issue while inserting into sql server… Columns are same name with excel as well as sql server but position of columns Name are different… can you tell me how to rearrange the columns name as per the sql server in linq method because i have huge data in excel…

i tried normal method it will take 2+hrs of timing to insert the data in sql server.

Thanks
Shyam

[Anil_G (Anil Gorthi)]

@Shyam_Pragash

You can use set ordinal method to change the column positions as you need

Please check the details

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 …

Cheers

[Yoichi]

Hi,

If you want to rename each column name, the following my help you.

dt.Columns(idx).ColumnName = currentItem

Regards,

[Shyam_Pragash]

Hi @Yoichi @Anil_G

Thanks … i will try and come back…

@Yoichi i have 128 columns in excel sheet how to match this columns data move to this columns specificed.

Thanks
Shyam

[Anil_G (Anil Gorthi)]

@Shyam_Pragash

If you know the final index use the set ordinal with that index number…and remember set ordinal will scrabble the columns after the ordinal column…so start from last to arrange the columns and come to first

cheers

[Yoichi]

HI,

Do you already have datatable (schema) of SQL sever? if yes, the following expression may help you.

newColNames = dtSQL.Columns.Cast(Of DataColumn).Select(Function(dc) dc.ColumnName).ToArray()

Regards,

[Shyam_Pragash]

Hi @Yoichi

i cant able understand ur query…
input excel file == dt_Excel
this datatable to be manipulate like datatype (string, double, datetime,int) and then using bulk insert activity to be used to insert data to sql server… While using bulk insert input datatable columns name and sql server columns name should matched…i working on matching issue… so that i asked you…

Thanks
Shyam

[Yoichi]

HI,

Can you share more specific sample? The above sample seems there is just difference with column name.

Regards,

[Yoichi]

Hi,

How about the following?

 dt = dt.DefaultView.ToTable(False,{"Ticket ID","Date","Channel","Brand" ,"Product","State" ,"Zone","Amount" ,"Contact Name","Area" ,"Original Message" ,"Closed Date" ,"Assignee" ,"Call" })

Sample20230614-5L.zip (8.7 KB)

Regards,

[Shyam_Pragash]

Hi @Yoichi

Thanks…

Thanks
Shyam

[Yoichi]

HI,

Can you share your problem in details? If you need to add rows, AddDataRow activity or MergeDataTable activity may help you.

Regards,