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?Did you try to import text data from an Excel and convert it onto decimal in SQL Server?|||DECIMAL with precision 28 and scale 18. Tried with scale 19 but only got one more zero at the end.|||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|||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!|||Floats are good for measurements, but lousy for counts.

Huh?

How is a datatype that's imperfect good for anything?|||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

Import data truncate my data

I am using SQL 2005. I have a table defined as decimal(12,4).
I import data like 1.99305 to it, and the value in the database becomes
1.9930 instead of 1.9931.
But, when I edit the the database like
update myTable set price = 1.99305 where id = '12345'
the value of the Price becomes 1.9931.
How can I make the import program import the data to be 1.9931 instead of
1.9930 ?
Thank you.
CREATE TABLE myTable (
id int
Price decimal(12,4) NULL
)
1.99305It will be kind of hard to aanwer without knowing the importing tool.
Is it using Bulk Insert? BCP? DTS? SSIS? Something else?
Roy Harvey
Beacon Falls, CT
On Fri, 22 Jun 2007 15:36:05 -0500, "fniles" <fniles@.pfmail.com>
wrote:
>I am using SQL 2005. I have a table defined as decimal(12,4).
>I import data like 1.99305 to it, and the value in the database becomes
>1.9930 instead of 1.9931.
>But, when I edit the the database like
>update myTable set price = 1.99305 where id = '12345'
>the value of the Price becomes 1.9931.
>How can I make the import program import the data to be 1.9931 instead of
>1.9930 ?
>Thank you.
>CREATE TABLE myTable (
> id int
> Price decimal(12,4) NULL
>)
>1.99305
>|||I was using the Copy wizard in the Microsoft SQL Server Management
Studio (right click on the database - Tasks - Copy Database)
On Jun 22, 4:24 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> It will be kind of hard to aanwer without knowing the importing tool.
> Is it using Bulk Insert? BCP? DTS? SSIS? Something else?
> Roy Harvey
> Beacon Falls, CT
> On Fri, 22 Jun 2007 15:36:05 -0500, "fniles" <fni...@.pfmail.com>
> wrote:
>
> >I am using SQL 2005. I have a table defined as decimal(12,4).
> >I import data like 1.99305 to it, and the value in the database becomes
> >1.9930 instead of 1.9931.
> >But, when I edit the the database like
> >update myTable set price = 1.99305 where id = '12345'
> >the value of the Price becomes 1.9931.
> >How can I make the import program import the data to be 1.9931 instead of
> >1.9930 ?
> >Thank you.
> >CREATE TABLE myTable (
> > id int
> > Price decimal(12,4) NULL
> >)
> >1.99305- Hide quoted text -
> - Show quoted text -|||(fiefie.niles@.gmail.com) writes:
> I was using the Copy wizard in the Microsoft SQL Server Management
> Studio (right click on the database - Tasks - Copy Database)
This I don't get:
>> >I am using SQL 2005. I have a table defined as decimal(12,4).
>> >I import data like 1.99305 to it, and the value in the database becomes
>> >1.9930 instead of 1.9931.
When you use the Copy Database Wizard, you are copying the entire database
definition, so you cannot copy from, say, decimal(14,6) to decimal(12,4),
and this you cannot get this truncation issue. Or am I missing something?
Another story is that CDW offers to method of copying: Attach/Detach
and the SMO method, and the latter method is very unreliable, and nothing
you can use seriously. I think that by SP2, it has reached the state
where it can be called a beta, but it is far from being trustworthy.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||> When you use the Copy Database Wizard, you are copying the entire database
> definition, so you cannot copy from, say, decimal(14,6) to decimal(12,4),
> and this you cannot get this truncation issue. Or am I missing something?
Sorry, I was not clear. I was copying from an Access database to the SQL
Server database, and the datatype in the Access is Text(50).
The data in the Access database is 1.99305, and when it copied to SQL Server
it became 1.9930 instead of 1.9931.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99589C25392E0Yazorman@.127.0.0.1...
> (fiefie.niles@.gmail.com) writes:
>> I was using the Copy wizard in the Microsoft SQL Server Management
>> Studio (right click on the database - Tasks - Copy Database)
> This I don't get:
>> >I am using SQL 2005. I have a table defined as decimal(12,4).
>> >I import data like 1.99305 to it, and the value in the database becomes
>> >1.9930 instead of 1.9931.
> When you use the Copy Database Wizard, you are copying the entire database
> definition, so you cannot copy from, say, decimal(14,6) to decimal(12,4),
> and this you cannot get this truncation issue. Or am I missing something?
> Another story is that CDW offers to method of copying: Attach/Detach
> and the SMO method, and the latter method is very unreliable, and nothing
> you can use seriously. I think that by SP2, it has reached the state
> where it can be called a beta, but it is far from being trustworthy.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||On Mon, 25 Jun 2007 07:44:31 -0500, "fniles" <fniles@.pfmail.com>
wrote:
>> I was using the Copy wizard in the Microsoft SQL Server Management
>> Studio (right click on the database - Tasks - Copy Database)
>Sorry, I was not clear. I was copying from an Access database to the SQL
>Server database, and the datatype in the Access is Text(50).
>The data in the Access database is 1.99305, and when it copied to SQL Server
>it became 1.9930 instead of 1.9931.
I am still not clear, since the tool you say you used does not work
with Access.
When I open Microsoft SQL Server Management Studio, right click on a
database, and choose the Tasks / Copy Database... option, the Copy
Database Wizard opens. The message in that window is "You can use
this wizard to move or copy databases from an instance of SQL Server
2000 or SQL Server 2005 to an instance of SQL Server 2005."
Roy Harvey
Beacon Falls, CT|||I am very sorry, I meant to say I was using the Microsoft SQL Server
Management Studio Import wizard, right click on a
database, and choose the Tasks / Import Data... option to import from Access
database to SQL Server database (I chose Access as the source database and
SQL Server as the destination database)
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:31kv73dpnmkfh7srad18og9de1alac8c4t@.4ax.com...
> On Mon, 25 Jun 2007 07:44:31 -0500, "fniles" <fniles@.pfmail.com>
> wrote:
>> I was using the Copy wizard in the Microsoft SQL Server Management
>> Studio (right click on the database - Tasks - Copy Database)
>>Sorry, I was not clear. I was copying from an Access database to the SQL
>>Server database, and the datatype in the Access is Text(50).
>>The data in the Access database is 1.99305, and when it copied to SQL
>>Server
>>it became 1.9930 instead of 1.9931.
> I am still not clear, since the tool you say you used does not work
> with Access.
> When I open Microsoft SQL Server Management Studio, right click on a
> database, and choose the Tasks / Copy Database... option, the Copy
> Database Wizard opens. The message in that window is "You can use
> this wizard to move or copy databases from an instance of SQL Server
> 2000 or SQL Server 2005 to an instance of SQL Server 2005."
> Roy Harvey
> Beacon Falls, CT|||I am not familiar with the import tool in 2005, but I just played with
it a bit. I assume that the wizard did not assign decimal(12,4) as the
data type when Access has Text(50), so I assume the table definition
was done before the import.
It would appear that you have found a shortcoming of the import
process when crossing data types. The only workaround I can see would
be to import to a table that matches the original datatypes, and then
move the data to the tables with the new types using SQL INSERERT
commands. That would allow you to use explicit CONVERT and ROUND
commands.
Roy Harvey
Beacon Falls, CT
On Mon, 25 Jun 2007 12:54:00 -0500, "fniles" <fniles@.pfmail.com>
wrote:
>I am very sorry, I meant to say I was using the Microsoft SQL Server
>Management Studio Import wizard, right click on a
>database, and choose the Tasks / Import Data... option to import from Access
>database to SQL Server database (I chose Access as the source database and
>SQL Server as the destination database)
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>news:31kv73dpnmkfh7srad18og9de1alac8c4t@.4ax.com...
>> On Mon, 25 Jun 2007 07:44:31 -0500, "fniles" <fniles@.pfmail.com>
>> wrote:
>> I was using the Copy wizard in the Microsoft SQL Server Management
>> Studio (right click on the database - Tasks - Copy Database)
>>Sorry, I was not clear. I was copying from an Access database to the SQL
>>Server database, and the datatype in the Access is Text(50).
>>The data in the Access database is 1.99305, and when it copied to SQL
>>Server
>>it became 1.9930 instead of 1.9931.
>> I am still not clear, since the tool you say you used does not work
>> with Access.
>> When I open Microsoft SQL Server Management Studio, right click on a
>> database, and choose the Tasks / Copy Database... option, the Copy
>> Database Wizard opens. The message in that window is "You can use
>> this wizard to move or copy databases from an instance of SQL Server
>> 2000 or SQL Server 2005 to an instance of SQL Server 2005."
>> Roy Harvey
>> Beacon Falls, CT
>|||On Jun 23, 2:36 am, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL 2005. I have a table defined as decimal(12,4).
> I import data like 1.99305 to it, and the value in the database becomes
> 1.9930 instead of 1.9931.
> But, when I edit the the database like
> update myTable set price = 1.99305 where id = '12345'
> the value of the Price becomes 1.9931.
> How can I make the import program import the data to be 1.9931 instead of
> 1.9930 ?
> Thank you.
> CREATE TABLE myTable (
> id int
> Price decimal(12,4) NULL
> )
> 1.99305
Hi, I was wondering, instead of using decimal(12,4) why not use
decimal(20,5) or decimal(20,10)? Then you can round your data once it
is imported in the server and alter the column to decimal(20,4) later?