topic Load multiple excel Worksheets with different structure in QlikView

Load multiple excel Worksheets with different structure

Tue, 10 Jun 2014 15:56:39 GMT

Hello guys,

I am now facing another challenge. I have to load multiple worksheets each with different structure from one XLS file into QV. Result should be script which creates for every sheet one table in QV.

I was able to load worksheets with different names but same structure according to this thread:

Load multiple excel files from Multiple excel sheetsinto QV

But can QV allow to do this automatically even for tables with different structure?

My current code crashes probably on using Load * for XLS files:

for each

vFile in FileList('$(vPathXLS)\File.xlsx')

ODBC CONNECT64 TO [Excel Files;DBQ=$(file)];

tables:

SQLtables;

DISCONNECT;

Let vSheetCount = NoOfRows('tables');

FOR i = 0 to $(vSheetCount) -1

LET vSheetName = purgeChar(purgeChar(peek('TABLE_NAME', $(i), 'tables'), chr(39)), chr(36));

$(vSheetName):

LOAD *, '$(sheetName)' as Sheet

FROM [$(vFile)] (ooxml, embedded labels, table is [$(sheetName)]);

STORE $(vSheetName) into $(vPathQVDStore)\Output_$(vSheetname).qvd;

DROP TABLE $(vSheetName);

NEXT i

DROP TABLE tables;

Thanks,

James

Re: Load multiple excel Worksheets with different structure

whiteline — Tue, 10 Jun 2014 20:44:11 GMT

Hi.

Your code could crash because of specific SheetName.

But I think the problem is the missing 'v' before sheetName in the string:

FROM [$(vFile)] (ooxml, embedded labels, table is [$(sheetName)]);

and also

LOAD *, '$(sheetName)' as Sheet

Re: Load multiple excel Worksheets with different structure

Wed, 11 Jun 2014 15:01:29 GMT

Thank you for your input.

The code was indeed crashing because of variable name of table for QVD, it has to be static. I also had few errors there in names of variables. After correcting these I came upon new error which was TABLE_NAME from Excel connection.

Excel seems to create hidden sheets when manipulating with data and that was causing crashes of load because of incorrect format of TABLE_NAME. After fixing this I have working QVD generator which won't crash even when manipulating data in excel (i.e. using autofill, new sheets etc.).

Script searches through all sheets in excel defined by variable vFile,

then for each sheet creates a QVD file with same name as in source .

//Loads all tables information in selected excel spreadsheet

for each vFile in FileList('$(vPathXLS)Data.xlsx')

ODBC CONNECT64 TO [Excel Files;DBQ=$(vFile)];

List:

SQLtables;

DISCONNECT;

//Excel sometimes creates hidden sheets when manipulating with data and using Auto Filter,

//this temp table filters these and stops possible crashes of load

SheetList:

Load TABLE_NAME

resident List

where not WildMatch (TABLE_NAME, '*_FilterDatabase');

Drop table List;

LET vSheetCount = NoOfRows('SheetList'); //Counts all sheets

// Loop for all sheets creates QVDs

FOR i = 0 to $(vSheetCount) -1

LET vSheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'SheetList'), chr(39)), chr(36)); // clears Sheet names from '' and $ signs

TableData:

LOAD *,

'$(vSheetName)' as SourceSheet // New column with source sheet is loaded with extract, not mandatory

FROM $(vFile) (ooxml, embedded labels, table is [$(vSheetName)]);

LET vSheetName = upper ('$(vSheetName)'); // Sets source sheet name in upper case for output QVD

STORE TableData into $(vPathQVDStore)\TableData_$(vSheetName).qvd;

DROP TABLE TableData;

NEXT i

DROP TABLE SheetList;