Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
matthewjbryant
Creator II
Creator 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
Gysbert_Wassenaar

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

View solution in original post

7 Replies
vardhancse
Specialist III
Specialist III

ABC:

A,

B

FROM

[Table.xlsx]

(ooxml, embedded labels, table is Sheet1);

matthewjbryant
Creator II
Creator II
Author

Hi Sasi,

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

Gysbert_Wassenaar

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
Partner
Partner

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

Hi Parthiban,

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

Gysbert_Wassenaar

Try

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

FROM ...


talk is cheap, supply exceeds demand