topic Re: Error in Pulling multiple excel sheets into QV when all headings are the same in QlikView

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

Sat, 02 Nov 2013 10:49:39 GMT

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

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

fred_s — Sat, 02 Nov 2013 11:26:25 GMT

Hi Saeed,

It's easier to follow the active thread:

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

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

fred_s — Sat, 02 Nov 2013 11:58:23 GMT

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

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

Sat, 02 Nov 2013 12:03:28 GMT

ok.

Thanks

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

swuehl — Sat, 02 Nov 2013 12:11:16 GMT

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;