Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 894
  • Last Modified:

Convert text into number with decimals

I am importing a large group of Excel files that includes two columns of numbers that are formatted as text. I import them, as is, into a temp table and now am trying to append them to the final table while changing the data type from text to numeric.  The problem I am having is that when I change the data type the numbers are being truncated to integers.

I have tried Value, CInt, CDbl, CSng and also tried changing the final table format to General Number, Single, Double, Fixed Format, Standard Format, General Number Format with Decimal set to a specific number and to Auto. Every time the number loses anything to the right of the decimal point.

Any suggestions?
2 Solutions
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
CDbl() should work, even with leading and trailing spaces.

?CDbl("  123.456  ")

Perhaps you have to test to see if the values are numeric, otherwise if any values are not it will cause the expression to fail.

?iif(IsNumeric("  123.456"), CDbl("  123.456"), "X")
Dale FyeCommented:
Make sure the data type of the field you are trying to push them to is Double.  Then make sure that the display format is set to General or Fixed with a specific number of decimal places.
JazCatAuthor Commented:
Thanks, both of these helped. I thought I had used the Double, Fixed, 1 decimal place format but I guess not cause that seemed to work. I also added the IsNumeric check just in case.  Interesting, the Double, General, 1 decimal place did not in all cases add a number after the decimal and also sometimes added two.

Thanks both of you for your quick response.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now