Loop through column in excel sheet

As i have a excel sheet having data from A to E columns but the the column names are stored row by row in F column.
So that I need to fetch the column name from F and store into respective columns.
i.e
F1 -A1
F2-B1
F3-C1
F4-D1
F5-E1

Hi @agathiyanv

Try the below solution,

Claim.xaml (7.3 KB)

You can delete F column using Delete column activity if required.

Hey!

We can use Excel Transpose…

Have a view on this …

Regards,
NaNi

Hi,

Is your excel sheet like the following?

image

if so, the following sample might help you.

Sample20220804-4.zip (10.1 KB)

Regards,

can you explain the invoke method and columnNames assign activity?

Hi,

I added annotation as the following. How about this?

Regards,

If you don’t mind, can you data in excel sheet because we need a proper excel file.

Hi,

The above sample zip file contains xlsx. Can you check this?

Regards,

I mean in the output use write range and write the column names in the excel file
Combined.xlsx (12.0 KB)
This is the data we get from data scrapping and the field names are in F column
In the output we need to fetch column names from F and store in the respective cell
Note: The empty cells are not created manually

HI,

Alright. the following outputs dt to xlsx file and contains the output xlsx.

Sample20220804-4 (2).zip (15.3 KB)

Regards,

1 Like

This is the data we get from data scrapping and the field names are in F column
In the output we need to fetch column names from F and store in the respective cell
Note: The empty cells are not created manually
Combined.xlsx (9.1 KB)

Your code is working fine for our case and we don’t know about the columnNames assign activity. Could you please explain more detail?
We want to read only the F column and write into the same sheet from A1-E1 and we don’t want to read entire data in the workbook.
please Help!!!

Hi,

dt.AsEnumerable.Select(Function(r) r(dt.ColumnCount-1).ToString).Where(Function(s) not String.IsNullOrEmpty(s)).ToArray

This is LINQ Expression.
Select(Function(r) r(dt.ColumnCount-1).ToString) returns collection of item of the last column.
Where(Function(s) not String.IsNullOrEmpty(s)) removes null or empty items.
As result, this return array of column name.

We want to read only the F column and write into the same sheet from A1-E1 and we don’t want to read entire data in the workbook.

The following sample will work as you expect. Can you try this?
(As there are 4 column name in column F, datatable is written from B1 in this sample)

Sample20220804-4v3.zip (31.3 KB)

Regards,