Hi all,
CREATE TABLE SAMPL_TABLE
(
ITEM_CATEGORY VARCHAR2 (30 BYTE),
ITEM_NO VARCHAR2 (240 BYTE),
ITEM_DESCRIPTION VARCHAR2 (240 BYTE),
CUSTOMER_NAME VARCHAR2 (240 BYTE),
CUSTOMER_NO VARCHAR2 (240 BYTE),
VALID_AMOUNT_MONTH1 NUMBER,
VALID_UNITS_MONTH1 NUMBER,
VALID_BUDGET_AMOUNT_MONTH1 NUMBER,
VALID_BUDGET_UNITS_MONTH1 NUMBER,
ATTRIBUTE1 VARCHAR2 (240 BYTE),
ATTRIBUTE2 VARCHAR2 (240 BYTE),
VALID_RATE NUMBER
);
Control File
Options(errors=10000,skip=3)
LOAD DATA
INFILE '/home/appltest/Sheet1.csv'
BADFILE 'sample.bad'
DISCARDFILE 'sample.dsc'
REPLACE
INTO TABLE sampl_table
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(
customer_name CHAR NULLIF customer_name = BLANKS "RTRIM(:customer_name)",
customer_no CHAR NULLIF customer_no = BLANKS "RTRIM(:customer_no)",
item_no CHAR NULLIF item_no = BLANKS "RTRIM(:item_no )",
valid_amount_month1 "to_number(:valid_amount_month1 , '999,999,999.99')" ,
valid_units_month1 "to_number(substr(:valid_units_month1,instr(:valid_units_month1,'$')+1),'999,999,999.99')",
valid_budget_units_month1 "to_number(:valid_budget_units_month1, '999,999,999.99')" ,
valid_budget_amount_month1 "to_number(trim(substr(:valid_budget_amount_month1,instr(:valid_budget_amount_month1,'$')+1)),'999,999,999.99')" ,
valid_rate CHAR NULLIF valid_rate = BLANKS "RTRIM(:valid_rate)"
)
Sheet1.csv
| Cust Name | Cust # | Item No | Item Desc | Category | Amount - Month 1 | Units - Month 1 | act tot | act tot | Budget - Month 1 | Budget -Unit- Month 1 | Rate |
| Cust Name | Cust # | Item No | Item Desc | Category | Amount - Month 1 | Units - Month 1 | act tot | act tot | Budget - Month 1 | Budget -Unit- Month 1 | Rate |
| Cust Name | Cust # | Item No | Item Desc | Category | Amount - Month 1 | Units - Month 1 | act tot | act tot | Budget - Month 1 | Budget -Unit- Month 1 | Rate |
| REYNU | 1001 | ABC001 | ABC Renyu | ABC | | | | | 82 | 61 | 1 |
| REYNU1 | 1028 | ABC123 | ABCRenyu123 | ABC | | | | | 295 | 1469 | 1 |
| REYNU2 | 1028 | ABC344 | ABC Renyu 344 | ABC | | | | | 2270 | 6335 | 1 |
Getting below error
Record 1: Rejected - Error on table SAMPL_TABLE, column VALID_RATE.
ORA-01722: invalid number
Record 2: Rejected - Error on table SAMPL_TABLE, column VALID_RATE.
ORA-01722: invalid number
Record 3: Rejected - Error on table SAMPL_TABLE, column VALID_RATE.
ORA-01722: invalid number
Record 1: Rejected - Error on table SAMPL_TABLE, column VALID_AMOUNT_MONTH1 .
ORA-01722: invalid number
Record 2: Rejected - Error on table SAMPL_TABLE, column VALID_AMOUNT_MONTH1 .
ORA-01722: invalid number
Record 3: Rejected - Error on table SAMPL_TABLE, column VALID_AMOUNT_MONTH1
ORA-01722: invalid number
Can you please help me??
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production