Need to help to replace column headers in Excel

Hi All,

I’m working on Excel sheet and I’m getting error while working on that.
In my headers i have some special characters because of that I’m unable to read the Excel it is throwing me error.

I’m attaching my input and output sheet. Can anyone guide me.

I want to remove special characters from my headers



Hi @Beginner1234

Use Read Range Activity (Output=dataTable)

Use For each Row
The Input Should be dataTable.Columns.Cast(Of DataColumn)()

Use assign activity

col.ColumnName = System.Text.RegularExpressions.Regex.Replace(col.ColumnName, “[^a-zA-Z0-9]”, “”)

Hope this is usefull


currentitem=system.text.RegularExpressions.Regex.Replace(currentItem.ToString,“[^a-zA-Z0-9]+”," ")

Sequence1.xaml (8.7 KB)

refer this

@Shiva_Nikhil , @sanjay3 let me try both the solutions.

1 Like

@Shiva_Nikhil , @sanjay3 After replacing how can I write the entire data in the same sheet


use write range workbook activity to write back to excel

1.use excel scope activity in that read range activity.-o/p:dt_excel
“it will read even if we have spl. characters in header”
2.create a build data table with column names.-o/p: dt_build

dt_build=dt_excel.AsEnumerable.Select(function(x) dt_build.Clone.LoadDataRow({x(0).ToString,x(1).ToString},false)).CopyToDataTable
4.write range workbook activity,enable add headers in property

Use Write Range Workbook Activity

Thanks @sanjay3 it worked

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