Home » RDBMS Server » Server Utilities » Tab delimited, enclosed by "... still having problems
| Tab delimited, enclosed by "... still having problems [message #72170] |
Mon, 31 March 2003 16:45  |
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!
|
|
|
|
| Re: Tab delimited, enclosed by "... still having problems [message #72171 is a reply to message #72170] |
Tue, 01 April 2003 00:17  |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
[code]1. Your SQL*Loader control file indicates that you
are loading data into a column named
diag_codes_eff_date, but there is no such column in
your table. You need to add that column to your
table, prior to loading the data.
2. Fixed-field means that the columns are in fixed
positions, so that each column will start the same
number of spaces from the left edge of the text file.
For example, the first column might occupy spaces 1
through 6, then the next column might occupy spaces
7-56, and so forth. However, if there are spaces
between the columns, then you will have to adjust the
numbers.
3. You need to use FIELDS TERMINATED BY X'9' in your
controle file for a tab-delimited text file.
4. Because your text files are of different types,
one is tab-delimited and the other is fixed-field, you
will need two separate control files, and will have to
make two SQL*Loader runs.
5. You will need to specify the positions in the
control file for the fixed-field text file. In the
example below, I have assumed that the first column
occupies the first six spaces, that the rest are 50
spaces each, and that there are no spaces between the
fields. If your data file is different, then you will
have to make adjustments
6. I am not sure what you mean by your data looks
wrong. Is it loading the data into the wrong
columns? If that is the case, then it is because you
are not using FIELDS TERMINATED BY X'9'. However, if
you are expecting double quotes within your data, then
you need to not use the OPTIONALLY ENCLOSED BY '"'.
This causes it to view the " as something that tells
it where the data is and not as part of the data.
Here is an example:
-- test.ctl:
LOAD DATA
INFILE 'amaicd02t.txt'
APPEND
INTO TABLE icd_9_diagnosis_codes_hist
FIELDS TERMINATED BY X'9'
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')")
-- test2.ctl:
LOAD DATA
INFILE 'amaicd02f.txt'
APPEND
INTO TABLE icd_9_diagnosis_codes_hist
TRAILING NULLCOLS
(dx_code POSITION (1:6),
short_desc POSITION (7:56),
med_desc POSITION (57:106),
long_desc POSITION (107:156),
diag_codes_eff_date "TO_DATE ('01/01/2002', 'MM/DD/YYYY')")
SQL> CREATE TABLE icd_9_diagnosis_codes_hist
2 (dx_code VARCHAR2 (50) NOT NULL,
3 short_desc VARCHAR2 (50),
4 med_desc VARCHAR2 (50),
5 long_desc VARCHAR2 (50))
6 /
Table created.
SQL> ALTER TABLE icd_9_diagnosis_codes_hist
2 ADD (diag_codes_eff_date DATE)
3 /
Table altered.
SQL> HOST SQLLDR USERID=scott/tiger CONTROL=test.ctl LOG=test.log BAD=test.bad
SQL> HOST SQLLDR USERID=scott/tiger CONTROL=test2.ctl LOG=test2.log BAD=test2.bad
SQL> SELECT * FROM icd_9_diagnosis_codes_hist
2 /
DX_CODE SHORT_DESC MED_DESC LONG_DESC DIAG_CODE
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------
003.0 SALMONELLA GASTROENTERITIS SALMONELLA GASTROENTERITIS SALMONELLA GASTROENTERITIS" 01-JAN-02
003.1 SALMONELLA SEPTICEMIA SALMONELLA SEPTICEMIA SALMONELLA SEPTICEMIA 01-JAN-02
003.20 LOCALIZED SALMONELLA INFECT LOCALIZED SALMONELLA INFECTION UNSP LOCALIZED SALMONELLA INFECTION UNSPECIFIEC 01-JAN-02
003.21 SALMONELLA MENINGITIS SALMONELLA MENINGITIS SALMONELLA MENINGITIS 01-JAN-02
003.0 SALMONELLA GASTROENTERITIS SALMONELLA GASTROENTERITIS SALMONELLA GASTROENTERITIS 01-JAN-02
003.1 SALMONELLA SEPTICEMIA SALMONELLA SEPTICEMIA SALMONELLA SEPTICEMIA 01-JAN-02
003.20 LOCALIZED SALMONELLA INFECT LOCALIZED SALMONELLA INFECTION UNSP LOCALIZED SALMONELLA INFECTION UNSPECIFIED 01-JAN-02
003.21 SALMONELLA MENINGITIS SALMONELLA MENINGITIS SALMONELLA MENINGITIS 01-JAN-02
8 rows selected.
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 15:51:00 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
