none
alter column error RRS feed

  • Question

  • Hi All i am loading data from excel source into sql server table. I was getting the truncation error

    Previously all the columns were of datatype floats. once i got the truncation error i changed them to varchar(50). The package ran without errors but now instead of values i am seeing "#REF!" as values. 

    Now i am trying to alter the column back to float and i am getting the following error

    "Error converting data type varchar to float

    the statement has been  terminated.

    please advice

    Thanks

    Monday, July 22, 2019 2:01 PM

Answers

  • values i am seeing "#REF!" as values. 

    You get the error because you have non-numeric values in the table like this #REF!. You have to delete the rows or Change the value to a numeric one.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by LisaKruger Monday, July 22, 2019 4:29 PM
    Monday, July 22, 2019 2:17 PM
  • Hi LisaKruger,

    It is better not to use float data type  because it is imprecise.

    Please consider to use DECIMAL(x,y) instead.

    Also, please share a screen shot of the Excel file.

    • Marked as answer by LisaKruger Monday, July 22, 2019 4:29 PM
    Monday, July 22, 2019 2:23 PM

All replies

  • Try to save a text version of your excel and try to load it to see what is going on there.
    Monday, July 22, 2019 2:03 PM
    Moderator
  • values i am seeing "#REF!" as values. 

    You get the error because you have non-numeric values in the table like this #REF!. You have to delete the rows or Change the value to a numeric one.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by LisaKruger Monday, July 22, 2019 4:29 PM
    Monday, July 22, 2019 2:17 PM
  • Hi LisaKruger,

    It is better not to use float data type  because it is imprecise.

    Please consider to use DECIMAL(x,y) instead.

    Also, please share a screen shot of the Excel file.

    • Marked as answer by LisaKruger Monday, July 22, 2019 4:29 PM
    Monday, July 22, 2019 2:23 PM
  • Thanks all loaded without errors after deleting the row

    Monday, July 22, 2019 4:29 PM