Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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.
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
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.
Thank you Miguel, Karl and Peter for your help!
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