mishradba
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
"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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I just ran exactly what you posted in 10.2.0.3 and I do not see the double quotes on the columns.
ASKER
Yes, I removed The trailing " quotations and it did work.
Thanks for the solution
Thanks for the solution