Home » RDBMS Server » Server Utilities » External Table with Tab Delimiter Loads Data Wrong (Oracle 18.0.0 Unix)
| External Table with Tab Delimiter Loads Data Wrong [message #678931] |
Wed, 22 January 2020 10:45  |
Duane
Messages: 585
Registered: December 2002
|
Senior Member |
|
|
First off, I'm unable to provide the data so the data can be tested. No way for me to provide the data to an open forum like this.
The data is Tab Delimited with double quotations around any string data. Numeric data does not have any double quotations.
The problem: I believe one or more of the columns in the data are being shifted to the left which causes an "invalid number" error. I read a post where someone had a problem with an External Table where they thought that Oracle interprets two subsequent tabs incorrectly as one tab. That would make sense since the data is shifted to the left by one or more columns. Data from other columns are ending up in a different column. I think it might have something to do with there is no data for that column and there are subsequent tabs so Oracle gets confused.
As a test I made all the columns VARCHAR2 and I'm able to load the data using an External Table. The problem is after the "primary person last name" column, the data is incorrect for the rest of the columns. Data is ending up in columns that should not contain that data. Throwing the data into a table and exporting the data with double quotations around all the columns with a tab delimiter also loads the data correctly in the External Table. It's only when the data has double quotations around strings and numeric data does not does the data not load correctly.
Is there any work around for when Oracle interprets two subsequent tabs incorrectly as one tab?
CREATE TABLE TEST_1
(
affiliation number,
account_number number,
letter_salutation varchar2(50 byte),
sequence_name varchar2(16 byte),
envelope_salutation varchar2(50 byte),
company_name varchar2(50 byte),
address_line_1 varchar2(50 byte),
address_line_2 varchar2(50 byte),
city varchar2(50 byte),
state_or_province varchar2(30 byte),
zip_code varchar2(20 byte),
zip_4 number,
telephone_number number,
alternate_id varchar2(11 byte),
source_code varchar2(8 byte),
pledge_amount number,
pledge_date date,
mode_of_payment number,
benefit_code varchar2(6 byte),
premium_code varchar2(8 byte),
premium_size_code varchar2(4 byte),
premium_description varchar2(30 byte),
pledge_amount_paid_to_date number,
pledge_type varchar2(1 byte),
fund_code varchar2(5 byte),
number_of_pledge_years number,
total_market_value number,
payment_date date,
payment_amount number,
cash_receipt_number number,
gl_account_number number,
payment_reference varchar2(30 byte),
payment_market_value number,
email_address varchar2(60 byte),
major_donor_indicator varchar2(3 byte),
pledge_id number,
payment_id number,
primary_person_first_name varchar2(30 byte),
primary_person_middle_name varchar2(30 byte),
primary_person_last_name varchar2(50 byte),
hard_credit_affiliation number,
hard_credit_account_number number,
hard_credit_alternate_id varchar2(11 byte),
hard_credit_envelope_sal varchar2(50 byte),
hard_credit_company_name varchar2(50 byte),
hard_credit_address_line_1 varchar2(50 byte),
hard_credit_address_line_2 varchar2(50 byte),
hard_credit_city varchar2(50 byte),
hard_credit_state_or_province varchar2(30 byte),
hard_credit_zipcode varchar2(20 byte),
hard_credit_zip4 number,
hard_credit_telephone number,
refund_payment_id number,
refund_crr_number number,
country varchar2(3 byte),
country_name varchar2(50 byte),
country_code_2 varchar2(2 byte),
hard_credit_country varchar2(3 byte),
hard_credit_country_name varchar2(50 byte),
hard_credit_country_code_2 varchar2(2 byte),
shipping_amount number,
shipping_paid_to_date number
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY TEST_FILE_DIRECTORY
ACCESS PARAMETERS
( records delimited by newline
badfile test_file_directory: 'test_upload_bad_file.bad'
logfile test_file_directory: 'test_upload_log_file.log'
skip 1
fields terminated by 0X'09' optionally enclosed by '"' lrtrim
missing field values are null
date_format date mask "yyyymmdd"
)
LOCATION (TEST_FILE_DIRECTORY:'3786620_1 12.18.19.txt')
)
REJECT LIMIT 0;
|
|
|
|
| Re: External Table with Tab Delimiter Loads Data Wrong [message #678991 is a reply to message #678931] |
Sat, 25 January 2020 20:32  |
Duane
Messages: 585
Registered: December 2002
|
Senior Member |
|
|
Ok, I challenge anyone to load up the attached file. I think I have have tried every single possible combination to get this file to load. No cheating in adding double quotations around all the columns or putting in values. I can get it to load then but not the way the current file is.
It has to be something with stripping out whitespace and the tabs or something where Oracle is not properly reading the tabs.
Text File
1 "First1" "M1" "Last1" 0 5 0 "R1"|
2 "First2" "M2" "Last2" 0 0 4 "R2"|
CREATE TABLE TEST_TAB1
(
AFFILIATION NUMBER,
FIRST_NAME VARCHAR2(50 BYTE),
MIDDLE_NAME VARCHAR2(16 BYTE),
LAST_NAME VARCHAR2(50 BYTE),
HOUSEHOLD NUMBER,
EXTRA_1 VARCHAR2(50 BYTE),
DISTRICT NUMBER,
EXTRA_2 VARCHAR2(50 BYTE),
EXTRA_3 VARCHAR2(50 BYTE),
CHILDREN_NAME VARCHAR2(50 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY FILE_DIRECTORY
ACCESS PARAMETERS
( records delimited by '|' characterset 'AL32UTF8'
badfile file_directory: 'bad_file.bad'
logfile file_directory: 'log_file.log'
fields notrim terminated by 0x'09'
(affiliation integer,
first_name char(50) enclosed by '"' ,
middle_name char(50) enclosed by '"' ,
last_name char(50) enclosed by '"' ,
household integer defaultif household = blanks,
extra_1 integer defaultif extra_1 = blanks,
district integer defaultif district = blanks,
extra_2 integer defaultif extra_2 = blanks,
extra_3 integer defaultif extra_3 = blanks,
children_name char(50) enclosed by '"'
)
)
LOCATION (FILE_DIRECTORY:'tab-test.txt')
)
REJECT LIMIT 0;
-
Attachment: tab-test.txt
(Size: 0.07KB, Downloaded 3002 times)
[Updated on: Sat, 25 January 2020 20:37] Report message to a moderator |
|
|
|
| Re: External Table with Tab Delimiter Loads Data Wrong [message #678994 is a reply to message #678991] |
Sun, 26 January 2020 04:32 |
John Watson
Messages: 8966
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
What error do you gwt?It gives me this,KUP-04021: field formatting error for field FIRST_NAME
KUP-04035: beginning enclosing delimiter not found
KUP-04101: record 1 rejected in file C:\TMP\tab-test.txt
KUP-04021: field formatting error for field EXTRA_2
KUP-04023: field start is after end of record
KUP-04101: record 2 rejected in file C:\TMP\tab-test.txt
KUP-04021: field formatting error for field AFFILIATION
KUP-04025: field extends beyond end of record
KUP-04101: record 3 rejected in file C:\TMP\tab-test.txt |
|
|
|
| Re: External Table with Tab Delimiter Loads Data Wrong [message #678997 is a reply to message #678994] |
Sun, 26 January 2020 15:36 |
Duane
Messages: 585
Registered: December 2002
|
Senior Member |
|
|
Here's what I get. I've also tried other combinations that return "invalid number" because it's trying to load character data into an number column. It would appear the data gets shifted over. I just can't figure out how to load the data without supplying all columns with data or enclosing all the data in double quotations.
CREATE TABLE TEST_TAB1
(
AFFILIATION NUMBER,
FIRST_NAME VARCHAR2(50 BYTE),
MIDDLE_NAME VARCHAR2(16 BYTE),
LAST_NAME VARCHAR2(50 BYTE),
HOUSEHOLD NUMBER,
EXTRA_1 VARCHAR2(50 BYTE),
DISTRICT NUMBER,
EXTRA_2 VARCHAR2(50 BYTE),
EXTRA_3 VARCHAR2(50 BYTE),
CHILDREN_NAME VARCHAR2(50 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY FILE_DIRECTORY
ACCESS PARAMETERS
( records delimited by '|' characterset 'AL32UTF8'
badfile umsl_file_directory: 'bad_file.bad'
logfile umsl_file_directory: 'log_file.log'
fields notrim terminated by 0x'09'
(affiliation integer,
first_name char(50) enclosed by '"' ,
middle_name char(50) enclosed by '"' ,
last_name char(50) enclosed by '"' ,
household integer defaultif household = blanks,
extra_1 integer defaultif extra_1 = blanks,
district integer defaultif district = blanks,
extra_2 integer defaultif extra_2 = blanks,
extra_3 integer defaultif extra_3 = blanks,
children_name char(50) enclosed by '"'
)
)
LOCATION (FILE_DIRECTORY:'tab-test.txt')
)
REJECT LIMIT 0;
Field Definitions for table TEST_TAB1
Record format DELIMITED, delimited by |
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
AFFILIATION Integer (4)
Record position (+0, +4)
Terminated by "09"
FIRST_NAME CHAR (50)
Terminated by "09"
Enclosed by """ and """
MIDDLE_NAME CHAR (50)
Terminated by "09"
Enclosed by """ and """
LAST_NAME CHAR (50)
Terminated by "09"
Enclosed by """ and """
HOUSEHOLD Integer (4)
Record position (+0, +4)
Terminated by "09"
DEFAULT if (HOUSEHOLD = BLANKS)
EXTRA_1 Integer (4)
Record position (+0, +4)
Terminated by "09"
DEFAULT if (EXTRA_1 = BLANKS)
DISTRICT Integer (4)
Record position (+0, +4)
Terminated by "09"
DEFAULT if (DISTRICT = BLANKS)
EXTRA_2 Integer (4)
Record position (+0, +4)
Terminated by "09"
DEFAULT if (EXTRA_2 = BLANKS)
EXTRA_3 Integer (4)
Record position (+0, +4)
Terminated by "09"
DEFAULT if (EXTRA_3 = BLANKS)
CHILDREN_NAME CHAR (50)
Terminated by "09"
Enclosed by """ and """
KUP-04021: field formatting error for field FIRST_NAME
KUP-04035: beginning enclosing delimiter not found
KUP-04101: record 1 rejected in file /opt/transfer/tab-test.txt
KUP-04021: field formatting error for field FIRST_NAME
KUP-04035: beginning enclosing delimiter not found
KUP-04101: record 2 rejected in file /opt/transfer/tab-test.txt
|
|
|
|
| Re: External Table with Tab Delimiter Loads Data Wrong [message #679000 is a reply to message #678997] |
Mon, 27 January 2020 09:36 |
Duane
Messages: 585
Registered: December 2002
|
Senior Member |
|
|
|
Since someone else has received the same error or is having the same issue loading TAB delimited data into Oracle I'll assume that the Oracle Loader feature is broken for TAB delimited data. That's is, until someone can tell me what I'm doing wrong or show me a certain statement to use then Oracle Loader is broken.
|
|
|
|
Goto Forum:
Current Time: Mon Mar 03 17:34:07 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
