Reading a Delta-File from a certain Date

[thanhphongle]

Hello together,

I am receiving  from our business partner everyday a delta-file

2019-01-14_sale_orders.xlsx

2019-01-15_sale_orders.xlsx

2019-01-16_sale_orders.xlsx

...

which includes sales Information.

I am using the command LOAD Field1, Field2, ... FROM Path\*.xlsx in order to load the files.

From the beginning of 15.01.2019  our business partner added a new field called Status = {cancallation, retour, order}, some kind of flag in the file

2019-01-15_sale_orders.xlsx

When I try to add the new field in the script, after some time the script loading fails because it cant find the new field Status in the files before.

Is there a way to define a command which loads reads the csv. from the date

2019-01-15_sale_orders.xlsx

2019-01-16_sale_orders.xlsx

...

?

My Idea was to load sepparatly the fields

Status,

Ordernumber

and use an APPLY MAP afterwards.

Or is there a better solution in case of the Business Partner is adding new fields that i dont need to do it that way anymore?

Hope you can help me.

[jonathandienst · Accepted solution]

Rather than a simple wildcard, you need to use a For Each load so you can control the concatenation. Pattern like this:

SALES:
LOAD 0 as Dummy
AUTOGENERATE 0; 

For Each vFile in FileList('Path\*.xlsx')4
	Concatenate(SALES)
	LOAD * 
	FROM [$(vFile)]
	(ooxml);
Next

DROP Field Dummy;

The extra field(s) will have NULL values for the earlier dates.

[thanhphongle]

Thank you very much for your help. It works perfectly!