Ignore Excel Sheet Name

[#1]

Hi,

Can anyone please let me know if there is any way to totally ignore the sheet name when loading excel spreadsheets?

I have created a for each loop to run through a folder and read all the .xlsx files, but each file will have a sheet with a different name.

Please advise.

Thanks.

[erichshiino · Accepted solution]

Try to remove the part which says 'table is sheetName$'

If I remember correctly it works, ALL of them must have the same layout.

[pat_agen]

hi,

just remove the reference to table name in your biff statement so it reads like this

LOAD field1,
 field2
 etc. etc.
FROM *.xls (biff, embedded labels)[\code]
this will read the first tab irrespective of name from any xl file it opens. </body>

[disqr_rm]

I had similar issue, and ended up using ODBC for Excel files. This was the only way to loop through all the "Sheets" in the excel file. So basically I had to loop through all the Excel files in the directly and for each Excel file I had to loop through all the sheets, and generate a QVD like ExcelFileName-SheetName.Qvd.

If you need similar, you may need to install Excel ODBC drivers, if you are using 64 bit machine, and variable-ize ODBC connection within your loop to connect to Excel files.

Rakesh

[#5]

Thanks guys! Leaving out the table portion worked perfectly.

Thank you Rakesh, but it isn't 100% what I am looking for...

[rwunderlich]

Note that you can also refer to a sheet by it's number, ignoring the name.

table is @1

Rob

[erichshiino]

Thanks Rob. I didn't know this syntax.

It's going to be very helpful.

[#8]

Hi Rob,

I tried that, based on some of your older posts, but I had no success.

Any ideas why?

Thanks-

[rwunderlich]

I missed the point in your first post that you are using xlsx. The @n form does not work with xlsx and I don't know of a direct equivilent.

[#10]

Perfect answer - thanks!