Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Posts
    123

    Unanswered: Import lots of decimals, problem on the 18th

    Hello,

    I'm trying to import data from an Excel sheet into a table. Not all of them is imported.

    Exl: 0,000801054857569349 becomes
    Sql: 0,000801054857569350 when it is imported.

    The column in SQL-Server is defined as DECIMAL 28.18, should take all 18 numbers i thought. Tried 28.19 also but it only added another zero at the end.

    I've tried importing via DTS and manually import,same result both times.

    Any suggestions?

  2. #2
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    Did you try to import text data from an Excel and convert it onto decimal in SQL Server?
    Last edited by Kris Zywczyk; 12-20-06 at 10:49.
    Regards
    Kris Zywczyk

  3. #3
    Join Date
    Jul 2003
    Posts
    123
    DECIMAL with precision 28 and scale 18. Tried with scale 19 but only got one more zero at the end.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The cell in Excel is probably a float. Depending on how it is rendered there will be a point where a given source (float) value will produce different target values for different target data types. This is a problem as old as floating point numbers.

    Floats are good for measurements, but lousy for counts. What you are seeing could be the difference caused by that quality of a float.

    -PatP

  5. #5
    Join Date
    Jul 2003
    Posts
    123
    The cell shows up as "Double" if I look at it in design view in an Access DB that has a link to it.

    Hope the beancounters can accepts this fact that the number doesn't show up EXACTLY as it is in Excel. I've tried to maually insert the value with all 19 decimal and it shows ok.

    I've seen this problem show up when I'm using a C++ application to insert values from Excel into SQL Server via ODBC also. And there the value is picked as a string and then used to build a SQL-Statement. I will get the same values as with the manual import into SQL Server.

    Thanks for the comment.

    One more workday, then it's Christmas!

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Pat Phelan
    Floats are good for measurements, but lousy for counts.
    Huh?

    How is a datatype that's imperfect good for anything?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Floats (reals and doubles) are perfect for dealing with measures, because floats work like measurements work... Counts (INT, MONEY, DECIMAL, etc) are perfect for counts, because they actually are counts. A measurement is an approximation that has some desired degree of precision, and is quick and easy to perform calculations of practically infinite scope at the desired degree of precsion. A count is exact, but fundamentally limited in terms of the kind of calculations it can do and the scope of values it can represent.

    When you're dealing with money, you need a count. When you're dealing with computational measures such as C or e, or figuring anything to do with astronomy or weather, counts are almost worthless and floats are appropriate.

    You can't use either tool to efficiently do the jobs that the other is built to handle... You can beat either of them into submission and they can pinch-hit for one another, but they are really two different classes of tools even though people sometimes confuse floats and counts when they don't grok them both.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •