Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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

Re: Load File Name as Field in Table from XLSX

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

14 Replies
MVP
MVP

Re: Load File Name as Field in Table from XLSX

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;

Re: Load File Name as Field in Table from XLSX

Add something like thjis to your LOAD statement:

LOAD ...

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

     :

FROM ...;

Best,

Peter

Not applicable

Re: Load File Name as Field in Table from XLSX

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

Re: Load File Name as Field in Table from XLSX

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

Re: Load File Name as Field in Table from XLSX

Thank you for both the correct answers!

Re: Load File Name as Field in Table from XLSX

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

Best,

Peter

Re: Load File Name as Field in Table from XLSX

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

Re: Load File Name as Field in Table from XLSX

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

MVP
MVP

Re: Load File Name as Field in Table from XLSX

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