We help IT Professionals succeed at work.

Parsing CSV file

mishradba
mishradba asked
on
Medium Priority
242 Views
Last Modified: 2012-05-12
I have a csv file which has records in the format:
"000000106"," 1"
"001382548"," 7"
"001959169"," 1"
"001959649"," 7"
"001963682"," 8"
"001964227"," 7"
"001965621"," 7"
"001985581"," 7"
"001968444"," 1"
"001968456"," 7"
"001968468"," 8"
"001968829"," 7"

I created a external table to read this as
CREATE TABLE SNS_IMPORT_EXTERNAL
(
 EMPLOYEE_ID  varchar2(100),
 ELIGIBILITY_STATUS varchar(5)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER DEFAULT DIRECTORY "INTERFACES"
ACCESS PARAMETERS
( records delimited BY newline
badfile 'SNS_IMPORT_EXTernal.bad'
logfile 'SNS_import_external.log'
fields terminated BY ',' optionally enclosed by '"'
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION ( 'Eligsav.csv' )
)
REJECT LIMIT UNLIMITED;

When I give select employee_id from sns_import_external, it gives this output as
"000000106"," 1"
But I want first filed to go in employee_id and second after comma to go in elligibility_ status.
Please help
Comment
Watch Question

Sr. System Analyst
CERTIFIED EXPERT
Commented:
if you remove " from your csv file. does it work?

try this, put this into csv file and try again...

000000106,1
001382548,7
001959169,1
001959649,7
001963682,8
001964227,7
001965621,7
001985581,7
001968444,1
001968456,7
001968468,8
001968829,7
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I just ran exactly what you posted in 10.2.0.3 and I do not see the double quotes on the columns.

Author

Commented:
Yes, I removed The trailing " quotations and it did work.
Thanks for the solution

Explore More ContentExplore courses, solutions, and other research materials related to this topic.