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

Matt Butler wrote:

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

If your CRM Oracle database is running on a Unix system and you try to load a DOS type file that terminates lines with <CRLF>, Unix lines terminate with <LF>. You can convert <CRLF> to <LF> by using ftp utility, make sure to use ascii transfers that takes care of line terminators. If you use bin(any) transfer, it is transfered as is (raw)

/Svend Received on Fri Jan 11 2002 - 13:19:54 CST