Home » SQL & PL/SQL » SQL & PL/SQL » loading comma seperated values through sql loader
loading comma seperated values through sql loader [message #222125] Thu, 01 March 2007 23:55 Go to next message
p_hariprasad
Messages: 4
Registered: December 2005
Location: Mumbai
Junior Member
Hi,
I want to load data file which has columns as comma seperated. The problem is that the values in specific column are comma seperated . How can i load this data.


SEQUENCE_ID,TAX_ID,AGENT_NAME,AGENT_CODE

1712165,630921451,William W Conwell,0060240836,0066014956,G3239

The agent code has several values as comma seperated, so currently ony first 0060240836 is getting loaded. i know one way is to change the data file with tab seperated etc. Can i do in any other way.
Re: loading comma seperated values through sql loader [message #222134 is a reply to message #222125] Fri, 02 March 2007 00:15 Go to previous messageGo to next message
friendarora
Messages: 24
Registered: February 2007
Junior Member

have you tried

\, for the text having comma
Re: loading comma seperated values through sql loader [message #222136 is a reply to message #222134] Fri, 02 March 2007 00:22 Go to previous messageGo to next message
p_hariprasad
Messages: 4
Registered: December 2005
Location: Mumbai
Junior Member
No but this will be same as doing the extract as tab seperated or ~ seperated
Re: loading comma seperated values through sql loader [message #222207 is a reply to message #222136] Fri, 02 March 2007 05:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
oracle@mutation#cat somectl.ctl
LOAD DATA
infile *
truncate into TABLE sometable FIELDS TERMINATED BY "," trailing nullcols
(
SEQUENCE_ID,
TAX_ID,
AGENT_NAME,
AGENT_CODE ":AGENT_CODE ||','||:field5||','||:field6",
field5 boundfiller,
field6 boundfiller
)
begindata
1712165,630921451,William W Conwell,0060240836,0066014956,G3239
oracle@mutation#
oracle@mutation#sqlldr userid=scott/tiger control=somectl.ctl

SQL*Loader: Release 9.2.0.7.0 - Production on Fri Mar 2 06:12:01 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 1
oracle@mutation#
oracle@mutation#query mutation scott.sometable

SEQUENCE_ID     TAX_ID AGENT_NAME
----------- ---------- --------------------
AGENT_CODE
------------------------------------------------------------
    1712165  630921451 William W Conwell
0060240836,0066014956,G3239

Re: loading comma seperated values through sql loader [message #222224 is a reply to message #222207] Fri, 02 March 2007 06:25 Go to previous messageGo to next message
p_hariprasad
Messages: 4
Registered: December 2005
Location: Mumbai
Junior Member
Thanks Mahesh, but here the column agent_code can have many "," separated values, dynamic values.
Re: loading comma seperated values through sql loader [message #222233 is a reply to message #222224] Fri, 02 March 2007 07:20 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Just because i am lazy, even without exploring the possiblities for a pure sqlldr solution, i would go for a scripting solution/fixing the datafile. Smile
The script depends on your OS.
Look into continueif clause in sqlldr (which is actually meant for multiline feeds).
Previous Topic: Understanding of the database
Next Topic: Append mode with REF CURSOR?!
Goto Forum:
  


Current Time: Thu Dec 08 00:14:28 CST 2016

Total time taken to generate the page: 0.10893 seconds