Read excel file header

Help Studio
, , , , , ,
1

I have an excel file and i want to read only the header of that excel file.
The problem is the header range is not always constant so cant specify the range while doing read range.

Question: how i can read only the header of the excel file without reading the entire excel data and store into datatable.

2

Hi @Pinky_AG

Use the .clone and you will get all the headers of the existing data table

For better understanding check below

dtExcelData= YourDataTableVariable.Clone()

Input:

image
image647×688 7.34 KB

Output:
image

Here YourDataTableVariable contains the original data table and dtExcelData contains all the headers of the YourDataTableVariable.

Regards

3

Hi @Pinky_AG

Use read range workbook activity to read the excel, in the properties of the read range workbook activity check the add headers option. Then it will read the data with headers and store in a datatable variable called Input_dt.

After read range use the assign activity and create a datatable variable called headers_dt

- Assign -> headers_dt = Input_dt.Clone()

Check the below workflow for better understanding,

_2024
_2024877×581 35.7 KB

Input -
image

Output -
image

Hope it helps!!

4

Correct…this can be done but i dont want to read the entire excel file and store it into datatable and than get the header…can this be done without reading to datatable first?

5

Okay @Pinky_AG

In that case you have to use the excel activities,

Use the Delete Rows activity to delete all the rows in excel, select the All visible rows option in What to delete option of delete rows activity.

Check the below screenshot for better understanding,

image
image675×668 20.3 KB

Hope you understand!!

6

No…i dont want to delete anything. Let me break the requirement:

  1. I am having a very large excel file foe which i am using oledb connection as it is large.
    2.but there is a small requirement. I want to just read the header of this big excel file for which header range in not constant.

How to just get the header name of this excel file without deleting anything or reading the entire file to datatable

7

@Pinky_AG

Try giving the range as 1:1

Cheers

8

1:1 gives all the headername + column 999 .
Can i just get only the header name not the empty header with value column1…column999?

9

@Pinky_AG

Try liek this…this will give all column names as array…you can use to create the datatable with those columns, Can be used inside use excel file

(From dc In Excel.Sheet("SheetName").DataTableValue.Columns.Cast(Of System.Data.DataColumn) Select dc.ColumnName).ToArray()

another way is you can get column count and then read using range which reads only the columns filled and only column namesExcel.Sheet("SheetName").Range("A1:A" + Excel.Sheet("SheetName").GetNumberOfColumns.ToString)

Hope this helps

cheers

10

I am not getting any function name as Excel…
Getting error as “Excel is not declearrd.”

1706160802971964605356592385947
17061608029719646053565923859471920×1440 414 KB

The exact code

rangeString = Excel.Sheet(“SheetName”).Range(“A1:A” + Excel.Sheet(“SheetName”).GetNumberOfColumns.ToString)

11

@Pinky_AG

Are you on modern activities?

This must be used inside use excel file modern activity

If you are on classic activities this would not work…

Only way for you would be use 1:1 and then delete all the columns starting from column1

Cheers

12

I am using this inside excel appliaction scope, but looks like it is not there for classic.

Is there anything similar for classic view? As i cant read the entire data table

13

@Pinky_AG

As mentioned above in classic we do not have such option…

The only other way instead of reading whole table is to use 1:1 and then delete the extra columns

Cheers

14

You can read range not check ‘header’ to get all header as a data row
then get 1st row is header
regards,

15

Thank you all…i got the solution.

16

Let mark what comment as solution to close topic and other developer can see solution for similar topic
Regards

17

Can you give us more details? Are there blank rows before the headers? Blank columns?

It sounds like you’ll have to read the entire sheet from A1 (with use headers unchecked), then use some logic to determine which row and column the headers start in.

18

What is the solution? You need to post it here so others may be helped in the future.

19

Hello All,

This is what I am doing.

  1. Using “read row” activity with range as “A1”. This is only giving me the firts row which is header and not blank row.
    Store this result in variable called “Getcolname”.

  2. Use assign activity and do this
    ColumnIndex(int32) = Getcolname.Count

  3. User assign activity and

Lastcolref(string) = if((columnIndex -1) \26 >0, ChrW(64+((columnIndex -1)\26)),“”)&ChrW(65+((columnIndex -1)Mod 26))&“1”

This will give the exact range based on column index. for example for index 45 it will come AS1.

Hope this helps

20

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