Skip to Main Content
  • Questions
  • IMPORT -- how to load delimited text data.

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rajashekar.

Asked: June 19, 2000 - 3:21 am UTC

Last updated: October 24, 2016 - 12:43 am UTC

Version: 8.0.4

Viewed 10K+ times! This question is

You Asked

Hi tom

I have a data in the text file delimited by :

I want to import the same into a table can i do that by writing a stored proc. if so how do i do that.

Is there any way so that i can use and import the same into a table .

Awaiting for your reply
Regards
Raj



and Tom said...

SQLLDR is the most probable tool for doing this. If you look in $ORACLE_HOME/rbms/demo, you'll find a couple of examples. One of them might look like:


LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC)
BEGINDATA
12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"


If you alter the terminated by ',' into terminated by ':', it'll load a : delimited file. Please see the server utilities manual:
</code> http://docs.oracle.com/cd/A58617_01/server.804/a58244/toc.htm
for complete details on sqlldr.

If you wish to do this in PLSQL, it is easy enough.  Here is a sample routine that will load a delimited text file into a table.  You must configure UTL_FILE for this to work correctly (see 
http://docs.oracle.com/cd/A58617_01/server.804/a58241/ch8.htm#1939 <code>for information on setting that up).


drop table badlog;
create table badlog( errm varchar2(4000), data varchar2(4000) );

create or replace
function load_data( p_table in varchar2,
p_cnames in varchar2,
p_dir in varchar2,
p_filename in varchar2,
p_delimiter in varchar2 default '|' )
return number
is
l_input utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_buffer varchar2(4000);
l_lastLine varchar2(4000);
l_status integer;
l_colCnt number default 0;
l_cnt number default 0;
l_sep char(1) default NULL;
l_errmsg varchar2(4000);
begin
l_input := utl_file.fopen( p_dir, p_filename, 'r' );

l_buffer := 'insert into ' || p_table || ' values ( ';
l_colCnt := length(p_cnames)-
length(replace(p_cnames,',',''))+1;

for i in 1 .. l_colCnt
loop
l_buffer := l_buffer || l_sep || ':b'||i;
l_sep := ',';
end loop;
l_buffer := l_buffer || ')';

dbms_sql.parse( l_theCursor, l_buffer, dbms_sql.native );


loop
begin
utl_file.get_line( l_input, l_lastLine );
exception
when NO_DATA_FOUND then
exit;
end;
l_buffer := l_lastLine || p_delimiter;


for i in 1 .. l_colCnt
loop
dbms_sql.bind_variable( l_theCursor, ':b'||i,
substr( l_buffer, 1,
instr(l_buffer,p_delimiter)-1 ) ) ;
l_buffer := substr( l_buffer,
instr(l_buffer,p_delimiter)+1 );
end loop;

begin
l_status := dbms_sql.execute(l_theCursor);
l_cnt := l_cnt + 1;
exception
when others then
l_errmsg := sqlerrm;
insert into badlog ( errm, data )
values ( l_errmsg, l_lastLine );
end;
end loop;

dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_input );
commit;

return l_cnt;
end load_data;
/

And here is an example using the above:


ops$tkyte@8i> create table t1 ( x int, y int, z int );
Table created.

ops$tkyte@8i> host echo 1,2,3 > /tmp/t1.dat
ops$tkyte@8i> host echo 4,5,6 >> /tmp/t1.dat
ops$tkyte@8i> host echo 7,8,9 >> /tmp/t1.dat
ops$tkyte@8i> host echo 7,NotANumber,9 >> /tmp/t1.dat

ops$tkyte@8i> begin
2 dbms_output.put_line(
3 load_data( 'T1',
4 'x,y,z',
5 '/tmp',
6 't1.dat',
7 ',' ) || ' rows loaded' );
8 end;
9 /
3 rows loaded

PL/SQL procedure successfully completed.

ops$tkyte@8i> select * from badlog;

ERRM DATA
------------------- -------------------
ORA-01722: invalid number 7,NotANumber,9


ops$tkyte@8i> select * from t1;

X Y Z
---------- ---------- ----------
1 2 3
4 5 6
7 8 9



Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

please help me with this related question

Richard, December 11, 2001 - 12:46 pm UTC

I know it is against your principle to ask question through this channel but i can't catch you at the specified time since am not connected to net all times.
Please this the question:
Hello Tom,
My question is that I have 3 tables, 2 of which contain many records. I have to write a code(plsql) that will select all matching records and insert them into the 3rd table(C) .The real problem is that table C must contain records in either Excel or Access file format which is saved on a diskette. Thus apart from selecting records from table B to be inserted into table C, I should also find a way of reading or selecting matching records saved on the floppy diskette.
Can this be achievable in oracle7.3.2? Can you give me a simple code to do that?
NB. Table A(studnumber,subject,level)
Table B(studnumber,dept,year)
Table C(studnumber,subject,level,dept,year).
Thanks a lot.



