Load multiple excel Worksheets with different structure

[#1]

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

[whiteline]

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

[#3 · Accepted solution]

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;