Renaming multiple datatable columns at the same time

Help
1

Hi! How can I loop through the columns in a datatable and rename them? E.g. I’ve many columns titled Qn1, Qn2, Qn3… Qn100.

Currently I’m using send hotkeys in excel to find “Qn” and replace it with the desired string e.g. “ABC” to get ABC1, ABC2, ABC3… ABC100, then reading it into a datatable. But this seems rather inefficient, so is there a better way to do the same without using hotkeys, or after reading it into a datatable e.g. using replace or something similar?

I’ve read this topic (Data Table Loop Through Columns and Rename each column) but I just got the same error as the user did, and the last solution proposed doesn’t work for me because there’s hundreds of columns in the excel sheet so they can’t be renamed individually.

Thanks!

2 
columnCount = myDataTable.Columns.Count

For Each index in Enumerable.Range(0,columnCount).ToArray
{
     if(myDataTable.Columns(0).ColumnName.Contains("Qn"))
             myDataTable.Columns(0).ColumnName = myDataTable.Columns(0).ColumnName.Replace("Qn","ABC")
}

Regards,
Karthik Byggari

3

Thanks for your reply. But this code just replaces the “Qn” with “ABC” in Column(0), and doesn’t loop through to replace it for the rest. I tried this:

 if(myDataTable.Columns(index).ColumnName.Contains("Qn"))
         myDataTable.Columns(index).ColumnName = myDataTable.Columns(index).ColumnName.Replace("Qn","ABC")

But it just gives an error. How can I change the code so it will loop through and replace all the columns with “Qn” in it?

4

Thanks for your help, i’ve built on that to find a solution:

columnCount = myDataTable.Columns.Count
For Each index in Enumerable.Range(0,columnCount).ToArray
{
             myDataTable.Columns(index).ColumnName = Regex.Replace(myDataTable.Columns(index).ColumnName.ToString,"(Qn)","ABC")
}

TypeArgument of the For Each should be set to Int32.

2 Likes
5

Hey,

Please mark which ever logic you got to solve the issue.

Regards,
Pavan H

6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.