Re: Import from text file
Date: 4 Jun 2003 21:24:52 -0700
Message-ID: <febbed51.0306042024.10a357c2_at_posting.google.com>
TurkBear <john.greco_at_dot.state.mn.us> wrote in message news:<6h8sdvgjnrhgnpghrk9hi4b89i3t7n8dm9_at_4ax.com>...
> You can use SqlLoader to load after exporting from Excel in delimited format...
>
> Check the docs on using SqlLoader...
>
> "Ned" <73613.3133_at_compuserve.com> wrote:
>
> >I am using SQL*Plus: Release 9.2.0.1.0 & Oracle8i Enterprise Edition Release
> >8.1.7.4.0. The client pc is running Windows NT 4.x. I am new to Oracle and
> >recently attended an introductory class in Oracle 8i. I want to recreate
> >the database tables I used in the class to do the practice excercises. I
> >wrote the script below to create one of the tables and load it with data. I
> >realized that I do not want to place a list of INSERT INTO statements in my
> >code and would rather import the data from a text file (or and MS Excel
> >file). Is it possible to read data from my pc drive in a PL/SQL program?
> >How would I do this?
> >
> >Thanks,
> >
> >Ned
> >
> >
> >ACCEPT User CHAR FOR A3 PROMPT 'Enter your initials please... '
> >
> >CREATE TABLE emp_&User
> > (empno NUMBER(4) NOT NULL,
> > ename VARCHAR2(10),
> > job VARCHAR2(9),
> > mgr NUMBER(2),
> > hiredate DATE,
> > sal NUMBER(7,2),
> > comm NUMBER(7,2),
> > deptno NUMBER(2) NOT NULL );
> >
> >INSERT INTO emp_&User(empno, ename, job, mgr, hiredate, sal, comm, deptno)
> > VALUES (7839,'KING','PRESIDENT', NULL, '17-NOV-81', 5000, NULL, 10);
> >
> >INSERT INTO emp_&User(empno, ename, job, mgr, hiredate, sal, comm, deptno)
> > VALUES (7698,'BLAKE','MANAGER', NULL, '01-MAY-81', 2850, NULL,30);
> >
>
>
>
> ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
> http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
> ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---
u can use sqlloader to load data in database
u can use
fixed record format
variable record format
fixed record format like
load data
infile '<path of file>
into table <table name>
(col1 position(01:05)
col2 position(06:10))
in variable format
LOAD DATA
INFILE '<file name>'
APPEND INTO TABLE <table name>
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (colno1,
colno2,
col_DATE DATE(8) "YYYYMMDD"
)
for more information see oracle documentation .oracle utilities Received on Thu Jun 05 2003 - 06:24:52 CEST