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

On 9 Jan 2002 16:11:46 -0800, matt_butler_at_agilent.com (Matt Butler) wrote:

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

I have pretty much the same problem. I want to export data in a "legacy" Access system via "flat" files, one physical record per logical line. Sql loader chokes on the output because of the embedded CR LF.

I asked the comp.database.ms-access group for advice. There were a number of approaches, but the concesus seems to be to create a function that strips CR LF (char(10) and char(13) ) from the text fields, then export a "view" of the original table where all text fields of the original table show up as replace( originial_col, "crlf", null) where "crlf" is expressed in VB-ish mode chr$(10)&chr$(13) and null is "".

I am hampered in implementing all the suggestions and of fully understanding all the hints and tricks because I'm basically an utter newcomer to Access. I am not familiar with the details of how one asks Access to Export tables and views, how much control one has over the delimiting characters (I'm assured that there's a way to control the new record character normally CRLF , the field delimiter usually a comma, and the optional enclosing character, normally a double quote) I'm not too concerned about that right now, because my feeling is that if I know what "standard" thing I want done any manual, tutorial, help file or just plain poking around will get me there. I need help on the big picture, the standard terminology, and basically in "breaking the ice" with what for me is a brand new product.

I tried to email you a copy of some of the choicer responses that I got from ms-access group and others, but the email bounced.

You could of course go there and try to find the thread. Try searching on "flat file".

If you get anywhere with your problem, I would appreciate it if you could share the info with me at
Lee_at_JamToday.com

Thanx in advance.

>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 Sun Jan 13 2002 - 16:56:49 CST