Skip to Main Content

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!

External table for comma delimmited file

GVRFeb 9 2010 — edited Feb 9 2010
Im trying to create a externla table for comma delimited file
but it also has a scenario where Fields can have a Comma enclosed in "" for example

sample Record
1,one,first
2,two,second
3,three,third
4,four,"4,fourth"
Sample table
create table ext_table_csv (
  i   Number,
  n   Varchar2(20),
  m   Varchar2(20)
)
organization external (
  type              oracle_loader
  default directory ext_dir
  access parameters (
    records delimited  by newline
    fields  terminated by ','
    missing field values are null
  )
  location ('file.csv')
)
reject limit unlimited;
So when i query this Externla table 4th record will be read as

O/p
i           n 	                m
4         four	"4
{code}
Above Ext table is just an example, Im adding bad file and log file in real table.

Please help me if there is a way this can be resolved with external table.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
This post has been answered by Lakmal Rajapakse on Feb 9 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 9 2010
Added on Feb 9 2010
1 comment
2,222 views