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]

stalwar1‌

could you please help me

[vardhancse]

atkinsow‌

Can please let me know any alternative.

Tried some solutions but they are generating separate QVD for each csv file, but the ultimate output should generate one single QVD.

Even though if we delete the csv in share drive also QV should maintain historic data.

[Anonymous]

It may not be the most efficient but this should work.  Assumes EMPId is unique and only adds new records.

============================================================

if isnull(QvdCreateTime(EMP_Data)) then

//Full reload if QVD not exists

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

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 not Exists(EMPId);

Store EMP_Data into EMP_Data.qvd;

ELSE

BaseQVD:

LOAD EMPId,

     Name,

     Location,

     Date_ID

FROM

EMP_Data.qvd

(qvd);

Concatenate(BaseQVD)

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 not Exists(EMPId);

Store EMP_Data into EMP_Data.qvd;

endif;

[vardhancse]

Thank you.

In my original data there is no primary key, in that case can please let me know does above solution will work.

[vardhancse]

Hi,

Tried your solution, its loading but when we delete any old csv files, then its even deleting in the QVD as well.

My ultimate goal is to load all the CSV files, even though we delete the files from share drive historic data should be maintained in QVD

[balar025]

Hi ,

Try this.

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

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

endif;

Store EMP_Data into 1302030\EMP_Data.qvd(qvd);

thanks

Ravi Balar

[vardhancse]

Hi Ravi

Thank you. Tried your solution

If we delete the CSV files also QVD is having historic data, but issue is that

If we take count(EMPId) after every reload the count is getting increased.

[vardhancse]

If are not placing new file and if we reload the app, last loaded records were being added to the previous month.

[balar025]

Hi Sasi,

If your emp_id is present in new file then it will take duplicate. for that you need to use where exists in else condition while loading csv files.

like where not exists(EMPId);