Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
khaycock
Creator
Creator

Loading numerous weekly flat files

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

1 Solution

Accepted Solutions
marcus_sommer

You could just use wildcards within the filename like:

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

or

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

- Marcus

View solution in original post

15 Replies
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
Creator
Creator
Author

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
Creator
Creator
Author

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
Partner - Creator III
Partner - Creator III

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
Creator
Creator
Author

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

You could just use wildcards within the filename like:

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

or

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

- Marcus

khaycock
Creator
Creator
Author

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*