Error converting data type varchar to float

• water490

  Ten Centuries

  Points: 1104

  More actions

  June 5, 2025 at 4:42 am

  Go to Answer

  #4602844

  Hi everyone

  I have a bunch of CSV files that I need to import into SS using BULK INSERT.  One file works fine.  The other fails.  Both have the same setup...there is one column that has no data in it so there is only a column header.  I used the logic from the CSV file that loaded correctly to the other CSV file.  The second CSV file failed.  I am completely stumped.

  The SP does a BULK INSERT into below table:

  CREATE TABLE #DIVIDENDS_NEW
  (
  [DIVIDEND_AMOUNT] [varchar](15) NULL
  )
  

  Once it is stored as varchar then I convert it and put it into another table:

  CREATE TABLE #DIVIDENDS_CURRENT
  (
  [DIVIDEND_AMOUNT] [numeric](10, 3) NULL
  )

  This logic works fine for one CSV but not the other even though both are same setup (ie, the column is empty):

  CAST(ROUND(REPLACE(DIVIDEND_AMOUNT,'"',''),3) AS NUMERIC(10,3)) AS DIVIDEND_AMOUNT

  Error:

  Error converting data type varchar to float.

  What could be the issue?  How do I fix it?

  Thank you

   

• Jo Pattyn

  SSC-Dedicated

  Points: 32435

  More actions

  June 5, 2025 at 8:53 am

  Answer

  #4602936

  There is probably a non-printable character in it.

  CREATE TABLE #DIVIDENDS_NEW
  (
  [DIVIDEND_AMOUNT] [varchar](15) NULL
  );
  
  insert into #DIVIDENDS_NEW select NULL;
  insert into #DIVIDENDS_NEW select '5';
  insert into #DIVIDENDS_NEW select CHAR(10); /* invisible character*/
  
  SELECT 
  [DIVIDEND_AMOUNT] [DIVIDEND_AMOUNT_CHAR]
  ,try_convert(NUMERIC(10,3),REPLACE(DIVIDEND_AMOUNT,'"','')) DIVIDEND_AMOUNT_TRY_NUMERIC
  ,CASE 
  WHEN try_convert(NUMERIC(10,3),REPLACE(DIVIDEND_AMOUNT,'"','')) IS NOT NULL THEN CAST(ROUND(REPLACE(DIVIDEND_AMOUNT,'"',''),3) AS NUMERIC(10,3)) 
  ELSE NULL
  END DIVIDEND_AMOUNT
  FROM #DIVIDENDS_NEW
  

• water490

  Ten Centuries

  Points: 1104

  More actions

  June 5, 2025 at 3:35 pm

  #4603117

  Jo Pattyn wrote:

  There is probably a non-printable character in it.

  CREATE TABLE #DIVIDENDS_NEW
  (
  [DIVIDEND_AMOUNT] [varchar](15) NULL
  );
  
  insert into #DIVIDENDS_NEW select NULL;
  insert into #DIVIDENDS_NEW select '5';
  insert into #DIVIDENDS_NEW select CHAR(10); /* invisible character*/
  
  SELECT 
  [DIVIDEND_AMOUNT] [DIVIDEND_AMOUNT_CHAR]
  ,try_convert(NUMERIC(10,3),REPLACE(DIVIDEND_AMOUNT,'"','')) DIVIDEND_AMOUNT_TRY_NUMERIC
  ,CASE 
  WHEN try_convert(NUMERIC(10,3),REPLACE(DIVIDEND_AMOUNT,'"','')) IS NOT NULL THEN CAST(ROUND(REPLACE(DIVIDEND_AMOUNT,'"',''),3) AS NUMERIC(10,3)) 
  ELSE NULL
  END DIVIDEND_AMOUNT
  FROM #DIVIDENDS_NEW
  

  Thank you so much!.  This worked.