Skip to Main Content
  • Questions
  • reading a delimited file into sqlldr

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Lisa .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: September 23, 2003 - 7:54 am UTC

Version:

Viewed 1000+ times

You Asked

I am trying to read a tab delimited file in SQLloader
is this possible



and Tom said...



LOAD DATA
INFILE *
REPLACE
INTO TABLE DEPT
FIELDS TERMINATED BY X'9' 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



is an example. there are tabs (chr(9), hex number '9') separating the fields after the BEGINDATA


Rating

  (3 ratings)

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

Comments

error in loading of data

Deepak Gupta, September 22, 2003 - 7:56 am UTC

Dear Mr. Tom,

I am using following steps to load data through sqlldr
But I am getting following error

Record 1: Rejected - Error on table MEDI_TEMP1, column MEDI_OPEN.
ORA-01722: invalid number

I tried different options like
instead of "char" I put decimal external / integer external and
field terminated by '$' option
( only those record got inserted without decimal value )

control.file
+++++++++
load data
infile 'medi_new1.txt'
INSERT into table
medi_temp
(
emp POSITION(1:4) char,
medi_open POSITION(17:22) CHAR,
medi_amt POSITION(33:38) CHAR)


medi_new1.txt
+++++++++
C008 $ 32191.2 $ 29000
C030 $ 310 $ 5810
C031 $ 23172.3 $ 6260
C035 $ 1641 $ 6130
C040 $ 174.5 $ 3875
C049 $ 0 $ 8460
C051 $ 495.52 $ 4170
C062 $ 0 $ 11565
C065 $ 0 $ 11070

Thanks + Regards



Tom Kyte
September 22, 2003 - 8:22 am UTC

looks like you have tabs in there.

when I cut and paste it -- pos(17:22)

C008 $ 32191.2 $ 29000
123456789012345678901234567890
^^^^^^

doesn't line up. check your input data.


what I do to test -- would be to create a temp table "T" with the same number of columns but make all of the datatypes varchar2(4000). then load. then you can "see" what data is loaded into which columns and figure out where you went wrong.

How to load data from tab input fiel

Deepak Gupta, September 23, 2003 - 7:50 am UTC

Dear Mr. Tom,
        
     Hi,

You rightly said , there is tab in my data file, but how to handle it ?

I tried as per your  advice but data is not coming in any logical order in the table columns 
++++++++++++++++++++
SQL> desc t3
 Name                            Null?    Type
 ------------------------------- -------- ----
 EMP                                      VARCHAR2(5)
 MEDI_OPEN                                VARCHAR2(4000)
 MEDI_AMT                                 VARCHAR2(4000)

SQL> set ARRAYSIZE 1
SQL> select * from t3;

EMP
-----
MEDI_OPEN
---------------------------------------------
MEDI_AMT
---------------------------------------------
C008
        32191.2
        $       29000

C030
        310     $       5
810

C031
        23172.3
        $       6260

C035
        1641    $
6130

++++++++++



Thanks + Regards
 

Tom Kyte
September 23, 2003 - 7:54 am UTC

if you use delimited by whitespace instead of POS()

then:

C008 $ 32191.2 $ 29000

will map to 5 fields -- you can "filler" the two dollar signs out

Tab delimited - terminated by X'9'

Paul, May 06, 2005 - 6:15 am UTC

Tom - nobody thanked you for your original answer on this thread, so I'm doing that now. This was exactly what I needed today.