SQLLDR reusing column records

[Jim]

I have a file that contains several records where each field is
delimited by commas:

'field1','field2','field3','field4',

ie:
'aaa','b','c','d',
'aaaaaaaa',bbb','','ffff',

I would like to use sqlldr to load the file into a table placing each
record's field in the following fashion:

LOAD DATA
APPEND
INTO TABLE mytable
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
column1 field1
column2 field2
column3 field3
column4 field2
column5 field2
column6 field3
)

Thank you

[TurkBear]

shija03@hotmail.com (Jim) wrote:
I have a file that contains several records where each field isdelimited by commas: 'field1','field2','field3','field4',ie: 'aaa','b','c','d', 'aaaaaaaa',bbb','','ffff',I would like to use sqlldr to load the file into a table placing eachrecord's field in the following fashion:LOAD DATAAPPENDINTO TABLE mytableFIELDS TERMINATED BY ","TRAILING NULLCOLS( column1 field1 column2 field2 column3 field3 column4 field2 column5 field2 column6 field3)Thank you
You can try ( but I do not think you really want to do this) :

LOAD DATA
APPEND
INTO TABLE mytable
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
field1,
field2,
field3,
field2,
field2,
field3, etc
)
Basically you name the field into which each value in each row of the delimited file should go .
Now, as to why not to do this:
Each subsequent reference to field2 after the first one , for instance, will replace the data previously loaded with the new
value as long as the same row of input data is being read and this method may not even work - I've never tried it..

If what you really want to do is to split a single line of input data into more than one record, that requires a different
approach and may not be possible..

[Daniel Morgan]

Turkbear wrote:
shija03@hotmail.com (Jim) wrote:I have a file that contains several records where each field isdelimited by commas: 'field1','field2','field3','field4',ie: 'aaa','b','c','d', 'aaaaaaaa',bbb','','ffff',I would like to use sqlldr to load the file into a table placing eachrecord's field in the following fashion:LOAD DATAAPPENDINTO TABLE mytableFIELDS TERMINATED BY ","TRAILING NULLCOLS( column1 field1 column2 field2 column3 field3 column4 field2 column5 field2 column6 field3)Thank youYou can try ( but I do not think you really want to do this) :LOAD DATAAPPENDINTO TABLE mytableFIELDS TERMINATED BY ","TRAILING NULLCOLS( field1, field2, field3, field2, field2, field3, etc)Basically you name the field into which each value in each row of the delimited file should go .Now, as to why not to do this:Each subsequent reference to field2 after the first one , for instance, will replace the data previously loaded with the newvalue as long as the same row of input data is being read and this method may not even work - I've never tried it..If what you really want to do is to split a single line of input data into more than one record, that requires a differentapproach and may not be possible..
Turkbear's method should work. If not ... load to a staging table and
then use a simple INSERT statement to move the data.

--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)