| tab delimited.. problems with .ctl file. [message #72167] |
Fri, 28 March 2003 13:25  |
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)
My data comes from 2 different data files(.TXT)
1. AMAICD02T.TXT (tab delimited)
e.g.
"003.0" "SALMONELLA GASTROENTERITIS" "SALMONELLA GASTROENTERITIS" "SALMONELLA GASTROENTERITIS"
"003.1" "SALMONELLA SEPTICEMIA" "SALMONELLA SEPTICEMIA" "SALMONELLA SEPTICEMIA"
"003.20" "LOCALIZED SALMONELLA INFECT" "LOCALIZED SALMONELLA INFECTION UNSP" "LOCALIZED SALMONELLA INFECTION UNSPECIFIED"
"003.21" "SALMONELLA MENINGITIS" "SALMONELLA MENINGITIS" "SALMONELLA MENINGITIS"
2. AMAICD02F.TXT (fixed-field) – (What does fixed-field mean??)
e.g.
003.0 SALMONELLA GASTROENTERITIS SALMONELLA GASTROENTERITIS SALMONELLA GASTROENTERITIS
003.1 SALMONELLA SEPTICEMIA SALMONELLA SEPTICEMIA SALMONELLA SEPTICEMIA
003.20LOCALIZED SALMONELLA INFECT LOCALIZED SALMONELLA INFECTION UNSPLOCALIZED SALMONELLA INFECTION UNSPECIFIED
003.21SALMONELLA MENINGITIS SALMONELLA MENINGITIS SALMONELLA MENINGITIS
My .CTL file (RIGHT NOW)looks like,
Load data
INFILE ‘AMAICD02F.txt’
INFILE ‘AMAICD02T.txt’
APPEND
INTO TABLE icd_9_DIAGNOSIS_CODES_HIST
FIELDS TERMINATED BY ' ' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
DX_CODE,
SHORT_DESC,
MED_DESC,
LONG_DESC,
DIAG_CODES_EFF_DATE "TO_DATE('01/01/2002','MM/DD/YYYY')"
)
My problem one is,
What it is doing right now is, it is only data from the AMAICD02T.TXT file (because that is the tab delimited file)
and neglecting the other .TXT file i.e. AMAICD02F.TXT (The fixed filed .TXT file)
SO HOW DO I INCORPORATE BOTH THESE LOGIC IN ONE .CTL FILE??
Problem 2,
The way it is loading data right now from file AMAICD02T.TXT is completely wrong too,
This is how my data looks like
DX_CODE SHORT_DESC MED_DESC LONG_DESC DIAG_CODES_EFF_DATE
003.0 SALMONELLA GASTROENTERITIS SALMONELLA 01-JAN-02
003.1 SALMONELLA SEPTICEMIA SALMONELLA 01-JAN-02
003.20LOCALIZED SALMONELLA INFECT LOCALIZED 01-JAN-02
003.21SALMONELLA MENINGITIS SALMONELLA MENINGITIS 01-JAN-02
Why is it ignoring the ENCLOSED BY '"' keyword?? even though the data looks good.
Any help is highly appreciated.
Thank you!
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
