Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load File Name as Field in Table from XLSX

Hello-

I am trying to load multiple .xlsx files from my computer, and use the file names as a field inside of Qlik.

I need help writing the script.

deleteme1.png

Claim '....GD-01' has an open and closed (CL & OP) claim status. The reasoning in loading the file name as a field would be for users to select what file date they want for history purposes.

1 Solution

Accepted Solutions
Peter_Cammaert
Partner
Partner

Thanks.

Did you get the difference between the two solutions? Basically FileBaseName() returns the filename without path and without extension. FileName() returns the filename without pzth but including the extension. Which is why I took the part of the filename up to the first dot. In your case, the two solutions return the same value.

You can also use the SubField() funciton to lift different parts from a filename, like a year or a month. For example, if your filename is formatted like 1234.2016.Jun;xls, then you can load the fifferent parts like this:

LOAD ...

     SubField(FileName(), '.', 1) AS FileID,

    SubField(FileName(), '.', 2) AS Year,

    SubField(FileName(), '.', 3) AS Month,

     :

FROM ...;

Best,

Peter

View solution in original post

14 Replies
sunny_talwar

May be this:

Directory C:\Users\wxgoud\Desktop\QlikTest\Legal\External Data\;

For each ExcelFile in filelist ('*.xls')

LEGAL_CLAIMS:

Load *,

  FileBaseName() as FileName

From $(ExcelFile) (ooxml, embedded labels, table is Sheet1);

Next ExcelFile;

Peter_Cammaert
Partner
Partner

Add something like thjis to your LOAD statement:

LOAD ...

     Left(FileName(), Index(FileName(), '.')-1) AS Number,

     :

FROM ...;

Best,

Peter

Not applicable
Author

I tested both your script and Sunnys--what is the difference?

Peter_Cammaert
Partner
Partner

The difference with Sunny's example is:

file = 012345.Claim.002.xls

returns 012345.Claim.002 (Sunny)

returns 012345 (Peter)

May may want to further experiment with parts of the filename.

Best,

Peter

Not applicable
Author

Thank you for both the correct answers!

Peter_Cammaert
Partner
Partner

Helpfuls can be assigned using the entry with the same name in the actions menu underneath each post.

Best,

Peter

Peter_Cammaert
Partner
Partner

Thanks.

Did you get the difference between the two solutions? Basically FileBaseName() returns the filename without path and without extension. FileName() returns the filename without pzth but including the extension. Which is why I took the part of the filename up to the first dot. In your case, the two solutions return the same value.

You can also use the SubField() funciton to lift different parts from a filename, like a year or a month. For example, if your filename is formatted like 1234.2016.Jun;xls, then you can load the fifferent parts like this:

LOAD ...

     SubField(FileName(), '.', 1) AS FileID,

    SubField(FileName(), '.', 2) AS Year,

    SubField(FileName(), '.', 3) AS Month,

     :

FROM ...;

Best,

Peter

Not applicable
Author

Peter-- Yes I understand your answer. Thank you for the detailed explanation. I have applied that solution to my application without trouble!

sunny_talwar

Peter -

Definitely a good explanation here, but I don't understand why can't we use FileBaseName() to do the same? I find it relatively easier since it already truncates the file type.

LOAD ...

    SubField(FileBaseName(), '.', 1) AS FileID,

    SubField(FileBaseName(), '.', 2) AS Year,

    SubField(FileBaseName(), '.', 3) AS Month,

    :

FROM ...;

What if the Filename is like this -> 012345_2016_Jan.xlsx

Wouldn't it be better to use FileBaseName?

LOAD ...

    SubField(FileBaseName(), '_', 1) AS FileID,

    SubField(FileBaseName(), '_', 2) AS Year,

    SubField(FileBaseName(), '_', 3) AS Month,

    :

FROM ...;

Unless there is performance benefits to using FileName() over FileBaseName, I would suggest William‌ to use FileBaseName() instead of FileName()