How can we replace a entire column in excel?

excel
activities

#1

Hi @ All,

I have excel file in that one column I need to replace with Special characters.
How the way we can do?
without using for each row


#2

Hi @varunk,

Refer this link,

Use Excel Application scope and use send hot key to replace special characters.

Regards,
Arivu


#3

@varunk
As of my knowledge you have to use for each to update the values.
You can use Queries only to retrieve the data.

Regards,
Mahesh


#4

Hi @varunk,
You read the excel as datatable and do the for next loop . You can use the regex format by replace the special characters inside the for next loop . like this Regex.Replace(str, “[^a-zA-Z0-9_.]+”, “”, RegexOptions.Compiled)

Finally use the write range to update the excel sheet.

Regards
Balamurugan.S


#5

Hi @balupad14

I have 3 columns are there in excel,I want to replace second column with third column.


#6

@varunk

I think you can try like this
dt1=dt.DefaultView.ToTable(False,“Column 1”,“Column 3”)

dt1.Columns(1).ColumnName=dt.Columns(1).ColumnName

Regards,
Mahesh


#7

Hi @MAHESH1 @varunk ,
I have tested the code . really do we need “dt1.Columns(1).ColumnName=dt.Columns(1).ColumnName” this one.

Below I have attached the xml for test.

File : How can we replace a entire column in excel.zip (9.3 KB)

Regards
Balamurugan.S


#8

@balupad14
Why I used “dt1.Columns(1).ColumnName=dt.Columns(1).ColumnName” means
because If am not going to use this it will have the 3rd column name .
So iam assigning the column name with Second Column Name.

Regards,
Mahesh


#9

@MAHESH1,
yes you are right.

Here the exact sample : How can we replace a entire column in excel.zip (8.7 KB)

Regards
Balamurugan.S