topic Associationg a date to an Excel Import in QlikView

Associationg a date to an Excel Import

Tue, 28 Aug 2012 13:47:00 GMT

Hey,

I have been looking for an answer to this question for quite a while now and couldn't find a proper answer.

Already apologizing for any "stupid" questions, I am pretty new to QlikView...

My problem:

I want to import Excel-Sheets on a monthly basis into QV. The sheets do not contain any information about the date but in the filename.

In Qlikview I would like to associate each sheet with the corresponding month (for sorting reasons).

Maybe I could edit the Excel file in some way, that makes this possible but I would rather like to avoid doing so.

I would be really glad, if someone could help me out.

Thank you very much, Thees.

whiteline — Tue, 28 Aug 2012 14:22:36 GMT

If you have some files with the same structure you can use this (just modify your existing load):

for each File in filelist ('*.xlsx')

LOAD

'$(File)' as FileName,

SomeField1,

SomeField2,

...

FROM

SomeExcelFile.xlsx

[$(File)]

(ooxml, embedded labels, table is Sheet1);

Next File;

Now you can also extract your date from the filename string '$(File)' with a string functions.

Tue, 28 Aug 2012 14:47:33 GMT

Thank you for the reply this is exactly what i was looking for!

One question for my understanding:

Does '$(File)' mean the whole path or just the filename?

whiteline — Tue, 28 Aug 2012 16:27:03 GMT

The whole path and filename with extension.

$(File) - substitutes there the value of the variable File. As it's string and can contain spaces use ''.

You can watch for variables in Debug mode (Button on top of the Stript editor).

Also you can run your script step by step.

You can also specify a directory in filelist ('SomeDir\*.xlsx').

You can use relative paths if you've used Directory; statement somwhere above (see help).