6 Replies Latest reply: Feb 13, 2011 6:56 AM by himanset RSS

    Excel file information

    Johan Adolfsson


      I am trying to read file information from an Excel file. But I get nothing, no information.
      It works fine with textfiles. Do I need to change something to read Excel file information?
      Information is then displayed in a text object.


      Let vFile= 'MyExcelFile.xls';
      '$(vFile)' as vFile_Name,
      FileName('$(vFile)') as FileName,
      FileSize('$(vFile)') as FileSize,
      FileTime( '$(vFile)') as FileTime
      From '$(vFile)';


      Thanks for your help.

        • Excel file information
          Peter Rieper

          You need to specify the sheetname.

          Try to use the assistant and see, how the script looks:


          LOAD .... FROM [MyPath\MyExcelFile.xls] (biff, no labels, table is MySheet$);


            • Excel file information
              Johan Adolfsson


              Well, I don't want to load the file...i just want to load information about the file.
              Like, filesize, filetime, filename...etc. My code work fine for a text file but not an Excel file.
              I don't know why.

                • Excel file information
                  Karl Pover

                  If you use the From you sometimes have to tell QV explicitly what the file format is. In this case it is necessary to add the work (biff) at the end of the file name. For example,

                  MyExcelFile.xls (biff)

                  If you just want file information you can also use the filelist() function as shown below from the QlikView Explorer v8.01 application:


                  Sub DoDir(Root)
                  For each Ext in '$(v_Extension)'

                  For each File in Filelist (Root&'\*.'&Ext)

                  let FileExtension = lower(mid(File,Index(File,'.',-1)+1));

                  Right(Name,Len(Name)-Index(Name,'\',-1)) AS File,
                  Left(Name,Index(Name,'\',-1)) AS FilePath,
                  subfield(mid(Name,Index(Name,'\',-2)+1),'\',1) AS FileFolder,
                  FileSize(Name) as Size,
                  FileTime(Name) as FileTime,
                  date(subfield(FileTime(Name),' ',1),'M/D/YY') as FileDate,
                  if(Index(Name,'.', -1)>=NumSum(Len(Name),-6) and Index(Name,'.', -1)>0, lower(right(Name, Len(Name)- Index(Name,'.', -1))), '') as Extension,
                  date(monthstart(FileTime(Name)),'MMM-YY') as FileMonthYear,
                  year(FileTime(Name)) as FileYear;
                  Load * Inline "
                  // ************* Load QVD Structure if requested:
                  if FileExtension = 'qvd' and v_AnalyzeQVD = 'Yes' then

                  call LoadQVDStructure(File)

                  end if

                  Next File

                  Next Ext

                  For each Dir in Dirlist(Root&'\*')

                  Call DoDir(Dir)
                  Next Dir

                  End sub

                  • Excel file information
                    Miguel Angel Baeyens de Arce

                    Easier than that, just a few checks on your code will do:


                    Let vFile= 'ExcelFile.xls'; FileInformation: LOAD '$(vFile)' as vFile_Name, FileName('$(vFile)') as FileName, // not needed since you already have the file name in the variable FileSize('$(vFile)') as FileSize, FileTime('$(vFile)') as FileTimeAUTOGENERATE 1; // another way to create a table

                    Hope that helps.