Problems with MS Access text export -> sqlldr -> Oracle 
Author Message
 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.

I'd be open to any sort of perl or VB pre-processing if some obscure
fix is not known.

Thanks

Matt Butler
Agilent Technologies



Mon, 28 Jun 2004 08:09:11 GMT
 Problems with MS Access text export -> sqlldr -> Oracle

Don't know whether you want to go down this route but you may be able to use
SQL*XL for part of the solution.

SQL*XL is an addin for Excel that can be accessed from VBA code. It can
directly insert data into the database (multiple rows at once) and it can
retrieve data from the database directly into a spreadsheet. All of this can
be done interactively or without user intervention by means of macros.

SQL*XL can be found at http://www.oraxcel.com

Regards, Gerrit-Jan Linker



Thu, 12 Aug 2004 03:39:54 GMT
 
 [ 2 post ] 

 Relevant Pages 

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

2. Oracle->ODBC->MS-ACCESS problem, need help

3. Delphi>>>>>>>

4. US-NY: ORACLE DBA >>>>>URGENT

5. US-NY: ORACLE DBA >>>>>URGENT

6. US-NY: ORACLE DBA >>>>>URGENT

7. US-NY: ORACLE DBA >>>>>URGENT

8. US-NY: ORACLE DBA >>>>>URGENT

9. US-NY: ORACLE DBA >>>>>URGENT

10. US-NY: ORACLE DBA >>>>>URGENT

11. US-NY: ORACLE DBA >>>>>URGENT

12. US-NY: ORACLE DBA >>>>>URGENT


 
Powered by phpBB® Forum Software