Re: Import/Export
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’s SQL code can segregate each field
Ø Download the file to the PC Ø Customized SQL code has been written and tested using Oracle’sSQL*Loader which has been tested to upload the data to Oracle’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 fieldsADDRESS1,ADDRESS2,ADDRESS3,ADDRESS4,
CITYCOUNTRY,STATE,ZIP,PHONE,CONTACT,DATEADDED )
sample of the data file
NOTE: the first field DOESN’T have “,” 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 “,” - 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 “Text Write Program” to delimit
“,” 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 “SQLLDR.EXE” you might have to copy it from your server machine and onto your PC where you are loading the data from. Resides in the “bin” directory. Do a file search in Windows for that exact filename.
Enter “sqlldr.exe | more “ 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 “CSV” 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