SQLTable TABLE_NAME Variable not Matching Sheet Name for Excel Load

[alextomlins]

Hi Guys, What is wrong with the below script? The variable is pulling the table names from an excel sheet using SQLTables Function. The variable seems to be fine as i can see it in the Trace statement - but when i call the variable inserted into the load statement as  ' Table is '$(vTable)')' it seems as if it can't match the Variable and the sheet in Excel. In short there is a discrepancy between SQLTables table names and the excel sheet tables names because when i come to call upon the sheets in the load it cannot identify/find them.

LIB CONNECT TO 'CRTrackerTest (924034-qlik_924034-admin)';

unqualify *;

TABLES:

SQLTables;

DATA:

LOAD 0 as Dummy AutoGenerate 0;

For i = 0 To NoOfRows('TABLES')

   Let vTable = Peek('TABLE_NAME', $(i), 'TABLES');

    Trace This is vTables Value: $(vTable);

    Concatenate(DATA)

    LOAD

    "CR Filename",

    Implemented as Date,

    Description,

    Requestor

    FROM [lib://ExcelSheets (924034-qlik_924034-admin)/CR Tracker.xls]

    (biff, embedded labels, Table is '$(vTable)')

    ;

Next

[Anil_Babu_Samineni]

Alex Tomlins wrote:

    FROM [lib://ExcelSheets (924034-qlik_924034-admin)/CR Tracker.xls]

    (biff, embedded labels, Table is '$(vTable)')

I can think, Problem May coming here. can you share screenshot of error?

[marcus_sommer]

Try it with:

...

(biff, embedded labels, Table is [$(vTable)]);

- Marcus

[alextomlins]

Didn't change error unfortunately - I get the first alphabetical sheet in the SQL TABLE List. So April is not the left most but it is the first row in the SQLTable 'TABLES' Table....

[marcus_sommer]

The single-quotes around the sheetname are wrong.

- Marcus

[stigchel]

And you probably need to remove the $

Let vTable = purgeChar(purgeChar(Peek('TABLE_NAME', $(i), 'TABLES'), chr(39)), chr(36));

[alextomlins]

Still Getting an Error - So confusing - now i have;

Let vTable = purgeChar(purgeChar(Peek('TABLE_NAME', $(i), 'TABLES'), chr(39)), chr(36))

It looks like the variable and the data load Variable call match - what is going on lol.

I do notice that SQLTables is pulling an empty duplicate ending in an underscore??

These are tabs in the excel Sheet - It seems to match Apr 2018. I know that there are hidden sheets

The SQL Tables is kicking out the following;

[stigchel]

I Don't use Sense but qlikview, normally the same but maybe I'm missing something. If so maybe others can help you..

One suggestion maybe, do you have something like a file wizard that generates the load script for a file? Maybe you can try to load just this one tab Apr 2018 using a wizard and see if and what the differences are in syntax.

[alextomlins]

There is a wizard yes thank you! Interestingly it is with all sorts Dollars, single quotes and underscores. This must be the problem. which means SQLTables is pulling the data out exactly as the ODBC connection reads the sheet names. i will remove the purge character and see if that works

[marcus_sommer]

Your sheet-screenshot of the tabs looked like a xlsx and not like a xls and in this case the fileformat with biff would be wrong and needs to be changed to ooxml.

- Marcus