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

FYI: cross-posts just get people angry. They do not get you faster answers.

There is probably some way of solving the problem with your current system. But I can't think of a single 'good' reason not to get MS Access out of the loop. Buy a copy of Tom Kyte's book "Expert One-On-One Oracle" and look at how he easily and elegantly handled this with an all Oracle solution.

Daniel Morgan

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
Received on Thu Jan 10 2002 - 16:59:06 CST