check if a new file has been loaded into directory

[alec1982]

hi,

I have a load statement based on file name which has date in it. for example 2015-10-01TotalSales.xlsx..

and I have a variable that has a date in it too. 2015-10-01

my load script has

load * from

[..\Data\2*.xlsx]

(ooxml, embedded labels, table is Sales)

where (left(FileBaseName(),10))> $(vLastFileDate);

now if there is no files with value greater than the variable I get an error..

is there a way to bypass the error if there is no files with dates greater than the variable?

Thxs,

Alec

[swuehl]

What error message do you get?

I would rather use a FOR EACH in FileList loop and skip the Load completely instead of using a WHERE clause though. But I assume the error is caused by something else.

[alec1982]

thank you for the answer. I used do/while and worked well in my case.

[alec1982]

I take that back.. the while statement caused duplicates to be inserted into the table every time I try to reload the file..

What I am trying to do here is incremental load based on file name where the file name has date string in it. (2015-10-01Sales.xlsx) nd take in account if the qvd has already been created or not.

I added if statement to check if the qvd exist and when I am doing "else" and do concatenate I get errors..

Do you have a sample with incremental load based on file name that I can use?

[alec1982]

I tried the following

FilesList:

LOAD  max(date(FileDate)) as FileDateTemp

FROM

[..\QVD\incrementalLoad.qvd]

(qvd);

Let vLastFileDate= Peek('FileDateTemp',-1,'FilesLis');

Drop table FilesList;

FOR Each File in filelist ('..\Data\2*Sales.xlsx')

Let vCheck =  if(num(Date(left(FileBaseName(),10)))> num(date(vLastFileDate)),1,0);

IF vCheck THEN

NewData:

LOAD Date(left(FileBaseName(),10)) as FileDate,

     Meterial,

     Sales    

from [..\Data\2*Sales.xlsx]

(ooxml, embedded labels, table is Sales);

store NewData into ..\QVD\incrementalLoad.qvd;

I am getting no Data..

[jagan]

Hi,

Try like this

LET vFilePath = 'C:\' & '2015-10-01TotalSales.xlsx';

If FileSize('$(vFilePath') > 0 Then

     Statements

ENDIF;

Hope this helps you.

Regards,

Jagan.

[alec1982]

thank you but that doesn't work.. I am checking for multiple files...

[settu_periasamy · Accepted solution]

Hi,

May be try this...

FilesList:

LOAD  max(date(FileDate)) as FileDateTemp

FROM

[..\QVD\incrementalLoad.qvd]

(qvd);

Let vLastFileDate= Peek('FileDateTemp',-1,'FilesLis');

Drop table FilesList;

//FOR Each File in filelist ('..\Data\2*Sales.xlsx')

//Let vCheck =  if(num(Date(left(FileBaseName(),10)))> num(date(vLastFileDate)),1,0);

//IF vCheck THEN

FOR Each File in filelist ('..\New folder\2*Sales.xlsx')

LOAD Distinct FileBaseName()as BaseFile from [..\New folder\2*Sales.xlsx]

(ooxml, embedded labels, table is Sales);

Let vBaseFile= Peek('BaseFile',-1);

Let vCheck =  if(num(Date(left(vBaseFile,10)))> num(date(vLastFileDate)),1,0);

IF vCheck THEN

NewData:

LOAD Date(left(FileBaseName(),10)) as FileDate,

    Material,

    Sales

from [..\New folder\2*Sales.xlsx]

(ooxml, embedded labels, table is Sheet1);

store NewData into incrementalLoad.qvd;

ENDIF

Edit:

Instead of Loading your file again, you can use the subField.

Like

FOR Each File in filelist ('..\New folder\2*Sales.xlsx')

if(num(Date(left(SubField(File,'\',-1),10)))> num(date(vLastFileDate)),1,0);

[alec1982]

thank you

[tamilarasu]

Hi Alec,

I have doubt in your script. Are you sure that the below statement correct!!

FOR Each File in filelist ('..\New folder\2*Sales.xlsx')

----

IF vCheck THEN

NewData:

LOAD Date(left(FileBaseName(),10)) as FileDate,

    Material,

    Sales

from [..\New folder\2*Sales.xlsx]

(ooxml, embedded labels, table is Sheet1);

If the vCheck condition is true then it will load all the files. Hence, your output must be wrong. Could you check your output.