topic Re: Loading data monthly from Excel in QlikView

Loading data monthly from Excel

cscherer — Fri, 15 Jan 2016 11:40:49 GMT

Hello,

I´m loading data monthly from excel files and store them in a .qvd-file. When I´m reloading the script, the data from the first file isn`t reloaded, but the data from the following files. And that is my problem: Why are the data from the second (and following files) reloaded - even if the data hasn`t changed? What do I have to change in the code?

Table:

buffer (incremental) LOAD A,

FROM

(ooxml, embedded labels, table is rawdata);

store Table into Table.qvd;

Concatenate Table:

buffer (incremental) LOAD A,

FROM

(ooxml, embedded labels, table is rawdata);

store Table into Table.qvd;

Concatenate Table:

buffer (incremental) LOAD A,

FROM

(ooxml, embedded labels, table is rawdata);

store Table into Table.qvd;

Thanks in advance,

best regards,

Clemens

Re: Loading data monthly from Excel

Peter_Cammaert — Fri, 15 Jan 2016 14:25:30 GMT

I guess because you're reading with option Incremental from Excel files, not from text files. This is the explanation of option incremental from the QV Desktop help:

The incremental option enables the ability to read only part of an underlying file. Previous size of the file is stored in the XML header in the QVD file. This is particularly useful with log files. All records loaded at a previous occasion are read from the QVD file whereas the following new records are read from the original source and finally an updated QVD-file is created. Note that the incremental option can only be used with load statements and text files and that incremental load cannot be used where old data is changed or deleted!

QlikView has no way to detect what rows have been added to your Excel based on the filesize alone.

You'll need to write your own incremental load using a timestamp field in you source data (or another indicator of time and date, for example filetime that is more recent than the last QVD). Search for Incremental Load in the Community. There must be thousands of examples, at least one should fit your situation.

Peter

Re: Loading data monthly from Excel

Anonymous — Fri, 15 Jan 2016 14:26:25 GMT

Hi,

Try this:

Table:

LOAD A,

FROM

(ooxml, embedded labels, table is rawdata);

store Table into Table.qvd;

DROP TABLE Table;

Table:

LOAD A,

FROM

(ooxml, embedded labels, table is rawdata);

Concatenate (Table)

LOAD * FROM Table.qvd(qvd)

store Table into Table.qvd;

DROP TABLE Table;

Table:

LOAD A,

FROM

(ooxml, embedded labels, table is rawdata);

Concatenate (Table)

LOAD * FROM Table.qvd(qvd)

store Table into Table.qvd;

Re: Loading data monthly from Excel

cscherer — Fri, 15 Jan 2016 14:37:54 GMT

Thank you. I will search for that!

Re: Loading data monthly from Excel

cscherer — Fri, 15 Jan 2016 14:38:17 GMT

Thank you! I will try.

Re: Loading data monthly from Excel

cscherer — Mon, 25 Jan 2016 07:56:55 GMT

thanks again. I decided to load from .csv instead of .xlsx

Re: Loading data monthly from Excel

cscherer — Mon, 25 Jan 2016 07:57:03 GMT

thanks again. I decided to load from .csv instead of .xlsx