Excel file updation using timestamps

[vikasmahajan]

Dear all ,

I have 3 excel files , I stored into QVD (allqvd) .  Now if same name file with diff. timestamps file copied then existing data from allqvd should be deleted and new data from new excel file should appended in allqvd file.

Please share logic.

Thanks in adv.

Vikas

[PradeepReddy]

what is the time stamp field/modification date field you are considering for this process..?

[vikasmahajan]

filetime() is a timestamps for this.

Vikas

[vikasmahajan]

Sunny can you give script for the same ??

@Sunny T

Vikas

[vikasmahajan]

please help

[sunny_talwar]

Try the following code, seems to be working to some degree. May be you need to make some changes, but I guess a direction to look into

For Each file in 'AssetTrackingAug1.xlsx', 'AssetTrackingSep1.xlsx', 'AssetTrackingOct1.xlsx'

  LET vVar = Replace(Right('$(file)', 9), '.xlsx', '') &'Time';

  LET vTime = Num(FileTime('$(file)'));

  Table:

  LOAD Financial_Code,

      Tower,

      Family,

      Set,

      Offering,

      Product,

      Availability,

      Price,

      Segmentation,

      Source_System,

      charge_month,

      inventory_month,

      FileName() as FileName,

      FileTime() as FileTime

  FROM

  $(file)

  (ooxml, embedded labels, table is Sheet);

  Main:

  LOAD Financial_Code,

  Financial_Code as FC,

      Tower,

      Family,

      Set,

      Offering,

      Product,

      Availability,

      Price,

      Segmentation,

      Source_System,

      charge_month,

      inventory_month,

      FileName,

      FileTime

  FROM

  Table.qvd

  (qvd)

  Where FileName = '$(file)' and FileTime <= $(vTime);

NEXT

FinalTable:

NoConcatenate

LOAD *

Resident Main;

Concatenate(FinalTable)

LOAD *

Resident Table

Where not Exists(FC, Financial_Code);

DROP Field FC;

STORE Table into Table.qvd (qvd);

DROP Tables Table, Main;

[PradeepReddy · Accepted solution]

see the attachment, it might be helpful...

[vikasmahajan]

While loading this script i am getting this error ?

[PradeepReddy]

I have assumed( Filkes given by you) your file names like..

File Name: AssetTrackingAug1; SheetName: AugInv1

File Name:  AssetTrackingSep1; SheetName: SepInv1

If there is any changes apart from these formats, then we need to  change our script accordingly.

Can you share exact file names and Sheet names, so that will try to resolve the issue.

[vikasmahajan]

ok no problem i will rename and check pls wait.

[vikasmahajan]

Thanks Pradeep

This script is worked as per expected.

Vikas.