Loading numerous weekly flat files

[khaycock]

I have flat files that needed to be loaded in weekly that have the same fields in each one but the date is different at the end. What would be the best way to load these in to a script?

Each ends with the month and the date the report was generated, so like this: download_intl_invoice_report_feb28

How could I load each week in without having to add it to the script each time?

I would do the current week and day thing but reports might not be pulled on the day they are generated each time, so they load would need to pick any recently added weeks to the dashboard

[prieper]

Then you may use FILELIST():

FOR EACH sFile IN FILELIST(MyFileSpecification)

     IF WILDMATCH ('$(sFile)', '*2015*', '*2016*') THEN

          LOAD ...... FROM $(sFile);

     END IF

NEXT sFile

Peter

edit: Wildcards added

[khaycock]

Where do i declare the location of the qvd?

[prieper]

You may do it in the FILELIST, e.g.

FOR EACH sFile IN FILELIST('C:\TEMP\MyQVD*.QVD')

....

Else you may use a variable (inputbox or via script):

LET sFilePattern = 'C:\TEMP\MyQVD*.QVD';

....

FOR EACH sFile IN FILELIST('$(sFilePattern)')

....

Rgds Peter

[khaycock]

Amazing thank you!!!

[ttollin11]

Marcus,

Sorry for the thread resurrection. Is there a way to get these concatenated when loading this way?

[marcus_sommer]

As far as the table-structure is the same all files will be automatically concatenated. To ensure this you need to specify all needed fields instead of using a wildcard like: load * from ... so that no new field could disrupt your load-logic. If new fields should be included or there are some missing you need an explicit concatenate(Table) statement - created by a conditionally statement or even fix to an empty table like:

dummy: load 'dummy' as dummy autogenerate 0;

concatenate(dummy)

load YourLoadStuff ...

drop fields dummy;

- Marcus