Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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
Highlighted

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;

Highlighted

Add something like thjis to your LOAD statement:

LOAD ...

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

     :

FROM ...;

Best,

Peter

Highlighted
Not applicable

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

Highlighted

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

Highlighted
Not applicable

Thank you for both the correct answers!

Highlighted

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

Best,

Peter

Highlighted

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

Highlighted
Not applicable

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

Highlighted

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