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 Go to next message
Duane
Messages: 460
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 Go to previous messageGo to next message
Duane
Messages: 460
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"|

External Table


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;

Can anyone get this to load?
  • Attachment: tab-test.txt
    (Size: 0.07KB, Downloaded 220 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 Go to previous messageGo to next message
John Watson
Messages: 8344
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 Go to previous messageGo to next message
Duane
Messages: 460
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 Go to previous message
Duane
Messages: 460
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.
Previous Topic: DataPump Export ORA-00922: missing or invalid option
Next Topic: Permission denied while scanning ASM disks
Goto Forum:
  


Current Time: Fri Sep 18 15:56:34 CDT 2020