topic Re: Load only last CSV file of floder in QlikView

Load only last CSV file of floder

jmialoundama — Wed, 21 Nov 2018 11:01:00 GMT

Hello,

I have multiple CSV file in my folder. The name of CSV file is like that :

20181125 -product-extract

20180820-product-extract

20180910-product-extract

I have a script and i want to load only last CSV file, but i would like to know how can i do this ?

Thanks in advance 🙂

Re: Load only last CSV file of floder

jcarloscandela — Wed, 21 Nov 2018 11:42:50 GMT

Hello,

This old answer can help you

https://community.qlik.com/t5/QlikView-Scripting/Only-load-latest-file/m-p/671638/highlight/true#M42955

Re: Load only last CSV file of floder

Thiago_Justen_ — Wed, 21 Nov 2018 11:45:27 GMT

Here it is:

For each vFile in FileList('lib://YOUR_PATH/*.csv')

LET vFileName = SubField('$(vFile)','/',-1);

LET vDate = Date(Date#(Left('$(vFileName)',8),'YYYYMMDD'),'YYYYMMDD');

tmpFiles:

LOAD '$(vFile)' as File, '$(vFileName)' as FileName, '$(vDate)' as Date AutoGenerate 1;

Files:

LOAD Max(Date) as Date RESIDENT tmpFiles;

LEFT JOIN (Files)

LOAD File, Date RESIDENT tmpFiles;

DROP TABLE tmpFiles;

For i = 0 to NoOfRows('Files') -1

LET vSourceFile = peek('File',$(i),'Files');

data:

LOAD * FROM [$(vSourceFile)] (csv);

Re: Load only last CSV file of floder

jmialoundama — Wed, 21 Nov 2018 11:48:29 GMT

Hello Thiago,

Thanks you for your aswer.

I try this script ,but it's not works and i didn't see where error can provide :

I have multiple csv file like this :

20181120-product-extract.csv

20180805-product-extract.csv

20180901-product-extract.csv

I want to load only last csv file.

I try to applicate this script to load lastest CSV file , but it's not working and i can't to see my error.

I update the script like this :

LET vFilePath = 'lib://AttachedFiles/*-product-extract.csv';
FOR EACH file in FileList('$(vFilePath)'); // Loops each excel file in the given Folder
LET vFileDate = Date((Left(file,8)); // Gets the year portion from the filename
LET vMaxDate = Rangemax(vFileDate, vMaxDate); // Gets the max year for every iteration
NEXT

Data:
LOAD sku
FROM [lib://AttachedFiles/$(vMaxDate)-product-extract.csv]
(txt, utf8, embedded labels, delimiter is '|', msq);

Error : "Cannot open file 'lib://AttachedFiles/-product-extract.csv'

Re: Load only last CSV file of floder

jmialoundama — Wed, 21 Nov 2018 11:56:55 GMT

Thiago,

I try your script and i have same error : Cannot open file: 'lib://AttachedFiles/lib://AttachedFiles/20181118-product-extract.csv-product-extract.csv'

Re: Load only last CSV file of floder

Thiago_Justen_ — Wed, 21 Nov 2018 12:06:35 GMT

Change this piece of code:

From:

LET vFilePath = 'lib://AttachedFiles/*-product-extract.csv';

To:

LET vFilePath = 'lib://AttachedFiles/*.csv';

Re: Load only last CSV file of floder

jmialoundama — Wed, 21 Nov 2018 12:58:57 GMT

Hello,

I solded my problem. I update script like this and it's works :

LET vFilePath ='lib://AttachedFiles';
FOR EACH file in FileList('$(vFilePath)/*.csv'); // Loops each excel file in the given Folder
LET vFileDate = Date(Left(right(file,28),8)); // Gets the year portion from the filename
LET vMaxDate = Rangemax(vFileDate, vMaxDate); // Gets the max year for every iteration
NEXT


Data:
LOAD *
FROM [$(vFilePath)/$(vMaxDate)-product-extract.csv]
(txt, utf8, embedded labels, delimiter is '|', msq);

With this script i get the latest csv file.

Thanks your for your help 🙂

Re: Load only last CSV file of floder

Thiago_Justen_ — Wed, 21 Nov 2018 13:05:14 GMT

Sound good. If my responses were useful, please give like to those.

Cheers