Incremental Load for Multiple Excels using filename as PK

[vikasmahajan]

Dear Team,

I have excel files like Jan,Feb,Mar ... etc Sample 2 file are attached.

Requirement :

1.  Creation of qvd  store all data into Single QVD (ALLDATA.QVD)

2. if user changed data into excel files again same name file will be uploaded into directory , And modified excel file

     should get updated into ALLDATA.QVD and old records should get deleted.

Please note : - I require script  using my excels files only , so can any one help me to achieve the same.

Vikas

[MindaugasBacius]

For the first requirement:

LET path = '\\your path';

SUB DoDir(Root)
FOR each File in filelist( Root & '\*.xlsx') 
  Order:
  LOAD *
  FROM
  [$(File)]
  (biff, no labels, table is [Sheet1$]);
NEXT File  

FOR each Dir in Dirlist (Root&'\*')  
     CALL DoDir(Dir)  
NEXT Dir  

END SUB   

CALL DoDir('$(path)\Folder') 

STORE Order into order.qvd (qvd);

This script allows to load all xlsx files from one folder into single table. At the end it stores the data.

[kaushiknsolanki]

Hi Vikas,

Have a look at the link below.

https://community.qlik.com/thread/61585

Regards,

Kaushik Solanki

[vikasmahajan]

Hi Kaushik,

Thanks for reply  actually I want to store into QVD and every time wants to update qvd with modified file if user copy modified excels into folder.

Vikas

[Peter_Cammaert]

Step-by-step approach:

1. Figure out the creation date of ALLDATA.QVD or (on very first load when ALLDATA.QVD doesn't exist yet) set date to something early (like Jan 1st, 1980)
2. Create new table NewAllData and load it with all Excels whose file modification date is more recent than date from step 1
3. From ALLDATA.QVD, load all months that do not exist yet in NewAllData.
4. Store NewAllData into ALLDATA.QVD

Best,

Peter

[vikasmahajan]

Thanks peter

can you provide script for the same ?

vikas