Change the Columns Name and load data

[Shyam_Pragash]

Hi

i have 150 columns data in excel… i have to change the columns as per my requirement of columns heading… it taking long time get data in normal sequence method…

is any another way to completed it as it possible.

Input File Columns Heading	Output File Columns Heading(Expected)
Business	Type
Date	Report Date
Actual FTD	Outlet Visit Target FTD
FTD Target	Outlet Visit Actual FTD
FTD Actual	New outlet tagging FTD Target
NPD Target	New outlet tagging FTD Actual
NPD Actual	NPD VALUE FTD Target
Routes FTD	Routes Visit Actual FTD
JTD Target	NPD VALUE JTD Target
NPD Actual	NPD VALUE JTD Actual
JTD	NPD VALUE JTD
Bal to achieve
OVERALL FTD	OVERALL VALUE Target FTD
Actual FTD	OVERALL VALUE Actual FTD
Achievment % FTD	Overall Value Achievment % FTD
VALUE Target JTD	OVERALL VALUE Target JTD
VALUE Actual JTD	OVERALL VALUE Actual JTD
Bal to achieve JTD	Overall Value Bal to achieve JTD
…	……

Some times the columns heading also change to difference position.

Thanks
Shyam

[jack.chan]

@Shyam_Pragash
columnConfig.xlsx (9.6 KB)
input.xlsx (8.5 KB)
refer to this sequence
Sequence.xaml (12.2 KB)
you can read the columnConfig file and input file into separate datatables, then loop over columnCOnfig table and for each row,
assign
inputDt.Columns(CurrentRow("Input File Columns Heading").ToString).ColumnName = CurrentRow("Output File Columns Heading(Expected)").ToString

columnConfig.xlsx

input.xlsx

output

[Shyam_Pragash]

Hi @jack.chan

Some time input columns has been changed to difference place…

like Today date in A1… in future it comes in D1… That time what can i do ?

This not small columns with data ,… i huge columns and data…

Thanks
Shyam

[ushu (Usha kiranmai)]

@Shyam_Pragash Do you have to replace all the 150 column names

[Shyam_Pragash]

Hi @ushu

Yes …

[Nithinkrishna]

Hey @Shyam_Pragash

Just prepare a mapping table with input and output header values. I guess you already have it. Read it in either a dictionary or datatable.

For-each row or dictionary key,

Dt_Data.Columns(item) = dict_Mapping(item).ToString

This should do the job, no matter the column position.

Hope this helps.

Thanks
#nK

[supermanPunch (Arpan)]

Hi @Shyam_Pragash ,

Irrespective of the Column Positions, if we do know that the Column Names will always be the same in the Input, then we should be able to use the Config file as our Advantage to Map the Input Column Names to the Output Column Names.

Then we can Change the Column Names as Required in the Expected Output.

We also can order the Column names Later as Required.

But we can see that there is an Empty Value for one of the Column Names, why is that?

[postwick (Paul)]

Use Add Data Column to add the columns to your datatable with your desired column names. Then Read Range and deselect “Has headers” - just make sure the order you created the columns matches the order they appear in the source file.

The only catch here is I think your first row will then contain the old headers as if it’s a data row, which can easily be deleted from the datatable (Remove Data Row).

[Shyam_Pragash]

Hi All

Thanks

Thanks
Shyam