how to load latest different txt files?

[psankepalli]

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

[maxgro]

// 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;

[effinty2112]

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;

[psankepalli]

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

[HirisH_V7]

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

[effinty2112]

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.

[psankepalli]

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

[effinty2112]

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

[#9]

This thread may help.

Load file based on timestamp

[effinty2112]

Hi Prasanna,

Please find attached the file i tried to send yesterday.

Regards