Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am using the FileName() as filename function to obtain the filename of the latest excel file imported to my data model but have realised that when the data file from, excel is empty with only the headers the filename is not showing.
Does anyone know how to show the filename from an excel file when the data is blank?
Thank you in advance,
Daniel
This is how i got around the issue of blank files and finding the filename of them...i hope some people can find this useful:
It returns the name size and time of the file creation.
SUB DoDir (Root)
FOR Each Ext in 'xlsx'
FOR Each File in filelist (Root&'\Replace this with your filename*.' &Ext)
LOAD
'$(File)' as Name,
FileSize( '$(File)' ) as Size,
FileTime( '$(File)' ) as FileTime
AutoGenerate 1;
NEXT File
NEXT Ext
FOR Each Dir in dirlist (Root&'\*' )
CALL DoDir (Dir)
NEXT Dir
END SUB
CALL DoDir ('Add your directory folder path here')
Hi,
Did you try using FileSize?
If(FileSize('$(vPath)\'&VolRpt)>0,-1,0) As FileExist
asuming you are using something like this:
LOAD
FieldA,
FieldB,
Filename() as filename
FROM *.xlsx (...);
Another approach is to loop through all files in a directory using for each ..in filelist ()
With this approach you will be able to fetch the filename from the _file variable.
Something like this:
FOR each _file in filelist ('$(vRootFolder)'&'\*.xlsx')
Load
FieldA,
FieldB,
subfield('$(_file )', '\', -1) as filename
FROM [$(_file )] (...);
next _file
HI There,
Thank you do much for this, i will give it as try today and let you know.
Regards and stay safe.
Daniel
HI,
I have managed to get round to this finally and am having some trouble implementing your solution.
My daily load is like this:
SalesStage1:
LOAD
filenames
from
*****
where ****
Incremental:
Noconcatenate
Load
*,
APPLY MAP Fields
Resident SalesStage1;
Drop table SalesStage1;
Store Incremental into ****
drop table Incremental
I have tried to add your code to the incremental load as per below:
FOR each _file in filelist ('$(vRootFolder)'&'\*.xlsx')
Incremental:
Noconcatenate
LOAD
*,
subfield('$(_file )', '\', -1) as [TEST FIELD],
APPLY MAP Fields
Resident SalesStage1;
Drop table SalesStage1;
STORE
Incremental into E:\Qlikview\Test\Group Sales Report\Table Data\QVDs\SalesLUKinc.QVD(qvd);
drop table Incremental;
next _file
however after the load there is no [TEST FIELD] available for selection in the doc to show the filename.
Any idea where i am going wrong?
thank you again for your help with this.
Daniel
Hi,
I also tried to specify the file location in the For each file statement and i now see the dimension for selection but it has no return data. THe file i imported was blank with headers only for testing
Thanks again
Daniel
I try to intrepret your code and it look like you are dropping all tables and therefore do not have any fields left in your applikcation. Is this correct or am I missunderstanding your script and/or problem?
HI,
On the next page of the script i load the stored qvd
SalesLUKinc.QVD(qvd
and concatenate with a history.qvd
Sorry for not explaining that
Thanks
Daniel
HI,
I have not tried this but i will.
Thanks
Daniel
This is how i got around the issue of blank files and finding the filename of them...i hope some people can find this useful:
It returns the name size and time of the file creation.
SUB DoDir (Root)
FOR Each Ext in 'xlsx'
FOR Each File in filelist (Root&'\Replace this with your filename*.' &Ext)
LOAD
'$(File)' as Name,
FileSize( '$(File)' ) as Size,
FileTime( '$(File)' ) as FileTime
AutoGenerate 1;
NEXT File
NEXT Ext
FOR Each Dir in dirlist (Root&'\*' )
CALL DoDir (Dir)
NEXT Dir
END SUB
CALL DoDir ('Add your directory folder path here')