-
SQL*LOADER issue
hi
iam new to SQL*LOADER.
I got FLAT file (text data) from powerhouse and not able to load into oracle .
my ctl look like this.
OPTIONS (BINDSIZE=1024000)
LOAD DATA
INFILE *
INTO TABLE air_miles
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
WORK_ORDER_NO
,AIRMILES_SEQ_NO
,AIRMILES_CARD_NO
,AIRMILES_OFFER_CD
,AIRMILES_AMOUNT
,AIRMILES_COUNT
,TRAN_DATE
,AIRMILES_LOCN_CODE
,POSTED_FLAG
,POSTING_DATE
,AIRMILES_ERROR_FLAG
,AIRMILES_LOAD_DATE
,CREDIT_FLAG
)
BeginData
275636, 63,8006 998 0100,STANDARD , 1225.80, 61,2000/08/29,PTVN,Y,2001/02/14, , ,Y
275646, 28,8006 998 0100,STANDARD , 141.53, 7,1998/03/24,PTVN,Y,1998/03/25, , ,Y
275648, 42,8170 563 8750,STANDARD , 30.00, 1,1998/04/07,PTVN,Y,1998/09/04, , ,Y
I got linefeed in the flat file.How can i get rid of line feed ?
-
Try this -
OPTIONS (BINDSIZE=1024000)
LOAD DATA
INFILE * -----> specify the textfile along with path
INTO TABLE air_miles
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
Col1 number(10) nullif col1=blanks,---->specify the datatypes for all columns along with width you have created the table.
col2 char(20) nullif col2=blanks,
col3 date(14) "MMDDYYYY" nullif col3=blanks
)
NOTE: Do not specify nullif (columnname)=blanks if the column is primary or foreign or not null columns.
Let me know if this helps.
Last edited by prince_jr; 10-13-2003 at 01:36 PM.
-
sarovb, did you ever fix this? was it really due to "non-standard" record separators? if so have a look at SRF here: http://www.csee.umbc.edu/help/oracle...ch03.htm#11755
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|