5

I have a Data file that looks like this:

    1            2          3        4             5            6    
FirstName1 | LastName1 | 4224423 | Address1 | PhoneNumber1 | 1/1/1980
FirstName2 | LastName2 | 4008933 | Address1 | PhoneNumber1 | 1/1/1980
FirstName3 | LastName3 | 2344327 | Address1 | PhoneNumber1 | 1/1/1980
FirstName4 | LastName4 | 5998943 | Address1 | PhoneNumber1 | 1/1/1980
FirstName5 | LastName5 | 9854531 | Address1 | PhoneNumber1 | 1/1/1980

My DB has 2 Tables, one for PERSON and one for ADDRESS, so I need to store columns 1,2,3 and 6 in PERSON and column 4 and 5 in ADDRESS. All examples provided in the SQL Loader documentation address this case but only for fixed size columns, and my data file is pipe delimited (and spiting this into 2 different data files is not an option).

Do someone knows how to do this?

As always help will be deeply appreciated.

Donato Szilagyi
  • 4,279
  • 4
  • 36
  • 53
Chepech
  • 5,258
  • 4
  • 47
  • 70

3 Answers3

3

Another option may be to set up the file as an external table and then run inserts selecting the columns you want from the external table.

Craig
  • 5,740
  • 21
  • 30
  • Mmm, that's an interesting idea – Chepech Nov 06 '10 at 16:01
  • Other option is to run the file two times; First time you only grab what you need for the first table and ignore the rest with a FILLER statement. You do the same for the second table. – Chepech Feb 25 '11 at 15:33
1
options(skip=1)
load data
infile "csv file path"
insert into table person
fields terminated by ','
optionally enclosed by '"'
trialling nullcols(1,2,3,6)

insert into table address
fields terminated by ','
optionally enclosed by '"'
trialling nullcols(4,5)
animuson
  • 53,861
  • 28
  • 137
  • 147
venki
  • 11
  • 1
0

Even if SQLLoader doesn't support this (I'm not sure) nothing stops you from pre-processing it with say awk and then loading. For example:

cat 1.dat | awk -F '|' '{print $1 $2 $3 $6}' > person.dat
cat 1.dat | awk -F '|' '{print $4 $5}' > address.dat
maximdim
  • 8,041
  • 3
  • 33
  • 48