16 Replies Latest reply: Oct 29, 2013 3:49 PM by Steve Dark RSS

    Load data from multiple worksheets in multiple Excel workbooks

      Have 50 workbooks - one for each customer - stored in "CustomerData" folder.  Workbooks can have 1 to 50 worksheets (one for each day with customer data.)  In each workbook, worksheets are named "1", "2", "3", etc. -- e.g. the first worksheet in workbook "A" is named "1" as is the first worksheet in workbook "B" -- worksheet names are not unique across workbooks.  For all worksheets, worksheet columns are identical - some have more rows (more records.)

      With one load statement, I can load all of the worksheets named "1" from all the workbooks in "CustomerData."  What I want to do is load all the data from each worksheet in each workbook with one load statement if possible.  If needed, each worksheet contains a value for "CustomerName" and a value for "Date" which when combined are unique to that worksheet.

      Any ideas on how to write the load statement or is this not possible?

      Thanks

      Bill

        • Load data from multiple worksheets in multiple Excel workbooks
          Steve Dark

          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.

           

          Hope that helps,

          Steve

           

          http://www.quickintelligence.co.uk/

           


          • Re: Load data from multiple worksheets in multiple Excel workbooks

            Hi everybody,

             

            I do have a similar kind of challenge ,kindly help me out ..

            well the challenge is

            I have a folder in which i have the source files along with the qv file

            and the source files are all xlsx  files

             

            Things to remembe are:

            -Source files are not limited in number and may have different names but are all xlsx files

            - source files may have no of worksheets and are not limited in number and may vary from book to book

            -Name of every sheet in the every excel file is different

            -In every source excel file only the 1st sheet will have the header and all other following sheets in that file will have no headers

            - The no of rows filled are not confined to anylimit and may vary from sheet to sheet in the file

            -the headers are similar in all the source files provided in the 1st sheet of every source file

             

            Result i need :

             

            based on the above conditions ,If i reload the qv by placing all the source files in the folder ,i need to the qv to be reloaded completely with the data in the source files

             

            for refernce the source files are as attached in the image sample1.jpgsample2.jpg

             

            The code im trying in qlikview is as per the following

            ***********************************************************************************

            for each vFileName in FileList('D:\Users\aju\Desktop\anand\*.xlsx');

            for Each vSheet in $(vFileName)

            Temp_Sheets:

            LOAD

            S_no,

                 Name,

                 [Roll no],

                 DOJ,

                 marks

            FROM $(vFileName)

            (biff, embedded labels, header is 1 lines, table is $(vSheet));

             

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

             

            DROP TABLE Temp_Sheets;

             

            for vSheetNo = 1 to $(vMaxSheet)

                 Data:

                 LOAD*

                     

                 FROM $(vFileName)

                 (biff, embedded labels, header is 1 lines, table is $(vSheet));

            next

            ****************************************************************************************************************

            I request you help and resolve this challenge

             

            Thanks ahead !!!

             

            Kind Regards

            Anand