topic Excel file information in QlikView

Excel file information

qw_johan — Sat, 12 Feb 2011 11:54:11 GMT

Hi!

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';
[Fileinformation]:
LOAD
'$(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

prieper — Sat, 12 Feb 2011 18:16:59 GMT

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$);

HTH
Peter

Excel file information

qw_johan — Sat, 12 Feb 2011 20:34:57 GMT

Hi,

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

pover — Sat, 12 Feb 2011 21:02:46 GMT

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));


 Main:
 Load
 Name,
 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 "
 Name
 $(File)";
 // ************* 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 — Sun, 13 Feb 2011 01:54:25 GMT

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.

Excel file information

qw_johan — Sun, 13 Feb 2011 10:18:46 GMT

Thank you Miguel, Karl and Peter for your help!

Excel file information

Sun, 13 Feb 2011 11:56:58 GMT

Try This

for each File in filelist ('$(QVDFolder)\*.qvd')

Load

'$(File)' as F33,

left(subField('$(File)', '\', subStringCount('$(File)','\')+1), len(subField('$(File)', '\', subStringCount('$(File)','\')+1))-4) as F3,

FileSize( '$(File)' ) as Size,

FileTime( '$(File)' ) as FileTime autogenerate 1;

next File

Hope this helps you

Regards

Himanshu Sethi