OraFAQ Forum: Server Utilities » having problems with the delimiter??

Home » RDBMS Server » Server Utilities » having problems with the delimiter??

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

having problems with the delimiter?? [message #72157]

Thu, 27 March 2003 17:08

Sabrina
Messages: 76
Registered: February 2002

Member

My table looks like,
SQL> desc icd_9_diagnosis_codes_hist;
Name Null? Type
------------------------------- -------- ----
DX_CODE NOT NULL VARCHAR2(50)
SHORT_DESC VARCHAR2(50)
MED_DESC VARCHAR2(50)
LONG_DESC VARCHAR2(50)

I am trying to load data into this table from a .TXT file.

My icd9b01.TXT file looks like, (comma � delimited, and contains the data for columns DX_CODE and SHORT_DESC)
"001 ","CHOLERA"
"001.0 ","CHOLERA DUE TO VIBRIO CHOLERAE"
"001.1 ","CHOLERA DUE TO VIBRIO CHOLERAE EL TOR"
"001.9 ","CHOLERA, UNSPECIFIED"
"002 ","TYPHOID AND PARATYPHOID FEVERS"
"002.0 ","TYPHOID FEVER"
"002.1 ","PARATYPHOID FEVER A"
"002.2 ","PARATYPHOID FEVER B"
"002.3 ","PARATYPHOID FEVER C"
"002.9 ","PARATYPHOID FEVER, UNSPECIFIED"
"003 ","OTHER SALMONELLA INFECTIONS"

e.g.
001 data goes in the DX_CODE column
CHOLERA data goes in the SHORT_DESC column.

My .CTL file looks like,
Load data infile �icd9a01.txt�
Into table ICD_9_DIAGNOSIS_CODES_HIST append
(
DX_CODE POSITION(1-50) TERMINATED BY �,� ENCLOSED BY ��,
SHORT_DESC POSITION(51-100) TERMINATED BY �,� ENCLOSED BY ��,
DX_CODE POSITION(101-151) TERMINATED BY �,� ENCLOSED BY ��,
DX_CODE POSITION(151-200) TERMINATED BY �,� ENCLOSED BY ��,
)

and then I have the .SH file and .LOG file

But when I load the data(using source ....sh), the data in the table ICD_9_DIAGNOSIS_CODES_HIST looks like,
SQL> select DX_CODE from ICD_9_DIAGNOSIS_CODES_HIST where rownum<10;

DX_CODE
--------------------------------------------------
"390 ","RHEUMATIC FEVER WITHOUT MENTION OF HEART
"391 ","RHEUMATIC FEVER WITH HEART INVOLVEMENT"
"391.0 ","ACUTE RHEUMATIC PERICARDITIS"
"391.1 ","ACUTE RHEUMATIC ENDOCARDITIS"
"391.2 ","ACUTE RHEUMATIC MYOCARDITIS"
"391.8 ","OTHER ACUTE RHEUMATIC HEART DISEASE"
"391.9 ","ACUTE RHEUMATIC HEART DISEASE, UNSPECIFI
"392 ","RHEUMATIC CHOREA"
"392.0 ","RHEUMATIC CHOREA WITH HEART INVOLVEMENT"

For some reason, it is ignoring the comma delimited and enclosed by""..

Please tell me, What am I doing wrong??

Thank you.

Report message to a moderator

Re: having problems with the delimiter?? [message #72161 is a reply to message #72157]

Fri, 28 March 2003 06:24

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

change your controlfile like this..add POSITION CLUASE
appropriately.

C:>cat test.ctl
LOAD DATA INFILE test.dat
replace
iNTO table test262
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  TRAILING NULLCOLS
(
DX_CODE,
SHORT_DESC,
MED_DESC,
LONG_DESC
)

C:>cat test.dat
"001 ","CHOLERA"
"001.0 ","CHOLERA DUE TO VIBRIO CHOLERAE"
"001.1 ","CHOLERA DUE TO VIBRIO CHOLERAE EL TOR"
"001.9 ","CHOLERA, UNSPECIFIED"
"002 ","TYPHOID AND PARATYPHOID FEVERS"
"002.0 ","TYPHOID FEVER"
"002.1 ","PARATYPHOID FEVER A"
"002.2 ","PARATYPHOID FEVER B"
"002.3 ","PARATYPHOID FEVER C"
"002.9 ","PARATYPHOID FEVER, UNSPECIFIED"
"003 ","OTHER SALMONELLA INFECTIONS"

C:>desc test262

Object: test262
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 DX_CODE                                                                          VARCHAR2(50)
 SHORT_DESC                                                                       VARCHAR2(50)
 MED_DESC                                                                         VARCHAR2(50)
 LONG_DESC                                                                        VARCHAR2(50)

C:>sqlldr userid=mag/mag control=test.ctl

SQL*Loader: Release 8.1.6.0.0 - Production on Fri Mar 28 08:08:04 2003

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 11

C:>query test262

Table: test262

DX_CODE                                            SHORT_DESC
-------------------------------------------------- --------------------------------------------------
MED_DESC                                           LONG_DESC
-------------------------------------------------- --------------------------------------------------
001                                                CHOLERA

001.0                                              CHOLERA DUE TO VIBRIO CHOLERAE

001.1                                              CHOLERA DUE TO VIBRIO CHOLERAE EL TOR

001.9                                              CHOLERA, UNSPECIFIED

002                                                TYPHOID AND PARATYPHOID FEVERS

002.0                                              TYPHOID FEVER

002.1                                              PARATYPHOID FEVER A

002.2                                              PARATYPHOID FEVER B

002.3                                              PARATYPHOID FEVER C

002.9                                              PARATYPHOID FEVER, UNSPECIFIED

003                                                OTHER SALMONELLA INFECTIONS

11 rows selected.

C:>

Report message to a moderator

Previous Topic:	imp from a .dmp file on another server
Next Topic:	coalesce running a long time for 1 tablespace only

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 15:52:03 CDT 2024