How to replace all the spaces of Headers with "_"

Hello,

I need to replace all the spaces for each header with “_”.
Can any one help me with that.
I had attached sample excel file for reference
Thanks in advance.
Sample.xlsx (10.8 KB)

1.Read excel into a data table
2.For each item in datatable.Coumns
Assign item.columName = item.ColumnName.Replace(" “,”_").Trim.
3.Write range with add headers.

give a try on following

read range add headers -dtData

For each activity | TypeArgument: DataColumn | Value: dtData.Columns

  • AssignAcitvity: item.ColumnName = item.ColumnName.Replace(" ", “_”)

Assign Activity:
dtData = dtData.Clone

Write range: dtData | AddHeaders

Hi @ppr, Thanks for replying.

Where should I place the second assign activity. Inside the for each or outside ?

outside after the for each

It is cloning, does have a result of oempty datatable and will later replace only the header row in excel, but will not touch the rows below the first row

This is not working. nothing is being written to the excel sheet.

Do you have any other fixes?

see, if it is not working on first shoot, then lets find out what is happening e.g. debugging.

Can you share screenshots from your modelling or the XAML

Also is ensured that AddHeaders is activated on the Write range?

I had enabled add headers in both read range and write range.

Please find the screenshot

But you checked the Worksheet 911?

I’m writing it to a different sheet.

I guess I should have written it back to RAWdata sheet

So keep in mind the updated col names will be present there you have written out with write range. Compared to the screenshot 911.

So please check this and let us know the result. Thanks

It is not even working if I write back to RAWdata sheet.

Hi @Sirimalla_Karthik_Chandra ,

I’ve noticed that some columns are repeated, which means enabling Add Headers will won’t let you proceed:
image

Here is something I came up with, I hope you find it resourceful.
image

dt_sampleData.Rows(0).ItemArray.Select(Function(s) s.ToString.Replace(" ","_")).ToArray()

ReplaceColumnHeaders.xaml (6.6 KB)

Kind Regards,
Ashwin A.K

Hi @ashwin.ashok . Yes, there are few headers which are repeated. hence I’m renaming those headers with other name and you can find correct data in Sheet 101.

I will try your solution.

Thank you.

find starter help here for handling duplicate col names in excel
FixDuplicatedColNames.xaml (10.2 KB)

read in excel with read range without add headers for this

for finalizing this topic just do following:

  • decide your case, what is now the requirement (handling duplicate col names or col name corrections with replace)
  • share you final requirement with us and lets try to avoid XY Problem
1 Like

I get an error at for each row. I had attached screenshot for reference.

Hi @Sirimalla_Karthik_Chandra ,

That code was for Sheet1, not 101.
I’ll check on sheet 101 and let you know

Kind Regards,
Ashwin A.K

@ppr I had already fixed duplicate column names issue. Now I need to replace all the spaces of each header with “_”.

Ignore sheet1,Please refer to 101 sheet.

did the job:
grafik

ppr_ExcelBox_ReplaceSpaceOnColNames.xaml (8.6 KB)

2 Likes

@ppr Thanks for the effort. it is working.

As the information in excel is sensitive I havent shared the information below headers.

Now the issue is the bot is not coping all the info below the headers.

Can you please help with that.