Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

matthewjbryant
Contributor II

Load Multiple Excel Sheets *Without* ODBC Connection

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

  A,

  B

   FROM

   [DOC.xlsx]

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

  

NEXT

1 Solution

Accepted Solutions

Re: Load Multiple Excel Sheets *Without* ODBC Connection

Try:

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

for each m in $(i);

     LOAD   A, B

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

next


talk is cheap, supply exceeds demand
7 Replies
vardhancse
Valued Contributor II

Re: Load Multiple Excel Sheets *Without* ODBC Connection

ABC:

A,

B

FROM

[Table.xlsx]

(ooxml, embedded labels, table is Sheet1);

matthewjbryant
Contributor II

Re: Load Multiple Excel Sheets *Without* ODBC Connection

Hi Sasi,

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

Re: Load Multiple Excel Sheets *Without* ODBC Connection

Try:

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

for each m in $(i);

     LOAD   A, B

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

next


talk is cheap, supply exceeds demand
parthiband
Contributor

Re: Load Multiple Excel Sheets *Without* ODBC Connection

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,

matthewjbryant
Contributor II

Re: Load Multiple Excel Sheets *Without* ODBC Connection

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?

matthewjbryant
Contributor II

Re: Load Multiple Excel Sheets *Without* ODBC Connection

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

Try

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

FROM ...


talk is cheap, supply exceeds demand
Community Browser