Re: Import/Export

From: george lewycky <gelewyc_at_nyct.com>
Date: 2 Oct 2002 07:20:01 -0700
Message-ID: <68aecc05.0210020620.2c708be3_at_posting.google.com>


I think SQL*Loader (sqlldr.exe) is much easier as long as u make a delimited formatted file.

good luck

george


Here is a sample I wrote up:

Procedure for Porting the Customized Customer spreadsheet into Oracle Ø Save the Excel spreadsheet as a .CSV file to delimit the file so that Oracle&#8217;s SQL code can segregate each field

Ø	Download the file to the PC
Ø	Customized SQL code has been written and tested using Oracle&#8217;s
SQL*Loader which has been tested to upload the data to Oracle&#8217;s table: RA_CUSTOMERS_INTERFACE_ALL
In a DOS Window run the dos program SQLLDR.EXE

> sqlldr apps/apps_at_vis ß all one word!!!

Control = loadtbag.ctl < enter a filename containing the code
>

Loadtbag.ctl is shown below:

load data
infile 'c:\MY SQL\oracust.txt' ß flat file of the DATA !!!!

replace					ß replace or append 
into table tbagcust			ß Oracle table created above
fields terminated by '","'       ß delimiter
(AUTH,CUST, NAME,                ß all fields
ADDRESS1,ADDRESS2,ADDRESS3,ADDRESS4,
CITYCOUNTRY,STATE,ZIP,PHONE,CONTACT,DATEADDED )

sample of the data file
NOTE: the first field DOESN&#8217;T have &#8220;,&#8221; preceding it, only succeeding it !!!!!!
This is due to fields terminated by '","' which is the delimiter separating the fields just like we do for spreadsheets

----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8

********************************* Top of Data
**********************************
1","0066","ABC BUS MANUFACTURING    ","1506 30TH STREET        
","NORTHWEST
1","0068","ABC RAIL PRODUCTS CORP.  ","11TH & WASHINGTON STREETS","
1","0070","ABCO REFRIGERATION SUPPLY","49-70 31ST STREET        ","
1","0090","ABEE PRINTING CORP.      ","305-1 KNICKERBOCKER AVE. ","

If you use append data must already be on the table

Other options were explored for loading this data and SQL*Loader was found to be the most effective for the amount of data being processed.

Useful Excel Macro to delimit &#8220;,&#8221; - note Excel alone wont allow such a delimiter

TEXT WRITE PROGRAM (excel macro)
http://tushar-mehta.com/excel/software/text_write_program/index.html has a real useful Macro &#8220;Text Write Program&#8221; to delimit &#8220;,&#8221; for a text file to be loaded into oracle using SQL, etc Excel will only do commas !!!

SQL LOADER sample and example

Find the DOS program &#8220;SQLLDR.EXE&#8221; you might have to copy it from your server machine and onto your PC where you are loading the data from. Resides in the &#8220;bin&#8221; directory. Do a file search in Windows for that exact filename.

Enter &#8220;sqlldr.exe | more &#8220; to see the commands

http://ugweb.cs.ualberta.ca/~c391/manual/chapt5.html VERY GOOD!!!!

http://utenti.lycos.it/yanorel4/1/ch25.htm Que: Using oracle 8Chapter 25
Using SQL*Loader and Export/Import

http://www.smckearney.com/oracle/sqlloader.php Oracle Resources Using SQL-Loader to load data into Oracle http://kachina.kennesaw.edu/~agalewsk/SQLloader.html

http://technet.oracle.com/doc/server.815/a67792/ch04.htm

http://www.doag.org/mirror/frank/faqloadr.htm

http://misdev.fnal.gov/oracledoc/v8.1.7/DOC/server.817/a76955/ch03.htm

http://gis.stpaul.gov/docs/oracle/server803/A54652_01/ch08.htm

http://www.cs.uvm.edu/oracledoc/server.815/a67792/ch03.htm

http://people.cs.uchicago.edu/~matei/TA/CS235/or-load.html Using the Oracle Bulk Loader

http://ugweb.cs.ualberta.ca/~c391/labs/Sqlplus_Sqlloader.html GOOD EXAMPLE! http://wpi.wpi.edu/server.815/a67792/ch04.htm CASE STUDIES

http://www.rumken.com/ioug2000/Paper421.htm Replacing 3GL Data Loading with SQL*Loader and Database Triggers

http://ftp.ora.com/orsqlloader/ Oracle SQL*Loader: The Definitive Guide Examples

NOTE: If using Excel save spreadsheet at &#8220;CSV&#8221; to create comma delimited file

SQL*LOADER ERROR LISTING:
        http://storacle.princeton.edu:9001/oracle8-doc/server.805/a58312/newch73.htm http://info-it.umsystem.edu/oradocs/doc/server/doc/MSG73/ch7.htm

http://wpi.wpi.edu/server.815/a67792/ch07.htm LOG FILE Received on Wed Oct 02 2002 - 16:20:01 CEST

Original text of this message