Today's Messages (on)  | Unanswered Messages (off)

Forum: Server Utilities
 Topic: External Table with Tab Delimiter Loads Data Wrong
External Table with Tab Delimiter Loads Data Wrong [message #678931] Wed, 22 January 2020 10:45
Messages: 457
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?

  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
       ( 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')

Current Time: Wed Jan 22 10:48:04 CST 2020