Home » RDBMS Server » Server Utilities » SQL LOADER issue upon loading CSV records (oracle database 10gR2)
SQL LOADER issue upon loading CSV records [message #617899] Fri, 04 July 2014 05:09 Go to next message
Prodigy101
Messages: 1
Registered: July 2014
Junior Member
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)
Re: SQL LOADER issue upon loading CSV records [message #617924 is a reply to message #617899] Fri, 04 July 2014 08:44 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is written in the log file?
Re: SQL LOADER issue upon loading CSV records [message #617925 is a reply to message #617924] Fri, 04 July 2014 08:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/
Re: SQL LOADER issue upon loading CSV records [message #617942 is a reply to message #617899] Fri, 04 July 2014 11:44 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I see that you found the problem yourself and posted it on the OTN forum:

https://community.oracle.com/thread/3580348
Previous Topic: username/password in exp/imp
Next Topic: ORA_06512
Goto Forum:
  


Current Time: Thu Mar 28 18:50:06 CDT 2024