Load Multiple Excel Sheets *Without* ODBC Connection

[matthewjbryant]

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

[vardhancse]

ABC:

A,

B

FROM

[Table.xlsx]

(ooxml, embedded labels, table is Sheet1);

[matthewjbryant]

Hi Sasi,

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

[Gysbert_Wassenaar · Accepted solution]

Try:

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

for each m in $(i);

     LOAD   A, B

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

next

[parthiband]

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]

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]

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