Excel file information

[qw_johan]

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.

[prieper · Accepted solution]

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

[qw_johan]

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.

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


 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

[Miguel_Angel_Baeyens]

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.

[qw_johan]

Thank you Miguel, Karl and Peter for your help!

[#7]

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