?
Solved

Exporting SQL Server 2005 data to CSV:  Decimal values inaccurate (such as 4.000000000001 rather than just 4)

Posted on 2011-05-04
4
Medium Priority
?
883 Views
Last Modified: 2012-05-11
I am trying to export a view from SQL Server 2005 to a CSV file.  One of the fields, PriceBreakControllingUnitPrice, is a float data type, which contains price information that is almost always 2 decimal places.  When I execute the view, SQL Server Management Studio is showing no more than 2 decimal places.  When this is exported to CSV, a value such as 8.65 becomes 8.6500000000000004 or 4.84 becomes 4.8399999999999999 .  How do I fix this?

I have made multiple attempts at different CAST and CONVERT functions, but it has no effect.

I run into this problem with Access databases on occasion, and rounding functions always take care of the issue, but not this time with SQL Server.

Below is the SELECT statement without any conversion added, but this info probably is not really needed for this question.

SELECT     SubQuery1.PriceBook, SubQuery1.CustomerID, dbo.FS_CustomerItem.CustomerItemNumber, dbo.FS_PriceBreak.PriceBreakControllingUnitPrice,
                      dbo.FS_PriceBreak.PriceBreakQuantityFrom, dbo.FS_PriceBreak.PriceBreakQuantityTo, dbo.FS_PriceBreak.PriceBreakPriceBasis
FROM         (SELECT     PriceBook, CASE WHEN [PriceBook] = 'XX 10' THEN '123456' END AS CustomerID, PriceBookItemNumber, ItemKey,
                                              PriceBookItemKey
                       FROM          dbo.FS_PriceBookItem
                       WHERE      (PriceBook IN ('XX 10'))) AS SubQuery1 INNER JOIN
                      dbo.FS_CustomerItem ON SubQuery1.CustomerID = dbo.FS_CustomerItem.CustomerID AND
                      SubQuery1.ItemKey = dbo.FS_CustomerItem.ItemKey INNER JOIN
                      dbo.FS_PriceBreak ON SubQuery1.PriceBookItemKey = dbo.FS_PriceBreak.PriceBookItemKey
0
Comment
Question by:HomerTNachoCheese
  • 4
4 Comments
 
LVL 6

Author Comment

by:HomerTNachoCheese
ID: 35691828
For cast and convert, I have tried to convert to decimal(20,2), decimal(12,2), decimal(12,4) - all of which have no effect.  I also tried casting as integer, multiplying by 1000, casting that result as decimal(12,4), and dividing by 1000 - still did not work.

I will try some other data formats.  Resulting file must be a CSV file.
0
 
LVL 6

Author Comment

by:HomerTNachoCheese
ID: 35691943
using numeric rather than decimal appears to work in testing.  Now I will try to create the SSIS package and make sure all is still well.  Also used .txt format instead for the test, which should also be an OK substitute for CSV (I doubt it matters to SQL Server).
0
 
LVL 6

Accepted Solution

by:
HomerTNachoCheese earned 0 total points
ID: 35692123
CSV vs TXT for the output file format actually makes a difference.  If I use CSV, I get extra decimal places and inaccurate numbers.  If I use TXT, then I get the expected results.
0
 
LVL 6

Author Closing Comment

by:HomerTNachoCheese
ID: 35692147
I figured it out.  The problem is not what I thought - conversion was probably not the issue at all, but instead it was a bug (cough... feature) of Microsoft SQL Server 2005.  Maybe there is a reason why exporting to CSV or TXT gives two different results.  Since the database that needs this data will accept either TXT or CSV, this is OK for me.  

If someone else reading this has the same problem and requires CSV, then maybe the data can be first exported to TXT then renamed to CSV.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Screencast - Getting to Know the Pipeline

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question