12 Replies Latest reply: Mar 8, 2017 4:58 AM by Alan Farrell RSS

    Load multiple excel files from Multiple excel sheetsinto QV

      Hi everybody,

       

      can anybody help me out in completing this challenge

      .

      well the challenge is

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

      and the source files which are all xlsx  files

       

      Things to remembe are before understandinng the result i need:

      -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 and are attched in excel formats fr quick reference

      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 *

      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