topic Ignore Excel Sheet Name in QlikView

Ignore Excel Sheet Name

Wed, 20 Apr 2011 15:11:35 GMT

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.

Ignore Excel Sheet Name

erichshiino — Wed, 20 Apr 2011 15:35:11 GMT

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

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

Ignore Excel Sheet Name

pat_agen — Wed, 20 Apr 2011 15:35:32 GMT

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>

Ignore Excel Sheet Name

disqr_rm — Wed, 20 Apr 2011 16:05:35 GMT

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

Ignore Excel Sheet Name

Wed, 20 Apr 2011 17:03:13 GMT

Thanks guys! Leaving out the table portion worked perfectly.

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

Ignore Excel Sheet Name

rwunderlich — Wed, 20 Apr 2011 20:23:11 GMT

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

table is @1

Rob

Ignore Excel Sheet Name

erichshiino — Wed, 20 Apr 2011 20:27:23 GMT

Thanks Rob. I didn't know this syntax.

It's going to be very helpful.

Ignore Excel Sheet Name

Thu, 21 Apr 2011 08:05:53 GMT

Hi Rob,

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

Any ideas why?

Thanks-

Ignore Excel Sheet Name

rwunderlich — Thu, 21 Apr 2011 17:30:21 GMT

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.

Re: Ignore Excel Sheet Name

Mon, 22 Aug 2016 08:43:45 GMT

Perfect answer - thanks!

Re: Ignore Excel Sheet Name

Mon, 22 Aug 2016 08:43:54 GMT

Perfect answer - thanks!