Change the Columns Name and load data

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

@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
image

columnConfig.xlsx
image

input.xlsx
image

output
image

1 Like

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

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

Hi @ushu

Yes …

1 Like

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

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?
image

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).

Hi All

Thanks :slight_smile:

Thanks
Shyam

1 Like

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