SQLLDR

From: The Magnet <art_at_unsu.com>
Date: Wed, 9 Feb 2011 06:44:03 -0800 (PST)
Message-ID: <578c894c-c917-4586-9b6d-304e98d8c6aa_at_u14g2000vbg.googlegroups.com>

[Quoted] [Quoted] Here is a weird one, I am using SQLLDR to load a comma delimited file. The last column in the record is numeric. If there is no trailing comma after the field, I get a numeric error. If I place a trailing comma, everything loads fine??

Any thoughts? I provided a line of data and the control file.

AXFN ,ISHARS-MS AC-US,ETF ,
24.95,24.95,0.32,2608,24.1,25.21,ISHARES MSCI ACWI EX US FINANCIALS

SECTO,BLACKROCK                               ,MSCI ALL COUNTRY WORLD

EX USA FINANCIALS,24.73,0.48,20101221,0,N/A LOAD DATA
INFILE *
TRUNCATE
INTO TABLE etf_data
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS

 (ticker                     "TRIM(:ticker)",
  company_name               "TRIM(:company_name)",
  etf_etn                    "TRIM(:etf_etn)",
  daily_open_price           "CASE WHEN TRIM(:DAILY_OPEN_PRICE)='N/A'
THEN NULL ELSE :DAILY_OPEN_PRICE END" ,
  daily_low_price            "CASE WHEN TRIM(:DAILY_LOW_PRICE)='N/A'
THEN NULL ELSE :DAILY_LOW_PRICE END" ,
  weekly_dividend            "CASE WHEN TRIM(:WEEKLY_DIVIDEND)='N/A'
THEN NULL ELSE :WEEKLY_DIVIDEND END" ,
  monthly_volume             "CASE WHEN TRIM(:MONTHLY_VOLUME)='N/A'
THEN NULL ELSE :MONTHLY_VOLUME END" ,
  monthly_low_price          "CASE WHEN TRIM(:MONTHLY_LOW_PRICE)='N/A'
THEN NULL ELSE :MONTHLY_LOW_PRICE END" ,
  monthly_high_price         "CASE WHEN TRIM(:MONTHLY_HIGH_PRICE)='N/
A' THEN NULL ELSE :MONTHLY_HIGH_PRICE END" ,
  full_name                  "TRIM(:full_name)",
  sponser                    "TRIM(:sponser)",
  benchmark                  "TRIM(:benchmark)",
  monthly_nav                "CASE WHEN TRIM(:MONTHLY_NAV)='N/A' THEN

NULL ELSE :MONTHLY_NAV END" ,
  annual_operating_expense "CASE WHEN
TRIM(:ANNUAL_OPERATING_EXPENSE)='N/A' THEN NULL ELSE :ANNUAL_OPERATING_EXPENSE END" ,
  dividend_ex_date "CASE WHEN
TRIM(:DIVIDEND_EX_DATE)='----------' THEN NULL ELSE TO_DATE(:DIVIDEND_EX_DATE,'YYYYMMDD') END",   dividend_yield "CASE WHEN TRIM(:DIVIDEND_YIELD)='N/A' THEN NULL ELSE :DIVIDEND_YIELD END" ,

  etf_rank                   "CASE WHEN TRIM(:ETF_RANK)='N/A' THEN

NULL ELSE :ETF_RANK END"
) Received on Wed Feb 09 2011 - 15:44:03 CET