Re: Import of CSV File

From: george lewycky <gelewyc_at_nyct.com>
Date: 26 Mar 2002 11:25:10 -0800
Message-ID: <68aecc05.0203261125.e8502ac@posting.google.com>

"Markus Neumaier" <markus.neumaier_at_rl-ag.de> wrote in message news:<a7n8ag$m26jc$1_at_ID-113543.news.dfncis.de>...
> Hi,
>
> i want to import a CSV File into a table. Is that possible?
>
> Best Regards
>
> Markus

This is something I put together showing how I did it and the trials and tribulations I went through

hope it helps

george lewycky

IV. SQL LOADER sample and example

SQL*Loader – page 1

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://technet.oracle.com/doc/server.815/a67792/ch04.htm

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

NOTE: If using Excel save spreadsheet at “CSV” 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 – page 2

Creating and Loading an Oracle Table

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 “;”

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 – 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’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. ","
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      ","

Received on Tue Mar 26 2002 - 13:25:10 CST