How to add Excel file Headers into another DataTable

Help Studio
1

I am trying to add Excel file Headers into newly created Datatable. Can anyone please please help to fix this.

I have attached the dummy workflow which I have created.
I need to copy Headers from “Wiki_Macro_Column_Headers.xlsx” file into Datatable created from CSV file Before apply filter in Datatable and that should reflect into file “Entire_Updated_Data.xlsx” after created.

Dummy Project.zip (191.6 KB)

2

@nilesh.mahajan,

Plz check Add Headers property of Read Range when you plan to read excel sheet.
you can also filter datatable based on column index 0, 1, 2 etc.

3

Is it not possible ti filter datatable as per column header name? instead of index.
The reason is I am not sure how many column will be there in file, But header name will be there on any index number.
Hence I want to filter the datatable as per name.

4

@nilesh.mahajan,

Plz try with Add headers property, you will achieve it.

5

Hi,

There are some ways to achieve it.
The following is to use string manipulation. Hope this helps you.

img20210608-2
img20210608-2673×747 17.9 KB

Sample20210608-1.zip (11.8 KB)

Regards,

6

That I already used and that will give me the output as Column1, Column2,…like this.

image
image767×89 4.62 KB

image

But I need this column headers as like below.
image

And this Column headers I will export from already saved Excel file.

My simple requirement is Before applying filter on datatable Column1, Column2…headers should replace with standard headers.

7

HI,

Another way: iterate datacolumn. (See sequence.xaml in the attached sample)

img20210608-3
img20210608-3437×873 16.8 KB

Sample20210601-1-v2.zip (2.8 KB)

Regards,

8

@nilesh.mahajan,

I guess by looking at screenshot the file which you use has no headers, right?
that is why their names are as column 1, 2
before you read it, will have to write column names using write cell activity or follow @Yoichi’s solution.

9

Hi Yoichi,
Can you please tell me what you mentioned in Assign activity under For Each.
Unfortunately As per sample solution it is not working.

10

Hi,

Sorry for i attached a wrong project.

Assign activity under For Each.

dtResult.Columns(idx)=item.ColumnName

TypeArgument of ForEach is DataColumn

Regards,

11

thnx for reply,

it give me the error.
image

If possible can you please upload correct project.

12

Hi,

Can you try the following? (Sorry, I cannot check expression in Studio now due to I’m out)

dtResult.Columns(idx).ColumnName=item.ColumnName

Regards,

13

Yoichi, I have put the Columns(idx) as Column(0), but every column gets updated in the same column only, How I can increment the column?

image

14

Hi,

We need to set idx at index property of For Each.

The following is what I want(ed) to share. Can you check this?

Sample20210608-1_v3.zip (12.0 KB)

Regards,

15

thnx Youchi, it is working as per my expectation.

1 Like
16

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