Re: SQLLDR

[Quoted] On Feb 9, 8:44 am, The Magnet <a..._at_unsu.com> wrote:
> 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"
> )

Enclose the N/A in quotes and it should work without the additional comma.

David Fitzjarrell Received on Wed Feb 09 2011 - 16:41:06 CET