topic Re: Load only new data from multiple csv in QlikView

Load only new data from multiple csv

Thu, 27 Feb 2014 11:58:32 GMT

Hello,

I have a QV document that loads data from multiple csv documents ( From *.csv).

New files are being saved in the data folder quite frequently and the load is getting excessively long. The problem I can see is that each of the CSVs is being reloaded from scratch.

Is there a way to specify that only newly added csv files are to be loaded, with the existing data retained?

Regards
Marek

Re: Load only new data from multiple csv

MK_QSL — Thu, 27 Feb 2014 12:03:48 GMT

Use While Exists during load with some primary key...

Possibly date...

Re: Load only new data from multiple csv

rustyfishbones — Thu, 27 Feb 2014 12:08:39 GMT

Hi Marek,

Try storing the old .csv files into a qvd file

It will reload much faster

Re: Load only new data from multiple csv

Thu, 27 Feb 2014 12:15:59 GMT

Marek,

As Alan said, see the QVD files.

You may also use the prefix BUFFER :

BUFFER LOAD ....

This will create the QVD from the existing data. Next time, the QVD will be read and only new lines will be read from csv.

The problem with this technique is that it does not detect the changed lines : you will always get old lines from QVD and new lines from csv.

It is why you certainly have to manage by yourself the QVD procedure.

Fabrice

Re: Load only new data from multiple csv

stevedark — Thu, 27 Feb 2014 12:18:01 GMT

Hi Marek,

I would create a QVD file for each CSV. You can do this by looping for each CSV file you have, and just creating QVD's where this doesn't already exist:

for each vFile in FileList('*.csv')

let vQVD = replace(vFile, '.csv', '.qvd');

if alt(FileSize('$(vQVD)'), 0) = 0 then

NextFile:

LOAD

FROM $(vFile)

([... CSV format in here ...])

;

STORE NextFile INTO $(vQVD) (qvd);

DROP TABLE NextFile;

end if

next

You can then load from *.qvd and this will go much much quicker than loading from the CSV files.

I've just typed the code straight into Community, so there may be slight syntax errors, by the idea behind it should be sound.

Hope that helps,

Steve

Re: Load only new data from multiple csv

stevedark — Thu, 27 Feb 2014 12:19:05 GMT

You may also want to take a look at this post on incremental loads:

http://www.quickintelligence.co.uk/qlikview-incremental-load/

Re: Load only new data from multiple csv

marcus_malinow — Thu, 27 Feb 2014 12:26:37 GMT

Another solution might be to use the filename() statement when loading.

LOAD *,

filename() as CSVFileName

from [your CSV].

Store this into your QVD, then when you do a reload, load the distinct values of CSVFileName from your QVD and skip those CSV's. Then concatenate the data from your QVD, and resave it.

Marcus

Re: Load only new data from multiple csv

Thu, 27 Feb 2014 12:31:45 GMT

Steve,

Great solution: easy to understand, to implement and certainly very efficient.

I hope for Marek that the csv (name, number) are not versatile.

Fabrice

Re: Load only new data from multiple csv

Fri, 28 Feb 2014 09:20:16 GMT

Hi,

Thank you all for all the help, Steve's advice was exactly what I was after !

Regards

Marek

Re: Load only new data from multiple csv

stevedark — Fri, 28 Feb 2014 15:04:44 GMT

Hi Marek,

Glad that this has solved your issue.

To respond to Marcus Malinow on his approach, this could be a bit quicker as the final load would be from a single QVD rather than many. What I like about my approach though (and why I have used a similar approach on a few occasions) is that if you know that a CSV has been modified you can simply delete the corresponding QVD and it will be recreated. Indeed you can delete all of the QVD's and the whole lot will be recreated from scratch.

It would be possible to put more logic into the load, eg. check the date on the CSV file and rebuild the corresponding QVD if the CSV had changed since the last load. The code above is a bare bones approach though.

When benchmarking the above solution I was amazed at how quick the FileSize function was - many files could be skipped with very little lag at all.

Steve

Re: Load only new data from multiple csv

stevedark — Fri, 07 Mar 2014 09:12:28 GMT

I've decided to take my thoughts on this thread further with a post on my blog. Here I explore different ways of looking at dealing with changes to source files and further optimisation of the load.

You can read the post here: http://www.quickintelligence.co.uk/convert-drop-folder-files-qvd/

- Steve

Re: Load only new data from multiple csv

aj0031724 — Mon, 16 Nov 2015 13:41:39 GMT

Dear Steve,

I was woring with this logic fo rincreental new file loadind only.

However if I keep on adding new qvd for each csv file then it continuously just fill up the disk space .

Is there any way by which I just ensure that I am loading only the new file without having to keep the qvd file along woth csv file ?

Re: Load only new data from multiple csv

stevedark — Mon, 16 Nov 2015 19:49:53 GMT

So, is it the case that you have a number of CSVs and you only want to load the latest?

If so, set up a loop like this:

let vFileName = '';

let vLatest = makedate(1984,1,1);

for each vFile in FileList('$(filepath)*.csv')

if FileTime('$(vFile)') > vLatest then

let vLatest = FileTime('$(vFile)');

let vFileName = vFile;

end if

next

LOAD

FROM $(vFileName)

[... file definition here ...]

;

Hope that helps.

Steve

Re: Load only new data from multiple csv

aj0031724 — Wed, 18 Nov 2015 09:18:17 GMT

Dear Steve,

Thanks.

I tried this logic but it seems it is always picking the last file(only one file ) always not more than that .

Can you please help?

Re: Load only new data from multiple csv

stevedark — Wed, 18 Nov 2015 09:27:31 GMT

Hi Avinash,

I thought that was what you were requiring, sorry. To load from all files, without going via QVD simply move the CSV load inside the loop, and lose all the date checking:

for each vFile in FileList('$(filepath)*.csv')

LOAD

FROM $(vFileName)

[... file definition here ...]

;

next

You will find though that the solution in my blog post (above), where you create QVDs for new files and load data from QVD for old files will be much much quicker over time.

If you have the data in QVD you can purge the CSV, and the QVD files should be considerably smaller than the original CSVs (unless the CSVs are very small as QVDs have a slight overhead in the header). That purge can be done with a CMD statement in the load, but I would recommend a manual clean up process rather than making your QV app need system rights.

Hope that helps,

Steve

Re: Load only new data from multiple csv

aj0031724 — Wed, 18 Nov 2015 09:35:18 GMT

Dear Steve,

Apologies If I did not mak emyself clear enough.

What I want to achieve is :

a)LOAD ONLY NEW FILES FROM THE SAME FOLDER .do not load files which are already loaded.

if today there are 10 files in the folder which are already loaded and 5 files are loaded now then I just nned to read from these 5 new files .

Re: Load only new data from multiple csv

stevedark — Fri, 20 Nov 2015 17:43:18 GMT

Sounds like you need a PARTIAL LOAD. I tend to avoid them as things can go a bit wrong if you need to reboot. You should be able to Google for information though.

You will need the PARTIAL syntax, and the code I posted to just get the new stuff. You will need to persist the date of the last loaded file, to bring in all files since that date, rather than all.

Hope that helps.

Steve