| SOL LOADER date problem [message #74277] |
Wed, 27 October 2004 01:54  |
Darren Links
Messages: 3
Registered: October 2004
|
Junior Member |
|
|
I am trying to load a row that includes two date fields. I have tried everything, including using the date mask, but I still get errors. The file I am using is below. I have also included the table description.
Please help!!
Rgds,
Darren
load data
infile *
into table vat_details
fields terminated by '{'
(compno,internal_ref,cardholder_no,cust_refno,cardholder_name, ?????, spend_category,trans_type,merchant_narrative,visa_mcc,transaction_amt,vat_data ,total_amt, ??????)
BEGINDATA
20000001{6350 {4715-1234-5678-9100{6350 {RINGSELL C {22/07/2002{Office Stationery and Equipment {U{THE STATIONERY STORE MAIL ORDER{ 5111{ 62.62{With VAT Data { 9.33{11/07/2002
Name Null? Type
----------------------------------------- -------- -------------
COMPNO VARCHAR2(8)
INTERNAL_REF VARCHAR2(16)
CARDHOLDER_NO VARCHAR2(19)
CUST_REFNO VARCHAR2(16)
CARDHOLDER_NAME VARCHAR2(35)
TRANS_DATE DATE
SPEND_CATEGORY VARCHAR2(40)
TRANS_TYPE VARCHAR2(1)
MERCHANT_NARRATIVE VARCHAR2(38)
VISA_MCC VARCHAR2(4)
TRANSACTION_AMT NUMBER(8,2)
VAT_DATA VARCHAR2(20)
TOTAL_AMT NUMBER(8,2)
POSTING_DATE DATE
|
|
|
|
| Re: SOL LOADER date problem [message #74278 is a reply to message #74277] |
Wed, 27 October 2004 03:02  |
Raj
Messages: 411
Registered: November 1998
|
Senior Member |
|
|
write date function in loader.
for example
LOAD DATA
INFILE ....
INSERT INTO TABLE
(mydate position(1:10) char
"to_char(to_date(:mydate,'dd/mm/yyyy'),'dd-mon-yyyy')"
)
date value in input file should contains:
01/11/2004
11/12/2004
Raj
|
|
|
|
| Re: SOL LOADER date problem [message #74282 is a reply to message #74278] |
Wed, 27 October 2004 05:59 |
Darren Links
Messages: 3
Registered: October 2004
|
Junior Member |
|
|
Unfortunately this does not work. Please see the following:
load data
infile *
replace into table vat_details
( compno position(01:08) CHAR,
internal_ref position(10:25) CHAR,
cardholder_no position(27:45) CHAR,
cust_refno position(47:62) CHAR,
cardholder_name position(64:93) CHAR,
trans_date position(95:104) CHAR "to_char(to_date(:trans_date,'dd/mm/yyyy'),'dd-mon-yyyy')" ,
spend_category position(106:140) CHAR,
trans_type position(142:142) CHAR,
merchant_narrative position(144:181) CHAR,
visa_mcc position(183:186) CHAR,
transaction_amt position(188:198)DECIMAL EXTERNAL,
vat_data position(200:219) CHAR,
total_amt position(221:233) DECIMAL EXTERNAL
)
BEGINDATA
20000001{6350 {4715-1234-5678-9100{6350 {RINGSELL C {22/07/2002{Office Stationery and Equipment {U{THE STATIONERY STORE MAIL ORDER{ 5111{ 62.62{With VAT Data { 9.33
The SQLLOADER session just hangs.
Darren
|
|
|
|
| Re: SOL LOADER date problem [message #74285 is a reply to message #74282] |
Wed, 27 October 2004 21:49 |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Just use to_date to convert the string to a date. Don't use to_char to convert it back to a string.
trans_date position(95:104) CHAR "to_date(:trans_date,'dd/mm/yyyy')" ,
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
