Reload with a specified file

[#1]

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.

[#2]

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

[#3]

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.

[#4]

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.

[#5]

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.

[#6]

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

[#7]

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));

NEXT

DROP Table tables;

NEXT File

Best of luck