Load File Name as Field in Table from XLSX

[#1]

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.

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.

[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]

Add something like thjis to your LOAD statement:

LOAD ...

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

     :

FROM ...;

Best,

Peter

[#4]

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

[Peter_Cammaert]

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

[#6]

Thank you for both the correct answers!

[Peter_Cammaert]

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

Best,

Peter

[Peter_Cammaert · Accepted solution]

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

[#9]

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