Pull a Date from the CSV Creation Date?

[dinicholls]

Hi,

I have a suite of extracts that run on a daily basis, and produce CSV files in the same location.

I'm relatively new to Qlik, and I have started trying to change all our current excel reports to QlikView Apps.

My major problem at the moment is that I have to keep copying the daily csv extracts over from one location to another. Is there any way I can take the date of the file creation and use it as a date field in a Qlik app?

This is an example of one of our extracts:

MI_029_ADT_LDM_DUE_287800

The numbers on the end increase to make the file name unique. There is nothing in the file that states what date the extracts was run, and nothing in the file name. All the extracts are run automatically, and nothing can be changed to include a date within the extract csv name.

Does this make sense?!

Here's hoping!

Di

[tresesco]

Try using filetime(), like:

Load *, Date(filetime()) as Date from <abc.csv> ;

[dinicholls]

Hi,

How would the 'date from <abc.csv>' work? Does it just pull the date from the file creation date?

Sorry if I seem a little thick, I'm on a steep learning curve!

I guess, thinking about things now, I'd also have to include a 'where the Date = today()' to make sure I don't include every single file! Six months worth!

Thanks

Di

[tresesco]

Filetime() returns the timestamp of last modification of the file. You could try like:

Load

          *

From <yourfile> where Date(filetime())=Today();

[dinicholls]

HI,

I figured it would be something like that, but when I tried it, I'm not getting any data through now.

Its looking and finding the files, and its found the last one, but its not picking it up.

I've tried the following combinations:

Date(FileTime())								as [Extract Date]
FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 8 lines)

where Date(Filetime())=Date#('30/10/14','DD/MM/YY');

---------

Date(FileTime())								as [Extract Date]
FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 8 lines)

where Date(Filetime())=Date('30/10/14');

---------

Date(FileTime())								as [Extract Date]
FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 8 lines)

where Date(Filetime())=Today();

Thanks for all your help so far, think I'd be lost without all you lovely people to point me in the right direction!

Di

[ashfaq_haseeb]

Hi,

Check this

It will give you creation time of your file.

Regards

ASHFAQ

[dinicholls]

Hi,

But if I'm using 'Today()', surely the date format wouldn't matter?

Have changed the date round to MM/DD/YYYY, and its still not finding today's file.

I feel like grabbing Qlik, shaking it, and saying 'I can see the file, its right there!!!!'

Arghhhh!!!

Di

[tresesco · Accepted solution]

Ahh, actually timestamp returned by filetime() has fraction that causes the issue, Try, removing the fraction using floor(), like:

where Floor(filetime())=Num(Today());                  // num() might not be required

[dinicholls]

Woohoo!!!!

Thank you so much!

Finally worked!!!