Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Ignore Excel Sheet Name

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.

1 Solution

Accepted Solutions
erich_shiino
Partner
Partner

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

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

View solution in original post

10 Replies
erich_shiino
Partner
Partner

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

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

View solution in original post

pat_agen
Specialist
Specialist

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
Partner
Partner

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

Not applicable
Author

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

erich_shiino
Partner
Partner

Thanks Rob. I didn't know this syntax.

It's going to be very helpful.

Not applicable
Author

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.

Not applicable
Author

Perfect answer - thanks!