Re: SQLLDR

On Feb 9, 9:41 am, ddf <orat..._at_msn.com> wrote:
> 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

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. Received on Wed Feb 09 2011 - 17:38:44 CET