0

I have raw data like this:

2002-3,X52.9418.001 , Abraham, Kennedy James, 111111111, 2125551234,ak123n@aol.com, 123 East 14th Street,, New York, NY,
2002,X52.9418.001 , Richard, Roberts, 111111112, 2125554321,rr345@aol.com, 123 East 14th Street, Apt.6 , New York, NY, 10010, United States, English
2002-4,X52.9418.001 , Katherine Ray Baird , 111111113, (212) 555 3456,kbilu@aol.com, 123 East 14th Street,, New York, New York, 10010-, , 
...

I need to load the raw data into the Oracle database using Sql Loader. I create this control file:

load data
infile '/home/l/liuh/rawdata.txt'
badfile '/home/l/liuh/badfile.bad'
discardfile '/home/l/liuh/discardfile.discard'
into table students

trailing nullcols (
    Year                INTEGER EXTERNAL terminated by "-",
    Semester            terminated by ",",
    department          terminated by ".",
    course_number       terminated by ".",
    session_number      terminated by ",",
    first_name          CHAR(10)  terminated by " ",
    Middle_name         CHAR(15)  terminated by ",",
    last_name           CHAR(15)  terminated by ",",
    id                  INTEGER EXTERNAL  terminated by ",",
    area_code           terminated by " ",
    phone_number        terminated by ",",
    email_address       CHAR terminated by ",",
    Street1             terminated by "," ,
    street2             terminated by "," ,
    city                terminated by "," ,
    State_prov          terminated by "," ,
    postal_code         terminated by "," ,
    country             terminated by "," ,
    language            terminated by "," 
)

But I got nothing into the database. Can somebody help me with the control file? I fount it difficult to control the data. Thanks.

fuermosi777
  • 153
  • 3
  • 9
  • anything in the log file? my guess is sqlldr would take the string '1)2002-3' as value for the Year column, and throw some "Not a number exception" – user1645975 Apr 04 '14 at 15:24
  • also what terminates Year column value in second data row? There is no "-" after the year. – user1645975 Apr 04 '14 at 15:40
  • Thank you user1645975. Sorry the 1) 2) 3) are just bullets I added. I have revised that. Yes there is no "-" in the second record, actually that is the problem...the data is so unformatted. I believe I was supposed to insert a null in the semester column in the table – fuermosi777 Apr 05 '14 at 04:20
  • try this: year terminated by ',' "substr(:year,1,4)", semester "substr(:year,6)", department terminated by ".", ... – user1645975 Apr 07 '14 at 16:59

0 Answers0