how to load data from multiple sheets data with single load statement?

[#1]

how to load data from multiple  sheets  data with single load statement? please explain with examples

[evan_kurowski]

Hello BOGI RADHAKRUSHNA,

You could have two options for loading multiple Excel sheets with a single statement: one is wildcard load and the other is iteration.

For wildcard it is the scenario where you may have multiple spreadsheets all in the same directory and they are all formatted identically, down to the field names and the sheet names.

Obviously as the uniformity and quality of the spreadsheets' consistency decreases, additional adjustments must be made to take data quality and complexity into account. This illustrates a basic example

//Wildcard

[Multiple_Spreadsheets_via_Wildcard]:

LOAD

COL1,

COL2

FROM

[..\SPREADSHEET DIRECTORY\Financials_*.xlsx]

(ooxml, Embedded Labels, table is Sheet1);

//Iteration:
FOR EACH vSpreadsheet in FileList('C:\SPREADSHEET DIRECTORY\Financials_*.xlsx')

          [Multiple_Spreadsheets_via_Iteration]:

         LOAD

          COL1,

           COL2

           FROM

           [$(vSpreadsheet)]

          (ooxml, Embedded Labels, table is Sheet1);

NEXT

[ecolomer]

Hi there - you will need to put a nested loop construct in place, firstly for each workbook in the folder and then for each sheet in the workbook.

For each workbook you will require code similar to this:

for each vFileName in FileList('.\*.xls')

And then within that loop, you will need to loop for each sheet.  It is possible to get a list of all sheets in an Excel spreadsheet by connecting to it with an ODBC connection and calling the SQLTables function.  But as you know the sheets are sequential from 1 that seems overboard.  I would probably put an extra sheet 0 on each workbook that contained a cell with the number of sheets.  The code would then be a bit like this:

Temp_Sheets:

LOAD

     Sheets

FROM $(vFileName)

(biff, embedded labels, header is 1 lines, table is [0$]);

let vMaxSheet = peek('Sheets', -1, 'Temp_Sheets');

DROP TABLE Temp_Sheets;

for vSheetNo = 1 to $(vMaxSheet)

     Data:

     LOAD

          [... Field List ...]

     FROM $(vFileName)

     (biff, embedded labels, header is 1 lines, table is [$(vSheetNo)$])

next

Obviously this does rely on the sheets being sequential from 1, otherwise the load will fall over.  In this case explore the SQLTables route.

[#4]

Hi,

Yes the approch is same as above, suppose tou want to load the data for Jan-Dec spreaded on multiple sheets.

YOu can use this script...

Also, find the attached example...:

set vMonth=01,02,03,04,05,06,07,08,09,10,11,12;

for Each mon in $(vMonth)

let var=

if($(mon)='01','JAN$',

if($(mon)='02','FEB$',

if($(mon)='03','MAR$',

if($(mon)='04','APR$',

if($(mon)='05','MAY$',

if($(mon)='06','JUN$',

if($(mon)='07','JUL$',

if($(mon)='08','AUG$',

if($(mon)='09','SEP$',

if($(mon)='10','OCT$',

if($(mon)='11','NOV$',

if($(mon)='12','DEC$',

))))))))))));

forecast:

YOUR DATA FIELDS....

FROM

[Forecast.xls]

(biff, embedded labels, table is $(var));

NEXT

[#5]

Hi,

If we have different field names in sheets/files how can we do.....

[ecolomer]

see my answer ... on 14-08-2014

[rajeshqvd]

ODBC CONNECT TO [EXCEL;DBQ=C:\USERS\RAJESHG\DESKTOP\Multiexcelsheet.xlsx];

Let vfile ='C:\give your path\Multiexcelsheet.xlsx';

Exceltable:

SQLTABLES;

DISCONNECT;

for i=0 to NoOfRows('Exceltable')-1

Let vExcelsheets = PurgeChar(Peek('TABLE_NAME',i,'Exceltable'),'$''');

LOAD *

FROM

$(vfile)

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

NEXT

DROP Table Exceltable;

[senpradip007]

Hi,

Creating ODBC is the best way to reach this issue.

Thanks.

Pradip