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

[marcus_sommer]

You could use something like:

subfield(filebasename(), '_', 5) as FileDate

to extract feb28 from the filename. Another method might be to use the filetime with filetime().

- Marcus

[khaycock]

How would I incorporate this with the load?

[marcus_sommer]

You could it directly use within the load like:

table:

load *, subfield(filebasename(), '_', 5) as FileDate, filetime() as FileTime

from Source;

- Marcus

[khaycock]

Sorry if I sound stupid but I'm struggling to understand what this does? Why am I creating a field for the file? I want to make it so the source is flexible each load to what it is loading in.. So if i have a file that is for Feb21 and Feb28, it loads the both in with the same piece of script rather than me having to add both and concatenate.

[RonaldDoes]

Hi Kathryn,

Sounds like you just want to load all files, regardless of their names.

Tablename:

LOAD

{fields in your table}

FROM

download_intl_invoice_report_*;

The use of wildcards in filenames is permitted, so this will load download_intl_invoice_report_feb28 as well as download_intl_invoice_report_dec01

I hope this helps you.

With kind regards,

Ronald

[marcus_sommer]

Please provide the relevant script-part to your question.

- Marcus

[khaycock]

So this is what I have currently:

Left join (Oracle)

LOAD

  'Non-Blue Box' AS Type,

  [Account Number],

     [Invoice Number],

     [Invoice Date],

     [Amount Due],

     [Shipment Number],

     [Collection Record],

     [Reference No.1],

     [Reference No.2],

     [Reference No.3],

     [Reference No.2] & '_' & [Reference No.1] AS KEY1,

     ([Collection Date]),

     [Net Charges]

FROM

[..\..\Data\FlatFiles\ups_download_intl_invoice_report_Feb21.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Left join (Oracle)

LOAD

  'Non-Blue Box' AS Type,

  [Account Number],

     [Invoice Number],

     [Invoice Date],

     [Amount Due],

     [Shipment Number],

     [Collection Record],

     [Reference No.1],

     [Reference No.2],

     [Reference No.3],

     [Reference No.2] & '_' & [Reference No.1] AS KEY1,

     ([Collection Date]),

     [Net Charges]

FROM

[..\..\Data\FlatFiles\ups_download_intl_invoice_report_Feb28.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

But ideally I want to be able to load them all at once, with * or a variable or something that means I haven't got to add each week individually

[marcus_sommer · Accepted solution]

You could just use wildcards within the filename like:

FROM [..\..\Data\FlatFiles\*.csv]

or

FROM [..\..\Data\FlatFiles\ups_download_intl_*_report_???*.csv]

- Marcus

[khaycock]

Great thanks. I have a similar situation where I have data tables for each year and i only want to load from 2015 upwards. For example, the tables are called TABLE2015, TABLE2016 and TABLE2017. Can I use wildcards for these? There are also tables that have 2014 and 2013 data so I can't just do TABLE*