Home » SQL & PL/SQL » SQL & PL/SQL » READ FROM A FILE WITH COMMA(,) as Delimiter
READ FROM A FILE WITH COMMA(,) as Delimiter [message #562659] Fri, 03 August 2012 03:57 Go to next message
praveen240881
Messages: 36
Registered: February 2012
Member
Hello Experts

Need you help here. I have a file where my string looks like below:

2420362,660064100019390040,476,12,Auth_code,01:48:12,2012-07-31,00,true,DECLINED * =,169.50,M,258147-0_7,false,,null

Here i will have to read only the highlighted fields. There is a possibility that some field may be with no value at all. Comma (,) is my delimiter.

I know that i will have to use SUBSTR & INSTR combination, but couldnt get it right.

Can you please let me know how can i achieve this?

Regards
Praveen
Re: READ FROM A FILE WITH COMMA(,) as Delimiter [message #562660 is a reply to message #562659] Fri, 03 August 2012 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 59496
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use an external table (or sql*loader) for this.

Regards
Michel
Re: READ FROM A FILE WITH COMMA(,) as Delimiter [message #562661 is a reply to message #562659] Fri, 03 August 2012 04:01 Go to previous messageGo to next message
praveen240881
Messages: 36
Registered: February 2012
Member
The functionality/logic is in such a way that i cant use either. just with substr and instr
Re: READ FROM A FILE WITH COMMA(,) as Delimiter [message #562662 is a reply to message #562661] Fri, 03 August 2012 04:27 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

First we need to load the data into table then we will extract the records..
Re: READ FROM A FILE WITH COMMA(,) as Delimiter [message #562664 is a reply to message #562659] Fri, 03 August 2012 04:37 Go to previous messageGo to next message
praveen240881
Messages: 36
Registered: February 2012
Member
Sorry. the problem here is i need to first read the highlighted values, store them in a varaiable and then do some calcualtions and then finally insert them onto the table
Re: READ FROM A FILE WITH COMMA(,) as Delimiter [message #562666 is a reply to message #562664] Fri, 03 August 2012 04:54 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Understand your problem...in this case we have to load the data into external table.. then only we can do..
Can you check external directory exists in your db.
or try to load the data USING sqlloader in the some temparory table then do rest of things what you want...
Re: READ FROM A FILE WITH COMMA(,) as Delimiter [message #562668 is a reply to message #562666] Fri, 03 August 2012 05:10 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Try like...
LOAD DATA
INFILE *
BADFILE 'd:/mk.BAD'
DISCARDFILE 'd:/mk.DSC'
INSERT INTO TABLE test
Fields terminated by ","
(
  field1,
  field2,
  field3,
  field4,
  field5
)
BEGINDATA
2420362,660064100019390040,476,12,Auth_code


Then run the below command in DOS prompt
c:\> sqlldr control=mk.ctl userid=username/passwd 

Re: READ FROM A FILE WITH COMMA(,) as Delimiter [message #562676 is a reply to message #562661] Fri, 03 August 2012 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59496
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
praveen240881 wrote on Fri, 03 August 2012 11:01
The functionality/logic is in such a way that i cant use either. just with substr and instr


You are wrong, you can use both.

Regards
Michel
Re: READ FROM A FILE WITH COMMA(,) as Delimiter [message #562714 is a reply to message #562676] Fri, 03 August 2012 12:39 Go to previous message
Bill B
Messages: 1124
Registered: December 2004
Senior Member
It's not that hard, simply use the filler command for example set up your control file like this


LOAD DATA
INFILE 'myfile'
INSERT
INTO TABLE WORK
FIELDS TERMINATED BY ","
(field1,
JUNK1 FILLER,
field2,
JUNK2 FILLER,
field3,
JUNK3 FILLER,
JUNK4 FILLER,
FIELD4,
JUNK5 FILLER
....
)

[Updated on: Fri, 03 August 2012 12:42]

Report message to a moderator

Previous Topic: help with query
Next Topic: help me to write a query
Goto Forum:
  


Current Time: Thu Oct 30 08:11:15 CDT 2014

Total time taken to generate the page: 0.07093 seconds