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
Then you may use FILELIST():
FOR EACH sFile IN FILELIST(MyFileSpecification)
IF WILDMATCH ('$(sFile)', '*2015*', '*2016*') THEN
LOAD ...... FROM $(sFile);
edit: Wildcards added
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)')
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;
load YourLoadStuff ...
drop fields dummy;