How to load a csv file into a database when the filename for the CSV changes everyday

Question

Trying to load a .csv into a database using SQL loader but The filename is changing every day but will always start with health_safety.

Here is the control file, it loads the data but I need to put in a specific file name, so if the filename changes, it breaks.

LOAD DATA
INFILE "\\ALI.PRI\Files\Apps\CRIS\test\Trents tests\Cris Too Tidal Job\HEALTH_SAFETY\health_safety345.csv"
INTO TABLE CRIS.CRIS_HEALTH_SAFETY_DATA
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
REPORTING_PERIOD,
PROJECT_ID,
VENDOR_ID,
HEALTH_SAFETY_ID,
HEALTH_SAFETY_ACTUAL,
HEALTH_SAFETY_DATE
)

Please post code as text rather than as image! – aschipfl Aug 06 '19 at 15:32 — aschipfl, Aug 06 '19 at 15:32
I did but the format gets messed up – Trent Aug 06 '19 at 15:52 — Trent, Aug 06 '19 at 15:52
@Trent try using ``` code here ``` – IoCalisto Aug 06 '19 at 15:57 — IoCalisto, Aug 06 '19 at 15:57

score 0 · Answer 1 · answered Aug 06 '19 at 17:36

You don't say what version of Oracle you are on, but if you are on 12c the infile clause now supports wildcard characters.

If you are lucky, you could use:

INFILE "\\ALI.PRI\Files\...\health_safety*.csv"

Otherwise, you'll need a wrapper script that passes the filename in using the DATA argument. Do a little searching here, there are examples.

How to load a csv file into a database when the filename for the CSV changes everyday

1 Answers1