topic Re: Load Multiple Excel Sheets Without ODBC Connection in QlikView

Load Multiple Excel Sheets Without ODBC Connection

matthewjbryant — Mon, 07 Jul 2014 09:55:24 GMT

Hi All,

I've been trying for a while now to loop through a list of sheets (JAN, FEB, ... , NOV, DEC), but I'm having issues loading more than one sheet. The following works, but as soon as I replace 'JAN' for 'JAN, FEB' I get the error 'Unknown file format specifier: FEB'. Is there a syntax nuance that I need to know or am I way off?

SET i = JAN;

FOR Each i in i;

LOAD

FROM

[DOC.xlsx]

(ooxml, embedded labels, Table is $(i));

Re: Load Multiple Excel Sheets Without ODBC Connection

vardhancse — Mon, 07 Jul 2014 10:01:05 GMT

ABC:

FROM

[Table.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Load Multiple Excel Sheets Without ODBC Connection

matthewjbryant — Mon, 07 Jul 2014 10:21:38 GMT

Hi Sasi,

How is this meant to loop through multiple sheets and load the data?

Re: Load Multiple Excel Sheets Without ODBC Connection

Gysbert_Wassenaar — Mon, 07 Jul 2014 10:25:22 GMT

Try:

SET i = 'Jan','Feb','Mar';

for each m in $(i);

LOAD A, B

FROM [DOC.xlsx] (ooxml, embedded labels, Table is [$(m)]);

Re: Load Multiple Excel Sheets Without ODBC Connection

parthiband — Mon, 07 Jul 2014 10:30:09 GMT

Hi Mathew,

See the below code.

The Inline table need not to contain all ur excel fields.

Just have 1 or 2 fields.

TestTable:

LOAD [

YourSampleFieldName, YourSampleFieldName1

];

LET vDataFolder= '..\YourDataPath\';

FOR EACH vFile in filelist('$(vDataFolder)*.xlsx');

let vFileName = SubField(vFile,'.',1);

let vFileName = mid(vFileName, index(vFileName, '\', -1) + 1);

//Connection to Excel Files

OLEDB CONNECT32 To [Excel Files;DBQ=$(vFile)];

//Read & Store the Sheets in Buffer

Temp_tables:

sqltables;

for iSheet = 0 to NoOfRows('SheetName') - 1

for iSheet = 0 to NoOfRows('Temp_tables')-1

let vSheetName = Peek('TABLE_NAME', iSheet, 'Temp_tables');

Let vSheetName=Replace(vSheetName,'$','');

Let vSheetName=Replace(vSheetName,Chr(39),'');

Concatenate(TestTable)

LOAD *

FROM

[$(vFile)]

(ooxml, embedded labels, table is [$(vSheetName)]);

DROP Table Temp_tables;

NEXT vFile;

DISCONNECT;

Thanks,

Re: Load Multiple Excel Sheets Without ODBC Connection

matthewjbryant — Mon, 07 Jul 2014 10:35:31 GMT

You beauty! This is perfect. I didn't need the '' around each i value, but the code is great. Cheers mate!

One question, with my variable file name I was able to pull in text to my columns. Do you know a way I can easily create a column that records 'JAN' in all the rows from JAN, 'FEB' in all the rows from FEB, ... etc. using this method?

Re: Load Multiple Excel Sheets Without ODBC Connection

matthewjbryant — Mon, 07 Jul 2014 10:36:29 GMT

Hi Parthiban,

I'm sure this works, but I asked for a solution that didn't use an ODBC connection.

Re: Load Multiple Excel Sheets Without ODBC Connection

Gysbert_Wassenaar — Mon, 07 Jul 2014 10:41:37 GMT

Try

LOAD aaa, bbb, '$(m)' as ccc

FROM ...

topic Re: Load Multiple Excel Sheets *Without* ODBC Connection in QlikView

Load Multiple Excel Sheets *Without* ODBC Connection

Re: Load Multiple Excel Sheets *Without* ODBC Connection

Re: Load Multiple Excel Sheets *Without* ODBC Connection

Re: Load Multiple Excel Sheets *Without* ODBC Connection

Re: Load Multiple Excel Sheets *Without* ODBC Connection

Re: Load Multiple Excel Sheets *Without* ODBC Connection

Re: Load Multiple Excel Sheets *Without* ODBC Connection

Re: Load Multiple Excel Sheets *Without* ODBC Connection

topic Re: Load Multiple Excel Sheets Without ODBC Connection in QlikView

Load Multiple Excel Sheets Without ODBC Connection

Re: Load Multiple Excel Sheets Without ODBC Connection

Re: Load Multiple Excel Sheets Without ODBC Connection

Re: Load Multiple Excel Sheets Without ODBC Connection

Re: Load Multiple Excel Sheets Without ODBC Connection

Re: Load Multiple Excel Sheets Without ODBC Connection

Re: Load Multiple Excel Sheets Without ODBC Connection

Re: Load Multiple Excel Sheets Without ODBC Connection