Load multiple xls and save as a QVD

[davyqliks]

HI, Thank you for your expertise in advance:

I am trying to use the loop  load store method of creating and loading from a qvd.

I have multiple xls in a folder which we have been loading all of the data each time using *.xls however this is becoming more time consuming daily (obviously).

So, i have successfully managed to created the script but can only manage it to check data from 1 of the xls in the given folder

Please see the below and inform if possible a way to loop and read from every file and then append to the qvd.

Thankyou

TAB- SETUP LOOP

Let VStartDate = '42800';

Let VEndDate   =today()-1;

TAB - LOOP START

For vCurser = VStartDate to VEndDate

Let vDateToLoad =    date(vCurser,'YYYY-MM-DD');      //'2018-02-22'

let vqvdsuffix  = text(date(vCurser,'YYYY-MM'));

TAB-LOAD

@1:

LOAD

(data removed)

FROM

\\*************_Dash\Test\test\*.xls

(html, codepage is 1252, embedded labels, table is @1)

Where Date_Imported='$(vDateToLoad)';

TAB- STORE

if NoOfFields ('@1') > 0 then

if not isnull (filetime('\\Scan01\bi\Qlikview\Documents\Development\1LabelData_$(vqvdsuffix).qvd')) Then //Check the file exists

Concatenate (@1)

LOAD

(data removed)

FROM

'\\************\1LabelData_$(vqvdsuffix).qvd'

(qvd)

Where not Exists (Date_Imported);

END IF

Store @1 into '\**************\1LabelData_$(vqvdsuffix).qvd'(qvd);

DROP Table @1;

END IF

Thank you in advance for consideration

Daniel

[wade12]

i suggest you do some research on incremental loading.

3 types of incremental loading:

•      insert
•      insert & update
•      insert, update & delete

choose which best suits you then apply.

best wishes.

[poluvidyasagar]

I have faced same issue before.

I would load all the xls files into Qvd and then Load the data from QVD and then load the latest data from xls file and add it to the existing qvd file.

Like Taylor said, look at some incremental loading details.

Thanks,
Vidya

[achettipalli]

Some sample code from @amit Saini:

Code to Access all files in directory (subdirectories)

SUB DoDir (Root)

FOR each File in filelist(Root& '\*.xls')

Tab1:

LOAD <<Field Names>>

FROM

[$(File)]

(biff, embedded labels, table is <<Table name>>$);

NEXT File

FOR each Dir in dirlist (Root&'\*')

CALL DoDir(Dir)

NEXT Dir

END SUB

CALL DoDir('Your Directory');

Code to Access All sheets in a Excel work book

Directory;
for a=1 to 3
LOAD employee
FROM
Looping\constructs1.xlsx // this is the excel sheet name
(ooxml, embedded labels, table is Sheet$(a));// $ sign allows to access the value of a
Next

Code to Access all sheets in a excel work book and convert to QVD

For a=1 to 4
Directory1:
LOAD employee
FROM
Looping\constructs1.xlsx
(ooxml, embedded labels, table is Sheet$(a));
//STORE Directory INTO C:\Users\asaini\Desktop\Looping\Directory$(a).QVD;
Next
STORE Directory1 INTO C:\Users\amits\Desktop\Looping\Directory.QVD;
Drop Table Directory1;
Directory;
LOAD employee
FROM
Looping\Directory.QVD
(qvd);

Reference : Dynamic Load of multiple qvds from a folder | Qlik Community

[achettipalli]

For incremental process into an existing QVD:

//Get the last updated date from you QVD file

LastUpdatedDate:

LOAD

MAX(datecolumn) as MaxDate FROM [qvdfilepath...]/file.qvd(qvd);

// get the first row value

LET vLastUpdatedDate=peek('MaxDatefilter',0,'LastUpdatedDate');

//Filter the data from excel file

Incremental:

Load columns FROM [excelfilepath].xls where datecolumn> $(LastUpdatedDate);

Concatenate

LOAD columns FROM [qvdfilepath...]/file.qvd(qvd);

STORE Incremental into [qvdfilepath...]/file.qvd(qvd);

DROP table Incremental;