topic Re: Reload with a specified file in QlikView

Reload with a specified file

Thu, 06 Dec 2012 07:47:29 GMT

Dear All,

I have a folder on the server where on daily basis i receive Excel files ( 56 excel files ) with a date tag, example : LTD_Dump_UVRL 6 Dec 2012.XLS

the folder contains files from 2006 almost 1.2 TB of data

what i need is QVW application to reload only with the curent day files

How can i fix the reload of the QVW only with the current files in an automised way.

Re: Reload with a specified file

Thu, 06 Dec 2012 09:30:20 GMT

hi,

Try this

Let vDay = Day(Now());

Let vMonth = Month(Now());

Let vYear = Year(Now());

T1:

LOAD *

FROM

'LTD_Dump_UVRL '$(vDay) $(vMonth) $(vYear).xlsx

(ooxml, embedded labels, table is Sheet1);

Best of luck

Re: Reload with a specified file

Thu, 06 Dec 2012 09:34:53 GMT

Hi,

u can use the Icremental Load concept

Step1: Create Qvd File to store the XLS Data LTD_Dump_UVRL.qvd

Step 2: Then, U can write script like this

sub ScanFolder(Root)

//This for each loop is used to allow text file, because we have a log file as text file

          for each FileExtension in 'XLS'

          //This for each loop is used to allow the applicable file

                    for each FoundFile in filelist( Root & '\*.' & FileExtension)

                     if(Date(FileTime('$(FoundFile)'),'DD/MM/YYYY')=Date(Today(),'DD/MM/YYYY')) then

                     //To store the Store statement line into TempBase

TempBase:

LOAD *

FROM

[$(FoundFile)];

//To store the TempBasedata with Record Number into BaseTable

BaseTable:

load * Resident TempBase;

Drop table TempBase;

//Drop table TempLastValue;

endif

                    next FoundFile

          next FileExtension

end Sub

////////Incremental Load

MainTable:

baseTable concatenate Load * form

LTD_Dump_UVRL.qvd;

Store MainTable into LTD_Dump_UVRL.qvd;

Regards,

Kabilan K.

Re: Reload with a specified file

Wed, 19 Dec 2012 07:25:19 GMT

Dear Satish,

Thanks a lot it works

The logic suggested by you worked but without single quotes,

now new problem is, the excel sheets m using ( 56 files ), some files are having multiple tabs

i.e Sheet1, Sheet2, Sheet3 which differs in every excel file received

i can use thm in application by selecting the sheet name

but how can i automise those.

Re: Reload with a specified file

Wed, 19 Dec 2012 07:25:54 GMT

Dear Satish,

Thanks a lot it works

The logic suggested by you worked but without single quotes,

now new problem is, the excel sheets m using ( 56 files ), some files are having multiple tabs

i.e Sheet1, Sheet2, Sheet3 which differs in every excel file received

i can use thm in application by selecting the sheet name

but how can i automise those.

Re: Reload with a specified file

Thu, 27 Dec 2012 10:08:19 GMT

hi,

try this but not sure it's a good way to follow.

Let vDay = Day(Now());

Let vMonth = Month(Now());

Let vYear = Year(Now());

Set vNo = 1;

Set ErrorMode = 0; //ignore error if sheet not found and continues execution

Do while vNo < 4

T1:

LOAD Field1

FROM

'LTD_Dump_UVRL '$(vDay) $(vMonth) $(vYear).xlsx

(ooxml, embedded labels, table is Sheet$(vNo));

LET vNo = $(vNo) + 1;

LOOP

Set ErrorMode = 1;

Best of luck

Re: Reload with a specified file

Thu, 27 Dec 2012 14:57:36 GMT

Hi,

try this, better script than previous bcoz it is dynamic process to know the number of sheets in each excel file.

FOR Each File in FileList ('C:\ExcelFiles\ExcelsData\*.xls')

ODBC CONNECT TO [Excel Files;DBQ=$(File)];

tables:

SQLtables;

DISCONNECT;

FOR i =0 to NoOfRows('tables')-1;

LET sheetName = PurgeChar(Peek('TABLE_NAME',i,'tables'),'$');

Data:

LOAD * From $(File)(ooxml, embedded labels, table is $(sheetName));

DROP Table tables;

NEXT File

Best of luck