0

A client site has supplied the following extract file for us to load into our database.

The problem is, for certain rows (the second row for example) the CREATED_DATE and the LAST_UPDATE_DATE are in "dd Mmm YYYY..." date format when the rest the rows (such as the top) are in the format of "YYYY-MM-DD HH24.MI.SSXFF"

PRIMARY_ID  ID VALUE       CREATED_DATE          LAST_UPDATE_DATE
20166267    20834830491    2012-04-30 08:18:00   2012-04-30 08:18:00
20166536    9112           01 Oct 2010 17:27:04  01 Oct 2010 17:27:04

My questions are: Q1. To avoid having to request an extract, can we manipulate these “dd Mmm YYYY...” formatted dates at import time in SQL Loader using the .ctl script? CUrrently my .ctl is

My .ctl file is scripted to import using:

IDENTIFIER_START_DATE   TIMESTAMP "YYYY-MM-DD HH24.MI.SSXFF",
LAST_UPDATE_DATE        TIMESTAMP "YYYY-MM-DD HH24.MI.SSXFF"

Q2. Is simply asking them for a re-extract with all date formats as requested the best practice in situations like this?

Nimbocrux
  • 509
  • 2
  • 10
  • 27
  • 2
    You could load into a staging table as varchar columns and convert from there (or use an external table). Not sure you can do much within the .ctl though. I'd be concerned about less obvious problems in other columns, or even - though that doesn't appear to be an issue from the sample - confusion between mm/dd and dd/mm, which could lead to subtle corruption or failures. I don't think it's unreasonable to expect sane data. Reasonableness doesn't always come into it of course... – Alex Poole Mar 07 '13 at 00:02

2 Answers2

2

Whether to request a re-extract of the data depends on a number of factors.

  • Is this a one-time process or an ongoing data feed? It may be perfectly reasonable to try to do your best with a one-time load of data where it is easier to eyeball the outliers. If you are going to manage an ongoing data feed, it generally makes much more sense to agree on a strict standard for the file rather than trying to manually inspect problematic rows.
  • Does the client have an incentive to make your load process simple and repeatable? Or was the client sold on a fixed price to load the data in whatever format they want to provide it? If the client has an incentive to make the load process simple and repeatable, it makes sense for them to invest the time to generate a clean file. If you've sold them a fixed price for whatever work needs to be done to turn the file into coherent data, on the other hand, they probably won't be pleased if you push a lot of that work back on them.
  • Are there rows where the data is ambiguous? For example "01-02-03" could refer to Jan 2, 2003 or Jan 2, 1903 or Feb 3, 2001 or a number of other dates. If there is ambiguity, it makes sense to request a re-extract.

As to how to load the data, while it is possible to do this in a single step, you generally wouldn't want to. It would generally make more sense to load the data into a staging table (or use an external table) where all the columns are declared as VARCHAR2 and then write some ETL logic that transforms the data into the appropriate data types (and logs errors for the data that cannot be converted). For example, if you loaded the data into a staging table where all the columns were defined as VARCHAR2, you could use something like the my_to_date function in this thread to try a number of different format masks to find one that works (if there are a lot of possible masks, you may want to iterate through a collection rather than hard-coding the two masks as I did in that example).

One additional point... An Oracle DATE stores the time to the second which appears to be the precision of the data you're being given. It would, therefore, seem to make more sense to load the data into a DATE column rather than a TIMESTAMP column.

Community
  • 1
  • 1
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
1

Use this .ctl script: 

load data
append
into table schema_name.table_name
fields terminated by ';' optionally enclosed by '"'
(
PRIMARY_ID,
ID_VALUE,
CREATED_DATE "to_date(:CREATED_DATE, case when regexp_substr(:CREATED_DATE,'\w+',1,2)=regexp_substr(:CREATED_DATE,'\d+',1,2) then 'YYYY-MM-DD HH24:MI:SS' else 'dd Mon YYYY HH24:MI:SS' end)",
LAST_UPDATE_DATE "to_date(:LAST_UPDATE_DATE, case when regexp_substr(:LAST_UPDATE_DATE,'\w+',1,2)=regexp_substr(:LAST_UPDATE_DATE,'\d+',1,2) then 'YYYY-MM-DD HH24:MI:SS' else 'dd Mon YYYY HH24:MI:SS' end)"
)
Egor Skriptunoff
  • 23,359
  • 2
  • 34
  • 64