topic Re: Load File Name as Field in Table from XLSX in QlikView

Load File Name as Field in Table from XLSX

Wed, 08 Jun 2016 15:47:01 GMT

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.

Re: Load File Name as Field in Table from XLSX

sunny_talwar — Wed, 08 Jun 2016 16:03:00 GMT

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

Peter_Cammaert — Wed, 08 Jun 2016 16:05:02 GMT

Add something like thjis to your LOAD statement:

LOAD ...

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

FROM ...;

Best,

Peter

Re: Load File Name as Field in Table from XLSX

Wed, 08 Jun 2016 16:07:24 GMT

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

Re: Load File Name as Field in Table from XLSX

Peter_Cammaert — Wed, 08 Jun 2016 16:09:19 GMT

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

Re: Load File Name as Field in Table from XLSX

Wed, 08 Jun 2016 16:11:13 GMT

Thank you for both the correct answers!

Re: Load File Name as Field in Table from XLSX

Peter_Cammaert — Wed, 08 Jun 2016 19:13:16 GMT

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

Peter_Cammaert — Wed, 08 Jun 2016 19:24:26 GMT

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

Re: Load File Name as Field in Table from XLSX

Wed, 08 Jun 2016 19:40:19 GMT

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

Re: Load File Name as Field in Table from XLSX

sunny_talwar — Wed, 08 Jun 2016 19:50:04 GMT

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

Re: Load File Name as Field in Table from XLSX

Wed, 08 Jun 2016 20:06:35 GMT

I am adding an excel master calendar to the script. Is that a reason to not use the Year Month you mentioned?

Re: Load File Name as Field in Table from XLSX

Peter_Cammaert — Wed, 08 Jun 2016 20:10:43 GMT

Sunny,

of course you're right, and I never said you couldn't accomplish the same result using FileBaseName(). (sorry about the reassignment, to me the original was perfectly ok).

Call it a nerd habit, but I always keep thinking about performance impact. Even a tiny one, like in this case. Lets compare it to how we would implement those file functions using the subfield() call. Given a full path, Filename would need a single call, while FileBaseName would need at least two. Multiplied by the number of different columns (I don't think the script engine caches the result from static calls between column creation expressions or inside a loop, but I'm not really sure) .And for no reason if you just want your specific part of the filename.

I may be nitpicking here, as the difference doesn't amount to much if you're not reading millions of lines from thousands of files.

Peter

Re: Load File Name as Field in Table from XLSX

Wed, 08 Jun 2016 20:15:14 GMT

I just added the master calendar, and I see syn keys.

Re: Load File Name as Field in Table from XLSX

sunny_talwar — Wed, 08 Jun 2016 20:15:14 GMT

Sorry about the reassignment

No apologizes needed, I find your responses as a way to learn new things.

Call it a nerd habit, but I always keep thinking about performance impact. Even a tiny one, like in this case. Lets compare it to how we would implement those file functions using the subfield() call. Given a full path, Filename would need a single call, while FileBaseName would need at least two. Multiplied by the number of different columns (I don't think the script engine caches the result from static calls between column creation expressions or inside a loop, but I'm not really sure) .And for no reason if you just want your specific part of the filename.

So there are performance benefits here, even though they might be very slight. Is there a resource where I can read about this (and other similar performance stories?)

Re: Load File Name as Field in Table from XLSX

sunny_talwar — Wed, 08 Jun 2016 20:29:23 GMT

I don't know you data well enough to give any insights, but if you don't want to join your table on date and month fields, then don't create them