topic Re: how to load data from multiple sheets data with single load statement? in QlikView

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

Thu, 14 Aug 2014 03:46:38 GMT

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

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

evan_kurowski — Thu, 14 Aug 2014 04:01:18 GMT

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);

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

ecolomer — Thu, 14 Aug 2014 04:02:13 GMT

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.

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

Thu, 14 Aug 2014 04:12:40 GMT

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));

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

Wed, 17 Jun 2015 18:47:02 GMT

Hi,

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

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

ecolomer — Wed, 17 Jun 2015 19:14:21 GMT

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

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

rajeshqvd — Tue, 26 Jun 2018 12:28:20 GMT

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)]);

DROP Table Exceltable;

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

senpradip007 — Tue, 26 Jun 2018 13:20:56 GMT

Hi,

Creating ODBC is the best way to reach this issue.

Thanks.

Pradip