Importing ISO 8061 timestamps using sqlldr

Many web service APIs use the ISO 8061 standard for test representations of dates and times. For example, the Salesforce Force.com API uses this standard for its dateTime datatype. Even though Force.com stores dateTime values at only one-second resolution, it represents these values at millisecond resolution. It also uses the UTC timezone, abbreviated as "Z". Here is an example dateTime; notice the "T" that separates the date from the time, the three zero digits to the right of the decimal point, and the "Z" for UTC (aka "Zulu time"):

2010-05-11T18:57:15.000Z

It's fairly easy to create such a representation using Oracle's to_char() function, but you need to use double-quote characters:

SQL> select to_char(sysdate,'YYYY-MM-DD"T"HH24:MI:SS".000Z"') from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DDT
----------------------------
2010-05-11T18:57:15.000Z

However, it's a bit trickier to import a text file using Oracle's sqlldr using a "date mask". These sqlldr masks must be enclosed in double-quote characters. But this obviously confuses sqlldr, since it not expecting double-quotes within the mask. Using single quotes, either within the mask or to delimit the mask, also fails.

It turns out that you can use backslashes to escape the double-quotes within a sqlldr date mask. Here is an example sqlldr control file that demonstrates this mask, used to import a Salesforce-generated CSV file:

OPTIONS(DIRECT=true)
LOAD DATA
 INFILE  'salesforce_export.csv'
 APPEND
INTO TABLE salesforce_export
   FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
       ID,
       EMAIL,
       CREATEDDATE DATE "YYYY-MM-DD\"T\"HH24:MI:SS\".000Z\"",
       CREATEDBYID,
       YADA__C,
       YADA_YADA__C,
       YADA_YADA_YADA__C
)