2 Replies Latest reply: Jun 11, 2014 11:01 AM by James Larsson RSS

    Load multiple excel Worksheets with different structure

      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 _

          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

              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;