How can i read excel file using column numbers instead of column names

Question

Post reply

How can i read excel file using column numbers instead of column names

jahnavi7643

SSC Journeyman

Points: 92
More actions
October 6, 2016 at 7:46 am

#330333

Hi
I have some excel input files but in those files column names text(Header fields) are varying one file to another.so, How can I read those excel files using column numbers instead of column names.
can anyone please suggest me how to read excel file without using column names.
Thank you
Janu:-)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Sure. Skip the header rows by setting 'Data Access Mode' to 'Table name or view name variable'.

Set the variable's value to a range which excludes header rows, eg:

sheet1$A2:D

which is columns A to D, from row 2 onwards, on sheet 'sheet1' (Excel adds the '$' automatically).

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

And good luck handling Excel's semi-random data-type guesswork 🙂

jahnavi7643 SSC Journeyman Points: 92 More actions · Answer 3

Thanks for your response.

select * from

[Sheet1$A2:AZ]

it's working correctly but this query reads the data with second row data as column names.

I need second row data also without header.

for example my input excel files are

file1:

id name addr

1 ABC hyd

2 XYZ vzg

file2:

no fname place

3 CDE NLR

4 FGH PKL

How to read the above excel files with same query?

can you please suggest me.

Thanks in advance

Janu:-)

Thom A SSC Guru Points: 99143 More actions · Answer 4

Your Connection Manager must be set up to treat the first row as a header. Go to your Connection Manager properties for the Excel sheet, and change FirstRowHasColumnName to FALSE.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk