Link to home
Start Free TrialLog in
Avatar of mishradba
mishradbaFlag for United States of America

asked on

Parsing CSV file

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
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I just ran exactly what you posted in 10.2.0.3 and I do not see the double quotes on the columns.
Avatar of mishradba

ASKER

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