Error in Pulling multiple excel sheets into QV when all headings are the same

[#1]

I'm trying to use your code but when I put 'XlsTables' after the ODBC connection then the 'SQLTables' instruction and it doesn't work. Do you know why this happens and what can I do to fix it?

Re: Re: Pulling multiple excel sheets into QV when all headings are the same

[fred_s]

Hi Saeed,

It's easier to follow the active thread:

Pulling multiple excel sheets into QV when all headings are the same

[fred_s]

Hi Saeed,

You've started a new discussion.
It's better to join the active thread with the same problem:

http://community.qlik.com/thread/95978

[#4]

ok.

Thanks

[swuehl · Accepted solution]

Yes, would be better to either keep to a single thread or at least reference the previous thread (as Fred then did).

Your problem is that SQLTables might return the table names single quoted (I think because the names are numbers).

This breaks the current logic.

If you are sure that your table names don't have a dollar sign in it, you can do it like

ODBC CONNECT TO [Excel Files;DBQ=Y:\Inventory2.xlsx];

XlsTables:
SQLTables;


DISCONNECT;


LET vRows = NoOfRows('XlsTables');


FOR i = 0 TO $(vRows)-1


LET vSheetName = purgechar(peek('TABLE_NAME', i,'XlsTables'),'$');

$(vSheetName):


LOAD
Store_no,
Article,
Kala_name,
Inventory
FROM 
(ooxml, embedded labels, table is $(vSheetName));


NEXT i


DROP TABLE XlsTables;