topic Re: Load multiple xls and save as a QVD in QlikView

Load multiple xls and save as a QVD

davyqliks — Thu, 18 Oct 2018 14:13:41 GMT

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

Re: Load multiple xls and save as a QVD

wade12 — Thu, 18 Oct 2018 14:46:46 GMT

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.

Re: Load multiple xls and save as a QVD

poluvidyasagar — Thu, 18 Oct 2018 14:51:53 GMT

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

Re: Load multiple xls and save as a QVD

achettipalli — Thu, 18 Oct 2018 16:24:02 GMT

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

Re: Load multiple xls and save as a QVD

achettipalli — Thu, 18 Oct 2018 16:34:25 GMT

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;