Re: SQLOADER help

From: george lewycky <gelewyc_at_nyct.com>
Date: 15 Apr 2002 06:35:46 -0700
Message-ID: <68aecc05.0204150535.e355690@posting.google.com>

"mgmombo" <mgmombo_at_I-55.com> wrote in message news:<a98jf1$ojk$1_at_news.datasync.com>...
> Being new to Oracle, I am unfamiliar with SQLoader.exe and the procedure to
> create the needed control file and the other files need.
> I have created a table and would like to import tab delimited text data. I
> am using Oracle 8 enterprise edition.
> Thanks in advance

I feel your pain and I put this together Hope it helps you some

George R Lewycky
grlewycky_at_yahoo.com
http://georgenet.net

http://groups.yahoo.com/group/OracleSharedInfo/

IV. SQL LOADER sample and example

SQL*Loader &#8211; page 1

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://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

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

Example of how to execute from dos:

G:\ORAWinNT\bin\SQLLDR73.EXE USERID=userename/passw_at_d... CONTROL=G:\test\filename.CTL LOG=G:\test\filename.LOG DATA=G:\test\filename.lst
BAD=G:\test\filename.bad DISCARD=G:\test\filename.dsc

userid -- ORACLE username/password
control -- Control file name

log -- Log file name 
bad -- Bad file name 
data -- Data file name 

discard -- Discard file name

The .ctl file is used to load the data and describes your datafile.  

SQL*Loader &#8211; page 2

Creating and Loading an Oracle Table

1. Creating a Table (database)

1a. In SQLPLUS do the following:

>SQL create table tbagcust

( auth char(1) not null,
cust char(4) not null,

name     char(25) not null,
address1 char(25),
address2 char(25),
address3 char(25),
address4 char(25),

citycountry char(18),

state    char(2),
zip      char(5),
phone    char(10),
contact  char(25),

dateadded char(6) )
; ß important to include a &#8220;;&#8221;

2. In SQL*PLUS verify the fields, structure, etc

SQL> desc tbagcust

Name                                      Null?    Type

AUTH                                      NOT NULL CHAR(1)
CUST                                      NOT NULL CHAR(4)
NAME                                      NOT NULL CHAR(25)
ADDRESS1                                           CHAR(25)
ADDRESS2                                           CHAR(25)
ADDRESS3                                           CHAR(25)
ADDRESS4                                           CHAR(25)
CITYCOUNTRY                                        CHAR(18)
STATE                                              CHAR(2)
ZIP                                                CHAR(5)

SQL*Loader &#8211; page 3

PHONE                                              CHAR(10)
CONTACT                                            CHAR(25)
DATEADDED                                          CHAR(6)

3.	In a DOS Window run the dos program  SQLLDR.EXE
a.	Note you might need to copy the program from the server




>  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. ","
1","0095","THOMAS ABRAHAM           ","80-43 249TH STREET       ","
1","0106","ABSOLUTE LOGIC           ","1189 EAST 56TH STREET    ","
1","0110","ACADEMY BUS TOURS, INC.  ","1515 JEFFERSON STREET    ","
1","0140","ACROSS THE RIVER, INC.   ","49 BLEECKER STREET      
","SUITE 606
1","0141","ADORAMA, INC.            ","42 WEST 18TH STREET      ","
 

> How do I specify the datatype of a field that is 9(5)v99 (hence the data
> looks like 99999 in the source file)? I am guessing for right now that I
> would say
> DECIMAL EXTERNAL(5) (but, how do I tell it the precision - we need 2?).
> Or, can I do INTEGER EXTERNAL(5) and use some function (at load time) to
> divide it by 100?

Here is how to handle it:
fieldname position(1:5) decimal external ":fieldname/100"

Using spreadsheet files:
· Save the spreadsheet &#8220;CSV&#8221; to then import the file using SQL Loader or UTL_FILE
· SQL*XL &#8211; add-in for Excel oraxcel.com Received on Mon Apr 15 2002 - 08:35:46 CDT