Incremental Load of excel files using timestamps

[vikasmahajan]

Hi Team,

Please find attached QVW file.  I have 2 excel files in feature it may be 12 or 13 .. etc  , I want to achieve the following things

- Creation of QVD  all excel files from folder with filename & Timestamps - > Achieved

- Loop  all excel files and check whether new excel file is available in folder if yes upload it  -> Achieved

- Loop all  excel file and check whether file is modified ?  if yes - > Delete all records from

                                                            QVD and take latest data from excel file                              - >  Require Help

I have attached qvw file also any one please help in this qvw.

I want to compare timestamps of existing excel file data from qvd and modified files from folder if found changed then delete data from QVD and take latest data into qvd.

I have attached sample excel files also.

Vikas

[sunny_talwar]

Hey Vikas, my response to your previous post did not help? What was missing?

[vikasmahajan]

No it was not worked sorry to update you.

Vikas

[vikasmahajan]

Sunny I have attached QVW  in this qvw I want to compare the timestamps of loaded data into qvd

Vs Modified files in a folder , if found modification then delete records from QVD and take latest records into qvd.

Vikas

[sunny_talwar]

I will have a look at it in a bit, if nobody else come up with a solution.

[MayilVahanan]

Hi

Edit:

May be try like this

Map_FileDetails:

Mapping LOAD

  FileName,

     FileTime

FROM $(vQVDFilePath)FileDetails.QVD(qvd);

for i=0 to NoOfRows('Temp_FileNames')-1

   Let vFileName= peek('FileName',$(i),'Temp_FileNames');

   Let vFile=subfield('$(vFileName)','\','-1');

   Let vFileTime= peek('FileTime',$(i),'Temp_FileNames');

   Let vFilePrevTime=timestamp(ApplyMap('Map_FileDetails','$(vFile)',0));

   TRACE $(vFileName) ;

   TRACE $(vFileTime) ;

   Temp1:

   LOAD *, ApplyMap('Map_FileDetails', FileName, 0) AS NewOrOldFile,

   If(FileTime - ApplyMap('Map_FileDetails', FileName, 0) > 0, 1, 0) AS  Flag;

  Load *,

      Timestamp(FileTime()) as FileTime,

      FileName() as FileName

  from $(vFileName)

  (ooxml, embedded labels, table is '$(*)');

  //Load the newly modified files

  ExistingFiles:

  LOAD Distinct FileName as ExistingFileName Resident Temp1 Where Flag = 1;

  NoConcatenate

  //Load the qvds which is not modified

  Files:

  LOAD * from $(vQVDFilePath)ALLQVD.qvd(qvd)

  where not Exists(ExistingFileName, FileName);

  //Concatenate the modified excel file

  Concatenate(Files)

  LOAD * Resident Temp1 Where Flag = 1;

  //Add the New excel file

  Concatenate(Files)

  LOAD * Resident Temp1 where NewOrOldFile = 0;

Store Files into $(vQVDFilePath)ALLQVD.qvd;

DROP Table Files, Temp1, ExistingFiles;

NEXT

ENDIF;

[vikasmahajan]

Error is appering  FileName Field not found.

[vikasmahajan]

Can you change my qvw and send me please check this error

Vikas

[MayilVahanan]

Hi

PFA

[vikasmahajan]

I will check and get back you , This executes at your end  ??

Vikas