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

Report message to a moderator

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)

Report message to a moderator

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 )

Report message to a moderator

Previous Topic: %ROWTYPE question
Next Topic: Group additions with respect to number systems
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Jun 10 09:19:49 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.