To Know Last Refreshed Date of Excel,CSV and Qvd's

[pra_kale]

Hi,

Before Reloading the already developed application I always required to check the last modified date of all the Qvd's, Excel files and csv used  in the application. The QVD's and excel files are not on the same path so I have to to go to each and every path and need to check the modified date. It is a painful task to check each and every file.

So is there any script which I can include in my already build application so by doing Debug with Limited load i can get in One Table All the QVD's,CSV, excel files modified date and their path..similar like in below given format.

No	Path	Modified Date
1	E:\Qlikview\Sample.qvd	09/01/2016 11.45
2	E:\Qlikview\Test.csv	12/12/2015 18.45
3	E:\Qlikview\New folder\Name.xlsx	02/01/2016 22.15

Thanks in Advance.

[marcus_sommer]

Something like this will work:

Files:

Load * inline [

Path

C:\Users\Marcus Sommer\Desktop\abc.xlsx

C:\Users\Marcus Sommer\Desktop\xyz.xlsx

];

for i = 1 to noofrows('Files')

    FilesAndFileTime:

    Load peek('Path', $(i) - 1, 'Files') as Path, filetime(peek('Path', $(i) - 1, 'Files')) as FileTime Autogenerate 1;

next

- Marcus

[Kushal_Chawda · Accepted solution]

For QVDS

Tab:

First 1 LOAD filedir() as Path,

          filename() as FileName,

          filetime() as Modified_Date,

          'QVD' as FileExtension

From E:\Qlikview\*.qvd(qvd);

concatenate (Tab)

First 1 LOAD filedir() as Path,

          filename() as FileName,

          filetime() as Modified_Date,

          'CSV' as FileExtension

From E:\Qlikview\*.CSV(txt);

concatenate (Tab)

First 1 LOAD filedir() as Path,

          filename() as FileName,

          filetime() as Modified_Date,

          'xlsx' as FileExtension

From E:\Qlikview\*.xlsx;

[pra_kale]

Hi Kush,

Thanks...

The above script is working but it is providing me the details of all the files,QVD's store in the mentioned path.

But, I want the script should provide me the details only of the QVD's or csv's or excel's used in the script without mentioning any path..whether it is possible.

Means it might be possible few QVD's are from E:\ drive few from D:\..etc.

[HirisH_V7]

Hi,

May be like this,

for each File in filelist ('Your Path\*.*')
Folder:
Load '$(File)' as Name,
FileTime( '$(File)' ) as FileTime
autogenerate 1;
next File
LatestFile:
first 1
Load
Name,
FileTime,
1 as dummy
Resident Folder
Order By FileTime DESC;
drop table Folder;


NoConcatenate



[Data For Excel]:
LOAD *
FROM
$(File)
(biff, embedded labels, table is Sheet1$);


[Data For QVD]:
LOAD *
FROM
$(File)
(qvd);

There you can see in above script for Both Excel and QVD loading.

PFA,

Hope this Helps,

Hirish

[HirisH_V7]

Hi,

You can Change the Path for above post,

Another Procedure and change the path as this,

From D:\Qlikview\*.qvd(qvd);

Hope this helps,

Hirish

[pra_kale]

Hi Hirish,

Thanks for your help!!

Your script is also running fine and providing me the latest updated file from that folder..

But, currently my requirement is little bit different on which Kush has provided the solution at one level, where I am getting the details of all the files available in the mentioned folders with their Last Refreshed Date, Type and Place, Below given is the out-put of Kush script..which is exactly I want.    

Path	FileName	Modified_Date	FileExtension
E:\Qlikview	Agency.csv	02-01-2016 18:27:59	CSV
E:\Qlikview	ActualTransactions.qvd	09-08-2015 14:55:54	QVD
E:\Qlikview	Avg Term.xlsx	10-10-2015 11:01:52	xlsx

But one more level ahead, can I get the details only of the files,QVD's used in the script. For e.g. If in a script I have used only 3 Qvd's and 2 Xlsx files, then I am interested in the details of these files only and not of all the files. Because in a path may get 50 to 100 files.

So just want to check whether this is possible..

Thanks in Advance.

[HirisH_V7]

Hi ,

Check this,

For picking latest QVD-Data:

for each File in filelist ('Your Path\*.QVD')
Folder:
Load '$(File)' as Name,
FileTime( '$(File)' ) as FileTime
autogenerate 1;
next File
LatestFile:
first 1
Load
Name,
FileTime,
1 as dummy
Resident Folder
Order By FileTime DESC;
drop table Folder;


NoConcatenate


QVD:
LOAD *
FROM
$(File)
(qvd);

For picking latest Excel-Data:

for each File in filelist ('Your Path\*.xls')
Folder:
Load '$(File)' as Name,
FileTime( '$(File)' ) as FileTime
autogenerate 1;
next File
LatestFile:
first 1
Load
Name,
FileTime,
1 as dummy
Resident Folder
Order By FileTime DESC;
drop table Folder;


NoConcatenate


Excel:
LOAD *
FROM
$(File)
(biff, embedded labels,table is Sheet1$);

For picking latest CSV-Data:

for each File in filelist ('Your Path\*.csv')
Folder:
Load '$(File)' as Name,
FileTime( '$(File)' ) as FileTime
autogenerate 1;
next File
LatestFile:
first 1
Load
Name,
FileTime,
1 as dummy
Resident Folder
Order By FileTime DESC;
drop table Folded labels, delimiter is ',', msq);


NoConcatenate


Excel:
LOAD *
FROM
$(File)
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

You can  use all of them in different script pages .

Hope this Helps,

Thanks,

Hirish

[pra_kale]

Thanks Kush, as Always, for Your Help...!!!

[pra_kale]

Thanks...Great..Hirish...!!!