Incremental Load with multiple CSV files and single sheet

[vardhancse]

Hi,

Have one requirement:

1. Load multiple CSV files appended with date e.g: 2017-03-28 EMP Data.csv [Done]

2. based on file name date column need to be extracted[Done]

2. All csv files will be one single folder[Done]

3. Load all csv files and concatenate to one single table[Done]

4. After loading the files, old csv files will be deleted [Pending]

4. daily new file will be placed and append new file records to existing records in QV table[Pending]

PFA sample csv files and qvw

Can any one please let me know the incremental load logic need to be applied here.

[vardhancse]

Thank you used where condition and your script works fine, can please let me know any way we can achieve with our primary key

[balar025]

Hi Sasi,

you can take max date from qvd and check the condition. Like extracted date of files should be greater than max date from qvd.

For Example,

Let vFilePath = '1302030\*EMP Data.csv';

Let qvdExists = isNull(QvdCreateTime('1302030\EMP_Data.qvd'));

if $(qvdExists) < 0 then

Trace New file came.No Qvd is there;

EMP_Data:

LOAD EMPId,

    Name,

    Location,

    Date(Left(FileName(),10),'YYYYMMDD')    as Date_ID

FROM

$(vFilePath)

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

else

Trace New file came with existing qvd;

EMP_Data:

LOAD*

FROM

[1302030\EMP_Data.qvd]

(qvd);

MaxDateQvd:

Load Max(Date_ID) as MaxDate

Resident EMP_Data;

Let vMaxDate = Peek('MaxDate',0,'MaxDateQvd');

EMP_Data:

LOAD EMPId,

    Name,

    Location,

    Date(Left(FileName(),10),'YYYYMMDD')    as Date_ID

FROM

$(vFilePath)

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where Date(Left(FileName(),10),'YYYYMMDD')>$(vMaxDate);

Bold part are changes in code.

-Ravi Balar

[vardhancse]

Thanks once will try now

[Anonymous]

Sorry you're having trouble.  My code would never just delete a QVD.  There is no script in there that would delete a file.  Worse thing any store command should do is create a QVD with no records in it.

Is that what is happening?

If so, the IF statement I had must not be 'catching'.  I would check the file paths and no a debug to see what is happening.

If Ravi's code is working for you then go with that.

[vardhancse]

Hi Ravi

With Date > condition unique records are not getting recognized

[balar025]

HI Sasi,

Can you please explain on Unique record? It is based on what? EMPId or EMPId&Date ?

Regards,

Ravi Balar