oracle-databaseexternal-tablescsv-import
Import a CSV file into an Oracle External Table
I have seen various similar questions to this but none of the solutions seem to work for me.
I have been given a CSV file produced on a mainframe that I need to load up into Oracle. I decided to try and map it in an Oracle external table and then use this to get it inserted into Oracle.
This is my CSV:
CONTRACT_NUMBER,PRODUCTCODE,TRANSACTION_NUMBER,EFFECTIVE_DATE,AMENDMENT,TERM,ACTIVE,AGENT_NUMBER,PREMIUM,ICRATE,RCRATE,IC_ALLOW,RC_ALLOW,SPRATE,TRANSACTION_CODE,TRANSACTION_DATE,AGENT_CATEGORY,AGENT_SALES_CODE,FREQ,TOT_PREMTD,REFERENCE,ALTERNATIVE_COMMISSION_METHOD,PAXUS_REF_ID
PAXUSCT1,MAA,1,07/10/2017,NB,12,Y,2905,6000,,,1,1,,T642,,,,,6000,,,
PAXUSCT1,MAA,2,07/05/2018,INC,11,Y,2905,2400,90,3,1,1,,,,,,,8400,,,
PAXUSCT2,MAA,1,01/06/2018,NB,12,Y,T1000,540,,,1,1,,,,,,,540,,,
PAXUSCT3,MAA,1,05/06/2018,NB,12,Y,T1000,1200,,,1,1,,,,,,,1200,,,
I created this definition, and many other variations of this but I keep getting errors:
create table LD_CMS_BASIS_MIGRATION
(
contract_number VARCHAR2(8),
productcode VARCHAR2(3),
transaction_number NUMBER,
effective_date DATE,
amendment VARCHAR2(3),
term NUMBER,
active VARCHAR2(1),
agent_number VARCHAR2(5),
premium NUMBER,
icrate NUMBER,
rcrate NUMBER,
ic_allow NUMBER,
rc_allow NUMBER,
sprate NUMBER,
transaction_code VARCHAR2(4),
transaction_date DATE,
agent_category VARCHAR2(4),
agent_sales_code VARCHAR2(4),
freq VARCHAR2(1),
tot_premtd NUMBER,
reference VARCHAR2(40),
alternative_commission_method VARCHAR2(40),
paxus_ref_id VARCHAR2(8)
)
organization external
(
type ORACLE_LOADER
default directory MIGRATIONS
access parameters
(
records field names all files
fields CSV without embedded record terminators
)
location (MIGRATIONS:'CMS_BASIS_MIG.csv')
)
reject limit UNLIMITED;
When I try to read from it I get this error:
This is what is in the log file on the server:
KUP-05004: Warning: Intra source concurrency disabled because parallel select was not requested.
Field Definitions for table LD_CMS_BASIS_MIGRATION
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
CONTRACT_NUMBER CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
PRODUCTCODE CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
TRANSACTION_NUMBER CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
EFFECTIVE_DATE CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
AMENDMENT CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
TERM CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
ACTIVE CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
AGENT_NUMBER CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
PREMIUM CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
ICRATE CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
RCRATE CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
IC_ALLOW CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
RC_ALLOW CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
SPRATE CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
TRANSACTION_CODE CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
TRANSACTION_DATE CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
AGENT_CATEGORY CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
AGENT_SALES_CODE CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
FREQ CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
TOT_PREMTD CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
REFERENCE CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
ALTERNATIVE_COMMISSION_METHOD CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
PAXUS_REF_ID CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
KUP-04117: Field name PAXUS_REF_ID
was not found in the access parameter field list or table.
KUP-04093: error processing the FIELD NAMES record in data file /u02/CAMS/MIGRATIONS/dataload/CMS_BASIS_MIG.csv
Any help is greatly appreciated.
Thanks, Mac
################## EDITAnswer from Tajesh below, pretty much. This is what worked. I think the Newline command is what mostly did the trick. When I had edited the CSV file and added a comma at the end of each line the it picked up the last column just fine. I did also have to add the date mask too. But, Tajesh solution means that I don't need to edit the CSV file.
create table LD_CMS_BASIS_MIGRATION
(
contract_number VARCHAR2(8),
productcode VARCHAR2(3),
transaction_number NUMBER,
effective_date DATE,
amendment VARCHAR2(3),
term NUMBER,
active VARCHAR2(1),
agent_number VARCHAR2(5),
premium NUMBER,
icrate NUMBER,
rcrate NUMBER,
ic_allow NUMBER,
rc_allow NUMBER,
sprate NUMBER,
transaction_code VARCHAR2(4),
transaction_date DATE,
agent_category VARCHAR2(4),
agent_sales_code VARCHAR2(4),
freq VARCHAR2(1),
tot_premtd NUMBER,
reference VARCHAR2(40),
alternative_commission_method VARCHAR2(40),
paxus_ref_id VARCHAR2(8)
)
ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "MIGRATIONS" ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
BADFILE 'CMS_BASIS_MIG_BAD.bad'
LOGFILE 'CMS_BASIS_MIG_LOG.log'
SKIP 1
FIELDS TERMINATED BY ','
DATE_FORMAT DATE MASK "dd/mm/yyyy"
MISSING FIELD VALUES ARE NULL
) LOCATION ( 'CMS_BASIS_MIG.csv' )
) REJECT LIMIT UNLIMITED
PARALLEL 5;
Solution
Can you please try with the following create table syntax?
create table LD_CMS_BASIS_MIGRATION
(
contract_number VARCHAR2(8),
productcode VARCHAR2(3),
transaction_number NUMBER,
effective_date DATE,
amendment VARCHAR2(3),
term NUMBER,
active VARCHAR2(1),
agent_number VARCHAR2(5),
premium NUMBER,
icrate NUMBER,
rcrate NUMBER,
ic_allow NUMBER,
rc_allow NUMBER,
sprate NUMBER,
transaction_code VARCHAR2(4),
transaction_date DATE,
agent_category VARCHAR2(4),
agent_sales_code VARCHAR2(4),
freq VARCHAR2(1),
tot_premtd NUMBER,
reference VARCHAR2(40),
alternative_commission_method VARCHAR2(40),
paxus_ref_id VARCHAR2(8)
)
ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "MIGRATIONS" ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
BADFILE 'CMS_BASIS_MIG_BAD.bad'
LOGFILE 'CMS_BASIS_MIG_LOG.log'
SKIP 1
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' MISSING FIELD VALUES ARE NULL
) LOCATION ( 'CMS_BASIS_MIG.csv' )
) REJECT LIMIT UNLIMITED
PARALLEL 5;
If the mentioned code throws an error of any type of "date conversion", then you must have to specify each column name and their format if the column's data type is the date. Example: conversion format