Home » SQL & PL/SQL » SQL & PL/SQL » SQLLDR, HOW TO IMPORT MULTI-LINES DATA?
SQLLDR, HOW TO IMPORT MULTI-LINES DATA? [message #37055] Sun, 13 January 2002 15:05 Go to next message
YXN
Messages: 4
Registered: December 2001
Junior Member
I HAVE A TABLE WITH MANY ATTRIBUTES, the error is too many characters, how shall i do?
my coad is as below:


Load DATA
INFILE 'E:tempspo.csv'
INTO TABLE LMBE.CON(
ORF,t0_G,t0_G_B,t0_R,t0_R_B,t0_F,t05_G,t05_G_B,t05_R,t05_R_B,t05_F,t2_G,t2_G_B,t2_R,t2_R_B,t2_F,t5_G,t5_G_B,t5_R,t5_R_B,t5_F,t7_G,t7_G_B,t7_R,t7_R_B,t7_F,t9_G,t9_G_B,t9_R,t9_R_B,t9_F,t115_G,t115_G_B,t115_R,t115_R_B,t115_F,gal-ndt80_G,gal-ndt80_G_B,gal-ndt80_R,gal-ndt80_R_B,gal-ndt80_F,ndt80_Del_Ear_G,ndt80_Del_Ear_G_B,ndt80_Del_Ear_R,ndt80_Del_Ear_R_B,ndt80_Del_Ear_F,ndt80_Del_Mid_G,ndt80_Del_Mid_G_G_B,ndt80_Del_Mid_G_R,ndt80_Del_Mid_G_R_B,ndt80_Del_Mid_G_F,t0_Cor,t05_Cor,t2_Cor,t5_Cor,t7_Cor,t9_Cor,t115_Cor,ndt80_Del_Ear_Cor,ndt80_Del_Mid_Cor,t0_Cor2,t05_Reg,t2_Reg,t5_Reg,t7_Reg,t9_Reg,t115_Reg,ndt80_Del_Ear_Reg,ndt80_Del_Mid_Reg,t0_gr_Ra,t05_gr_Ra,t2_gr_Ra,t5_gr_Ra,t7_gr_Ra,t9_gr_Ra,t115_gr_Ra,ndt80_Del_Ear_gr_Ra,ndt80_Del_Mid_gr_Ra,C79,SGD_N,SGD_F,TC,ndt80_S,Failed_PCR,Blast_Homology,Overlaps_another_ORF)

FIEL DSTERMINATED BY',' OPTIONALLY ENCLOSED BY '"'

thanks
Re: SQLLDR, HOW TO IMPORT MULTI-LINES DATA? [message #37065 is a reply to message #37055] Mon, 14 January 2002 10:51 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
are you loading data for any field more than 255 char length?

if not,try this...

Load DATA
INFILE 'E:tempspo.csv'
INTO TABLE LMBE.CON
FIELDS fields terminated by "," optionally enclosed by '"'
(
ORF,t0_G,t0_G_B,t0_R,t0_R_B,t0_F,t05_G,t05_G_B,t05_R,t05_R_B,t05_F,t2_G,t2_G_B,t2_R,t2_R_B,t2_F,t5_G,t5_G_B,t5_R,t5_R_B,t5_F,t7_G,t7_G_B,t7_R,t7_R_B,t7_F,t9_G,t9_G_B,t9_R,t9_R_B,t9_F,t115_G,t115_G_B,t115_R,t115_R_B,t115_F,gal-ndt80_G,gal-ndt80_G_B,gal-ndt80_R,gal-ndt80_R_B,gal-ndt80_F,ndt80_Del_Ear_G,ndt80_Del_Ear_G_B,ndt80_Del_Ear_R,ndt80_Del_Ear_R_B,ndt80_Del_Ear_F,ndt80_Del_Mid_G,ndt80_Del_Mid_G_G_B,ndt80_Del_Mid_G_R,ndt80_Del_Mid_G_R_B,ndt80_Del_Mid_G_F,t0_Cor,t05_Cor,t2_Cor,t5_Cor,t7_Cor,t9_Cor,t115_Cor,ndt80_Del_Ear_Cor,ndt80_Del_Mid_Cor,t0_Cor2,t05_Reg,t2_Reg,t5_Reg,t7_Reg,t9_Reg,t115_Reg,ndt80_Del_Ear_Reg,ndt80_Del_Mid_Reg,t0_gr_Ra,t05_gr_Ra,t2_gr_Ra,t5_gr_Ra,t7_gr_Ra,t9_gr_Ra,t115_gr_Ra,ndt80_Del_Ear_gr_Ra,ndt80_Del_Mid_gr_Ra,C79,SGD_N,SGD_F,TC,ndt80_S,Failed_PCR,Blast_Homology,Overlaps_another_ORF)
Re: SQLLDR, HOW TO IMPORT MULTI-LINES DATA? [message #37067 is a reply to message #37055] Mon, 14 January 2002 18:55 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
in that case, you have to specify length of char fields in control file. sqlloader takes 255 by default for all char fields. if data of one record is in multiple lines, you should provide some continution character to identify continuation of physical record .

(see CONTINUEIF option documentation)

load data
infile some_data.txt
append
into table table_name
fields terminated by ',' optionally enclosed by '"'
(col1 ,
col2 char(4000) ,
col3 )
Previous Topic: %ROWTYPE question
Next Topic: Group additions with respect to number systems
Goto Forum:
  


Current Time: Thu Mar 28 08:59:17 CDT 2024