Problems with MS Access text export -> sqlldr -> Oracle

Sorry for the multiple cross-posts but I'm in great need of assistance:

We have an Oracle based CRM app with hooks into VBA. There is an msaccess front end (written by others) for technicians to enter data while in the field. Upon returning the msaccess db is syncronized with oracle in the following manner:

From the CRM app the user initiates a VBA script which calls the DoCmd.transfertext method to have access create text files of certain tables.

  ObjectDoCmd.TransferText acExportDelim, , "export_spec", C:\pathtofile\filename.txt

The text files are comma delimited with text data quoted similar to:

  2877,"customercode","Company1","some notes here",235   ...

The text file is then loaded into Oracle with sqlldr. Here is the sqlldr control file for the above table:

  LOAD DATA
  APPEND
  INTO TABLE COMPANY
  (
  ORACLEID char terminated by ",",
  CBN char enclosed by '"',
  NAME char enclosed by '"',
  NOTE char enclosed by '"',
  IMPORTID char terminated by whitespace   )

All is fine except when users enter multiple lines of text in thei "NOTE" fields. Access will export them but the exported text file includes newline characters (carraige return line feed, CRLF):

  2877,"customercode","Company1","some notes here<CRLF>   some more notes here<CRLF>
  and so on<CRLF>
  and so on",235

(The <CRLF> is the newline chars).

sqlldr chokes on this!!! The NOTES field can be dozens of lines long.

Now, I know that I can tell sqlldr to use a different "end of record" character like the "|" or something but I cannot find a way to tell access to end records with a different character. I've looked for an appropriate export specification and can't find anything that exists.

Is there any way to fix this without re-writing the program to use A) ODBC, B) fixed width text files.

Thanks

Matt Butler
Agilent Technologies
matt_butler_at_agilent.com Received on Wed Jan 09 2002 - 18:11:46 CST