How to add prefix to all column name in excel, how to rename all columns

[Nidhi_Gupta1 (Nidhi G)]

hi

How to add prefix to all column name in excel, how to rename all columns
I have a excel with col names name age city
i want to add prefix to all column name
example
Data1_name|Data1_age||Data1_city

there are almost 20 columns, 100+rows

[Akash_Javalekar1 (Akash Javalekar)]

Hi @Nidhi_Gupta1
1.Read Excel file and stored in datatable dt_input.
2.Use For each activity Pass variable as dt.columns
inside for each take one assign activity
column.ColumnName = “Data1_” + col.ColumnName
3. Use the Write Range activity to write the updated DataTable back to Excel.

Happy Automation

[singh_sumit (Sumit Singh Tariyal)]

hie @Nidhi_Gupta1 can you use this method to replace you old column name with new column name

cheers happy automation

[ashokkarale]

@Nidhi_Gupta1,

You got two options

1. Read the excel file, modify the column name and write it back to excel.

To modify column name you can use this LINQ.

dt_input.Columns.Cast(Of DataColumn).ToList().ForEach(Sub(col) col.ColumnName = "Data1_" + col.ColumnName)

2. Another approach is directly update the column name in the excel file. Use this VBA code in Invoke VBA code activity.

Use this VBA code

Sub RenameColumns()
    Dim ws As Worksheet
    Dim i As Integer
    
    Set ws = ThisWorkbook.Sheets(1)
    
    For i = 1 To ws.UsedRange.Columns.Count
        ws.Cells(1, i).Value = "Data1_" & ws.Cells(1, i).Value
    Next i
End Sub

[SorenB]

Hello @Nidhi_Gupta1

You can loop the Columns of the datatable with a For Each activity.
Then make an If activity that checks if the CloumnName does not start with the prefix, and if not, then add it.

Download here:
Rename columns.xaml (11.0 KB)

Regards
Soren

[Tanvi_Gupta1 (Tanvi Gupta)]

1. Use Excel Scope application
2. Read Range Activity:
   Input: Specify the sheet name and range (e.g., "Sheet1").
   Output: dt (variable).
3. Assign Activity use :- For Each index In Enumerable.Range(0,dt.Columns.Count)dt.Columns(index).ColumnName = “NewColumnName” & (index + 1).ToString
4. Write Range to write the dt back in the excel

[Nidhi_Gupta1 (Nidhi G)]

hi @Akash_Javalekar1

it works, I added for each row then for each --nested
thanks !

[Nidhi_Gupta1 (Nidhi G)]

@ashokkarale

this also works, however I followed for each approach
can you please check -

I want to add 2nd sheet data for each row of 1st sheet data, cross join

Hi I want to add 2nd sheet data for each row of 1st sheet data, cross join. I tried using join option but it is not full join no of rows and col in both sheet will be dynamic sample ip n op attached, color used for understanding pls help, trying using for each but getting it hardcoded i want dynamic approach

[Akash_Javalekar1 (Akash Javalekar)]

@Nidhi_Gupta1 Glad it worked

Happy Automation

[SorenB]

Hello @Nidhi_Gupta1

Just be aware that if you re-run the same file several times, without using the If activity I suggested, then the prefix will be appended for each time you run it.

So

Name
Age
City

Will after run #1 be:

Data1_Name
Data1_Age
Data1_City

Will after run #2 be:

Data1_Data1_Name
Data1_Data1_Age
Data1_Data1_City

Will after run #3 be:

Data1_Data1_Data1_Name
Data1_Data1_Data1_Age
And so on...

Regards
Soren