How to load from variable table name

[#1]

Hi,

My table name will not always be the same and there is no pattern to the name.  How do I get the load script to pull the data?  Your help is much appreciated.

[maxgro]

Hi

are you looking for a way to load multiple table from same db? In that case try this post

http://community.qlik.com/message/191708#191708

Regards

[stevedark]

Hi Lanthy,

At some point you must know what the table name is in order to be able to load from it?

You can use a variable in your load statement like this:

LOAD
    *

     ;

SQL SELECT
   *

FROM $(vTableName)

;

You could then have an input box on the screen to allow the user to input the table name before refreshing?

If the user does not know the table name, but you know the database it is in you could perhaps get a list of tables in the database (perhaps doing a select on SysObjects (in SQL Server) or by using the SQLTABLES command (for ODBC connections) and then use a PEEK statement to get the table name from that query into a variable before performing a load as above.

If you could elaborate on how you would envisage the table name should be derived or captured then I can give further advice on how to achieve that.

- Steve

[#4]

Hi Steve,

My script looks like this:

tempload:

LOAD *

FROM

(ooxml, embedded labels, table is Sheet1);

The file name will be known, but the the table name is unknown....Sheet1 will not always be named Sheet1 and I will not be able to predict the table name because it is a randomly generated long set up letters and numbers by an external source.

[#5]

Massimo,

That's not exactly what i'm trying to do, but i will bookmark it for future use! Thanks for your help!

[stevedark]

Hi,

Take a look at this thread:

http://qlikcommunity.qliktech.com/thread/18641

You can simply replace Sheet1 with @1 and it will load the first sheet.

Hope that helps.

Steve

[#7 · Accepted solution]

Steve,

@1 doesnt work for xlsx files, but per the another post on the same thread removing 'table is ___' works! 
So my script was:

(ooxml, embedded labels, table is Sheet1);

and updated to the below which works:

(ooxml, embedded labels);

Thanks for pointing me to that post!

[stevedark]

Glad that works for you. Another approach I have used is to create an ODBC

connection to the spreadsheet and then use SQLTables to list all sheets.

You can then PEEK these into variables. This is good for when the are

multiple sheets you want to load.

Cheers,

Steve