Tom Kyte
December 11, 2001 - 3:03 pm UTC

well, putting the data into table c is trivial:

insert into c
select a.*, b.dept, b.year
from a, b
where a.studnumber = b.studnumber;

Geting the data OUT in a format for excel or access is easy as well. you can use a delimited file for both (see:

</code> http://asktom.oracle.com/~tkyte/flat/index.html

or for excel you can use the sylk format to get better control over the look of the data:

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:728625409049 <code>
...

Perfect

maxu, December 11, 2001 - 6:02 pm UTC

Tom, it's just owesome !!!

What about the dates?

Eduardo Campos, March 13, 2003 - 12:01 pm UTC

Tom your SP only work with int, but, how could it work with dates?

thanks.

Tom Kyte
March 14, 2003 - 5:34 pm UTC

did you try it?

any date will be converted using the default nls_date_format from a string. it works.

Alfredo Zamudio, November 30, 2004 - 2:05 pm UTC

Tom,

I couldn't upload a delimited text in just 1 from 12 tables here have the desc of the table
SQL> desc carga
 Name   Null? Type
 ------ ----- ----
 ORDEN        VARCHAR2(9)
 PRTNBR       VARCHAR2(16)
 OPER         VARCHAR2(4)
 STADAT       DATE
 COMDAT       DATE
 QTY          FLOAT(126)
 JOBDUE       DATE
 SEMANA       FLOAT(126)
 TIPO         VARCHAR2(1)
 ESTADO       FLOAT(126)

Here I have to lines from the delimited

MRP794490,01-811636-01,005,20041126,20041126,6,20041126,0,M,5
MRP837143,01-811636-01,005,20041130,20041130,24,20041130,1,M,5

And this is the error

ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 101
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at "LES.LOAD_DATA", line 18
ORA-06512: at line 4

Do you know whats wrong with this?

Thanks regards 

Tom Kyte
November 30, 2004 - 8:05 pm UTC

is line 18 the 'fopen'

if you just try to open the file ,does it work

is the file on the SERVER (has to be)
is the utl_file_dir init.ora set up?

Load_data function

faisal, April 05, 2005 - 6:30 pm UTC

how does this function treats data,does it do the same thing that the loader does,and what does the files that could be used in this function,and does it load date columns ?
I know that this could be a stupid quistion but how you call this is sqlplus but with DOS root c:\ f my file was t1.txt

Tom Kyte
April 05, 2005 - 7:01 pm UTC

you have the code? just read it, it is as simple as it looks.

basically just takes strings and loads them, nothing fancy at all

Excellant

Vijay, June 14, 2005 - 4:49 am UTC


Loading from multiple files

Reviewer, July 29, 2005 - 7:21 am UTC

Hi Tom,

Suppose I have 2 tables - table1 and table2. Now I create a third table which has columns from table1 and few columns from table2. I get the data from table1 and table2 in 2 separate text files. I have to use sql loader to select the data from both the files and insert into single row of table3. Is this possible using sql loader? Or do I have to load both the files into 2 separate tables and then run a query to insert into table3?
Please explain. Let me know if there are better options to achieve this. This will be used in our datawarehouse loading process.

Thanks in advance.

Tom Kyte
July 29, 2005 - 9:18 am UTC

use external tables, then you can join them (the files) and load table3 directly.

Reg: Loading from multiple files

A Reviewer, July 30, 2005 - 12:26 am UTC

I forgot to mention that it is oracle 8.1.7 So there is no external tables option for me. What is the other option?

Thanks.


Tom Kyte
July 30, 2005 - 8:54 am UTC

what you described then, load into stage, join.

A reader, November 07, 2007 - 3:51 am UTC

Tom, can i use this 'external table' method above to parse the date & data_fields for Oracle alert logs.

Such as:
Date -> Data field
Tom Kyte
November 07, 2007 - 5:56 pm UTC

http://asktom.oracle.com/pls/asktom/f?p=100:11:3800578428791239::::P11_QUESTION_ID:1352202934074

the data and data_fields for an alert log are in unpredictable places sometimes (until 11g when the alert log can be in XML)

Mohit, October 22, 2016 - 1:27 pm UTC

Hi Tom,

Can you please let me know if the file is tab delimited, then what needs to be passed. AFter reading your post i have tried out passing 'X''09''' and '\t' and ' ' . Its very urgent. A quick response from your side would be highly appreciated.
Connor McDonald
October 24, 2016 - 12:43 am UTC

See https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:533222350291

You can also open your text file with a binary editor to double check that the tab's are correctly being passed as ASCII 9.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library