Finding filename when no data in the file

[davyqliks]

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

[Saravanan_Desingh]

Hi,

Did you try using FileSize?

If(FileSize('$(vPath)\'&VolRpt)>0,-1,0) As FileExist

[Vegar]

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

[davyqliks]

HI There,

Thank you do much for this, i will give it as try today and let you know.

Regards and stay safe.

Daniel

[davyqliks]

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

[davyqliks]

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

[Vegar]

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?

[davyqliks]

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

[davyqliks]

HI,

I have not tried this but i will.

Thanks

Daniel

[davyqliks · Accepted solution]

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