Home » RDBMS Server » Server Utilities » SOL LOADER date problem
SOL LOADER date problem [message #74277] Wed, 27 October 2004 01:54 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
Barbara Boehmer
Messages: 9101
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')" ,
Previous Topic: WHEN conditions
Next Topic: SQL Loader and Selects
Goto Forum:
  


Current Time: Wed Dec 25 08:05:23 CST 2024