How to perform incremental Excel file loads where dates are written in Excel file names

[silambarasan]

How to do incremental load?  Extract date from file name for incremental

File Name:

SS_2016-01-31

SS_2016-02-01

Note:Day-1 file stored as qvd and then perform incremental load

gwassenaar‌hic‌swuehl‌

Message was edited by: Silambarasan M

[kkkumar82]

Can you elaborate your requirement ??

[marcus_sommer]

You could extract the dates from the filename like:

date(date#(subfield(filebasename(), '_', 2), 'YYYY-MM-DD'), 'MM/DD/YYYY')

- Marcus

[Kushal_Chawda]

see this

Load only new or updated excel files dynamically

[silambarasan]

ya i tried but its not work

[swuehl]

Not 100% sure what you want to do.

You can get the date from filename using filename() and text functions like Textbetween() or subfield():

Re: Capturing a date that's embedded in an Excel filename

You can get all filenames in a folder and loop over this list using FOR EACH vFile IN FILELIST('Path') ... NEXT vFile

(have a look at the sample in the HELP file)

If you load your historic QVD first, get the last read date, then use the FOR EACH ... NEXT loop and filter all filenames with dates newer than the latest from QVD, you should get what I assume you want.

[vikasmahajan]

Do you want like this  Incremental Load using Timestamps Multiple Excel  ?

Vikas

[marcus_sommer]

Then there is something different by you - where did you try it:

- Marcus

[silambarasan]

Step 1:

Scenario:

LOAD ID,

     Date(MakeDate(SubField(SubField(FileBaseName(),'_',4),'-',1),SubField(FileBaseName(),'-',2),SubField(FileBaseName(),'-',3))) as  Date

FROM

[..\New\SS_*.xlsx]

(ooxml, embedded labels, header is 9 lines, table is [Container 1]);

STORE Scenario into [..\New\Scenario.qvd](qvd);

DROP Table Scenario;

Step 2:

Scenario:

LOAD ID,

     Date

FROM

[..\New\Scenario.qvd]

(qvd);

Add:

Load Max(Date) as Maximum Resident Scenario;

Let vMaxDate=peek('Maximum ',0,Add);

Concatenate(Scenario)

LOAD ID,

     Date(MakeDate(SubField(SubField(FileBaseName(),'_',4),'-',1),SubField(FileBaseName(),'-',2),SubField(FileBaseName(),'-',3))) as  Date

FROM

[..\New\SS_*.xlsx]

(ooxml, embedded labels, header is 9 lines, table is [Container 1])

Where num(MakeDate(SubField(SubField(FileBaseName(),'_',4),'-',1),SubField(FileBaseName(),'-',2),SubField(FileBaseName(),'-',3))) > $(vMaxDate) ;

[silambarasan]

can't download your file..please attach