Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQL LOADER issue upon loading CSV records

TheProdigy101Jul 4 2014 — edited Jul 4 2014

Hi,

I have been loading a CSV records to an oracle table using SQL LOADER. Upon loading the CSV it doesn't continue on the other rows, it stops loading after the last row with comma on it on the last column. Here a sample of the CSV file.

MF3MBTLHANJ02,1/26/2013,0:15,0,0,0,,,

MF3MBTLHANJ02,1/26/2013,0:30,0,0,0,,,

MF3MBTLHANJ02,1/26/2013,0:45,0,0,0,,,

MF3MBTLHANJ02,1/26/2013,1:00,0,0,0,,,

MF3MBTLHANJ02,1/26/2013,1:15,0,0,0,,,

MF3MBTLHANJ02,1/26/2013,1:30,0,0,0,,,

MF3MBTLHANJ02,1/26/2013,1:45,0,0,0,,,

MF3MBTLHANJ02,1/26/2013,2:00,0,0,0,,,

MF3MBTLHANJ02,1/26/2013,2:15,0,0,0,,,

MF3MBTLHANJ02,1/26/2013,2:30,0,0,0,,,

MF3MBTLHANJ02,1/26/2013,2:45,0,0,0,,,

MF3MBTLHANJ02,1/26/2013,3:00,0,0,0,,,

MF3MBTLHANJ02,1/26/2013,3:15,0,0,0,,,

MF3MBTLHANJ02,1/26/2013,3:30,0,0,0,,,

MF3MBTLHANJ02,1/26/2013,3:45,0,0,0,,, ---- LAST RECORD LOADED

MF3MBTLHANJ02,1/26/2013,4:00,0,0,0

MF3MBTLHANJ02,1/26/2013,4:15,0,0,0

MF3MBTLHANJ02,1/26/2013,4:30,0,0,0

MF3MBTLHANJ02,1/26/2013,4:45,0,0,0

MF3MBTLHANJ02,1/26/2013,5:00,0,0,0

MF3MBTLHANJ02,1/26/2013,5:15,0,0,0

MF3MBTLHANJ02,1/26/2013,5:30,0,0,0

CONTROL FILE:

OPTIONS(SKIP=1)

LOAD DATA

INTO TABLE CRSS.TEST_MTR

APPEND

FIELDS TERMINATED BY ',' optionally enclosed by '"'

TRAILING NULLCOLS

(SEIN,

BDATE,

TIME_COL,

KWD "to_number(replace(:KWD,',',''))" ,

KWHD  "to_number(replace(:KWHD,',',''))",

KVARHD  "to_number(replace(:KVARHD,',',''))",

KWR "to_number(replace(:KWR,',',''))",

KWHR "to_number(replace(:KWHR,',',''))",

KVARHR  "to_number(replace(:KVARHR,chr(13)))",

SOURCE CONSTANT ":FILE")

TABLE:

SEIN      VARCHAR2(20 BYTE)                   NOT NULL,

  BDATE     VARCHAR2(10 BYTE)                   NOT NULL,

  TIME_COL  VARCHAR2(20 BYTE)                   NOT NULL,

  KWD       NUMBER(30,14),

  KWHD      NUMBER(30,14),

  KVARHD    NUMBER(30,14),

  KWR       NUMBER(30,14),

  KWHR      NUMBER(30,14),

  KVARHR    NUMBER(30,14),

  SOURCE    VARCHAR2(21 BYTE)

Comments

KarK

I tried the same, but all rows got loaded.

Are you facing any error ? If yes can you post the error message for the discarded rows

FreddieEssex

I've just tested out your ctl file and all the rows load fine....it skipped the first as expected and then loaded the rest:

Table TEST.TEST_MTR:


  21 Rows successfully loaded.


  0 Rows not loaded due to data errors.


  0 Rows not loaded because all WHEN clauses were failed.


  0 Rows not loaded because all fields were null.










Space allocated for bind array:                 148992 bytes(64 rows)


Read   buffer bytes: 1048576






Total logical records skipped:          1


Total logical records read:            21


Total logical records rejected:         0


Total logical records discarded:        0
TheProdigy101

I was able to resolve it since there are special characters in some of the columns. I included this chr(13) for the data type conversion and it continued.

1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 1 2014
Added on Jul 4 2014
#database-key-features, #export-import-sql-loader-external-tables, #sql
3 comments
1,670 views