OraFAQ Forum: SQL & PL/SQL » READ FROM A FILE WITH COMMA(,) as Delimiter

Home » SQL & PL/SQL » SQL & PL/SQL » READ FROM A FILE WITH COMMA(,) as Delimiter

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

READ FROM A FILE WITH COMMA(,) as Delimiter [message #562659]

Fri, 03 August 2012 03:57

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

Report message to a moderator

Re: READ FROM A FILE WITH COMMA(,) as Delimiter [message #562660 is a reply to message #562659]

Fri, 03 August 2012 03:59

Michel Cadot
Messages: 54236
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Use an external table (or sql*loader) for this.

Regards
Michel

Report message to a moderator

Re: READ FROM A FILE WITH COMMA(,) as Delimiter [message #562661 is a reply to message #562659]

Fri, 03 August 2012 04:01

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

Report message to a moderator

Re: READ FROM A FILE WITH COMMA(,) as Delimiter [message #562662 is a reply to message #562661]

Fri, 03 August 2012 04:27

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..

Report message to a moderator

Re: READ FROM A FILE WITH COMMA(,) as Delimiter [message #562664 is a reply to message #562659]

Fri, 03 August 2012 04:37

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

Report message to a moderator

Re: READ FROM A FILE WITH COMMA(,) as Delimiter [message #562666 is a reply to message #562664]

Fri, 03 August 2012 04:54

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...

Report message to a moderator

Re: READ FROM A FILE WITH COMMA(,) as Delimiter [message #562668 is a reply to message #562666]

Fri, 03 August 2012 05:10

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

Report message to a moderator

Re: READ FROM A FILE WITH COMMA(,) as Delimiter [message #562676 is a reply to message #562661]

Fri, 03 August 2012 06:13

Michel Cadot
Messages: 54236
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

Report message to a moderator

Re: READ FROM A FILE WITH COMMA(,) as Delimiter [message #562714 is a reply to message #562676]

Fri, 03 August 2012 12:39

Bill B
Messages: 989
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 24 18:14:37 CDT 2013

Total time taken to generate the page: 0.73521 seconds