multiple excels into qvd (Avoid reload of excel again)

[vikasmahajan]

Dear All

I have following code for getting all excel loaded into qlikview and create qvd  my problem is that if one excel is loaded already in qvd

who to skip that excel file for reloading again again is time consuming !

Set vPathXLS='\\inprgcfl06\HOME\Vikas\DataSources';

AsTrk:

Load

    *

, FileName() as FileName

From

[$(vPathXLS)\Asset*.xlsx]

(ooxml, embedded labels, table is [$(*)]);

Please advise

Thanks in adv.

Vikas

https://lh3.googleusercontent.com/-cGSYPnkvpuQ/AAAAAAAAAAI/AAAAAAAAAAA/FScqHBqttrY/s46-c-k-no/photo....

https://lh5.googleusercontent.com/-lJ_EqP6J25c/Vh6BdRKJ7EI/AAAAAAAACFw/WkUMdpFXR4s/w239-h425/IMG_201...

[Kushal_Chawda]

Set vPathXLS='\\inprgcfl06\HOME\Vikas\DataSources\';  // Your Excel source Path

Set vQVDPath = '\\inprgcfl06\HOME\Vikas\DataSources\QVD\' ;   // Your QVD store path

// Load already created QVD with Existing  Excel files.

Data:

LOAD Name,

     Age,

     FileName, // File name created using Filebasename() fucntion while creating qvd first time

     Salary,

     year

FROM

[$(vQVDPath)Empde.qvd]

(qvd);

// Code to check New excel Files. First 1 load will load only one line so Loading all file names are fast

Check:

first 1 LOAD

     FileBaseName() as FileName1

FROM

[$(vPathXLS)Empdet*.xlsx]

(ooxml, embedded labels, table is Sheet1)

where not Exists(FileName,FileBaseName());

if isnull(FileName1) then

Trace "No New Files are available";

else

for i=1 to FieldValueCount('FileName1');

let vFileName = FieldValue('FileName1',$(i));

concatenate(Data)

LOAD *,

FileBaseName() as FileName

FROM

[$(vPathXLS)$(vFileName).xlsx]

(ooxml, embedded labels, table is Sheet1);

next

end if

// Store the newly  concatenated data into the QVD again

STORE Data into $(vQVDPath)Empde.qvd;

[vikasmahajan]

hello

This code is not considering if i copy new excel file then it should run this file ?

Vikas

[Kushal_Chawda]

Are you getting any error?

[vikasmahajan]

Error is nothing but it seems new excel file code is not reading ???

Any Idea I want to close the same

Thanks

Vikas

[Kushal_Chawda]

which code you are using? Can you paste your code ?

[vikasmahajan]

Set vPathXLS='\\inprgcfl06\HOME\vikas\DataSources\';

set vQVDFileDir='\\inprgcfl06\HOME\vikas\Qvds\';

Data:

LOAD *

FROM

\\inprgcfl06\HOME\vikas\Qvds\AssetTracking.qvd

(qvd);

Check:

first 1 LOAD

     FileBaseName() as FileName1

FROM

[$(vPathXLS)AssetTracking*.xlsx]

(ooxml, embedded labels, table is Sheet1)

where not Exists(FileName,FileBaseName());

if isnull(FileName1) then

  Trace "No New Files are available";

else

for i=1 to FieldValueCount('FileName1');

   let vFileName = FieldValue('FileName1',$(i));

   concatenate(Data)

  LOAD *,

   FileBaseName() as FileName

  FROM

  [$(vPathXLS)$(vFileName).xlsx]

(ooxml, embedded labels, table is Sheet1);

next

end if

// Store the newly  concatenated data into the QVD again

STORE Data into $(vQVDFileDir)Empde.qvd;

[vikasmahajan]

this is my production code but you can apply same with empdet1,2,3 and give me solution you can create empdet4.xls and check whether this code is loading empdet4.xls(new) file ??

Vikas