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(AILY_OPEN_PRICE)='N/A'
THEN NULL ELSE AILY_OPEN_PRICE END" ,
daily_low_price "CASE WHEN TRIM(AILY_LOW_PRICE)='N/A'
THEN NULL ELSE AILY_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(IVIDEND_EX_DATE)='----------' THEN NULL ELSE
TO_DATE(IVIDEND_EX_DATE,'YYYYMMDD') END",
dividend_yield "CASE WHEN TRIM(IVIDEND_YIELD)='N/A'
THEN NULL ELSE IVIDEND_YIELD END" ,
etf_rank "CASE WHEN TRIM(:ETF_RANK)='N/A' THEN
NULL ELSE :ETF_RANK END"
)

On Feb 9, 8:44*am, The Magnet <a... (AT) unsu (DOT) com> wrote:
Enclose the N/A in quotes and it should work without the additional
comma.


David Fitzjarrell

On Feb 9, 9:41*am, ddf <orat... (AT) msn (DOT) com> wrote:
Looks like you were right. Very strange. I thought SQLLDR would
resolve the issue before it attempts to insert it and notice it is not
numeric. But weird that it is only the last column causing the issue.

On Feb 9, 9:44*am, The Magnet <a... (AT) unsu (DOT) com> wrote:

My first and second thoughts would be to strongly encourage you the
next time create a dummy test case and not post your companies data
online- even if it is just one line. In most shops, this would get you
fired. ans as David stated, enclose the last field in quotes OR do not
provide a value at all

a,b,c,d,