Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I would like to load latest text file (Flat Files) from a folder to QlikView document.
I have files inside folder are
ABC_20151118.txt
ABC_20151119.txt
XYZ_20151118.txt
XYZ_20151120.txt
My requirement is , I want to load only ABC_20151119.txt and XYZ_20151120.txt files. how to achieve it?
Thanks
KR
// read filename
tmp:
LOAD distinct FileName() as fn
FROM abc_*.txt
(txt, codepage is 1252, no labels, delimiter is '\t', msq);
LOAD distinct Filename() as fn
FROM
xyz_*.txt
(txt, codepage is 1252, no labels, delimiter is '\t', msq);
// calc first (max) filename by abc / xyz / ... using the first 3 chars
table:
load left(fn, 3), MaxString(fn) as fn
Resident tmp
Group by left(fn, 3);
DROP Table tmp;
// read files
for i=0 to NoOfRows('table')-1
let file=Peek('fn', $(i), 'table');
trace $(file);
load @1 FROM '$(file)' //maybe you have to change for your files, I just have 1 field in the files
(txt, codepage is 1252, no labels, delimiter is '\t', msq);
NEXT;
DROP Table table;
Hi Prasanna,
It looks like you want to load from files after a certain date which form the last eight characters of the filename.
If you set a variable in the UI or in the script that has the date you want to load from then this script might work for you.
This script assumes that the filename will not use numbers except for the date portion. If you change this you will need to use a different method to extract this part of the filename.
Also this script loads from a folder called txt which is a subfolder to the QvWorkPath folder, again change as required.
Let vDateFrom = 20151119;
Filenames:
LOAD Distinct
FileName() as FName,
KeepChar(FileName(),'0,1,2,3,4,5,6,7,8,9') as FDate
FROM
txt\*.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
LoadFiles:
LOAD
FName
Resident Filenames Where FDate >= $(vDateFrom);
DROP Table Filenames;
for i=0 to NoOfRows('LoadFiles')-1
let file=Peek('FName', $(i), 'LoadFiles');
Data:
LOAD *
FROM
txt\$(file)
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
NEXT;
DROP Table LoadFiles;
Hi Andrew,
I mentioned that date stamp for example only. there is no certain date. My files date stamp will keep on change. depends on the date stamp I want to load latest distnct files into QlikView
Thanks
KR
Hi ,
Try like this..
set Path=\data;
/* assume above path as base path where my year wise folder contains files */
Sub GetFolder(Path)
for each Mainfolder in DirList(Path)
for Each folder in DirList(Mainfolder&'\*')
Call GetFile(folder);
Latest :
LOAD [File Path],Max([File Time]) as [Max Time] Resident Files Group By [File Path];
Left Join(Latest)
LOAD [File Time] as [Max Time],[File Name] Resident Files;
DROP Table Files;
let vName=Peek('File Name',0,'Latest');
DROP Table Latest;
Dataset :
LOAD * ,'$(vName)' as [Src File]From '$(vName)';
let vName='';
Next
Next
End Sub
Sub GetFile(folder)
For Each ext in 'txt'
For Each file in Filelist(folder&'\*.'&ext)
Files:
LOAD '$(folder)' As [File Path], '$(file)' as [File Name] ,FileTime('$(file)') as [File Time] AutoGenerate(1) ;
next
next
End Sub
CALL GetFolder('$(Path)');
Hope this Helps ,
Regards,
HirisH
Hi Prasanna,
Try the attached qvw. If you're not licensed to open it let me know and I'll send you the script and explain what's need in terms of sheet objects. The script assumes that your txt files are held in a subfolder of the QvWorkPath called "txt".
Hope it helps.
Hi Andrew,
Thank you for your reply. unfortunately attachment is missing. I have licenced version of Qlikview I can open that attachment, Could you please re attach it.
Thanks
KR
Sorry about that Prasanna. I'm at work right now and the qvw is on my home pc hard drive. When I get home I'll try again.
Regards
This thread may help.
Hi Prasanna,
Please find attached the file i tried to send yesterday.
Regards