question

yugmorf avatar image
yugmorf asked

Excel VBA import data from SQL server. Data imported to excel shows slight difference in values.

Having this issue with importing data from SQL server express to Excel 2010 (win7). Using excel VBA to execute the SQL. No problem getting the data into excel, but for some reason it seems to have ever so slight differences from the orginal data as contained in the database. I tried different SQL comands including without any calculation (eg select top 100), so problem isn't here. The database data is stored as real(4). The imported data once in excel arrives as text values (i don't know why) with slight errors introduced at around the eighth and ninth decimal place. For example, if the orginal data reads 0.7400, the same number imported to excel reads as 0.74000018 - a slight but annoying difference. My connection parameter statement follows. Thanks. With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array ( "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" & DBname & ";Data Source=SAX\SQLEXPRESS;Use ", "Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SAX;Use Encryption for Data=False;Tag with column", " collation when possible=False"), Destination:=Range(PositionWriteData)).QueryTable .CommandType = xlCmdSql .CommandText = StringToArray(Ssql) .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .SourceConnectionFile = "C:\Users\Guy\Documents\My Data Sources\SAX_SQLEXPRESS " & DBname & " " & DBtable & ".odc" .ListObject.DisplayName = "SQLdataTable" .Refresh BackgroundQuery:=False End With
excelerrorimport-datasql-server-express
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Scot Hauder avatar image
Scot Hauder answered
Change the real columns in the db to decimal. If you need numbers to be exact don't use float or real.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

yugmorf avatar image yugmorf commented ·
Thanks Scot. I tried changing to decimal but now both the database values (and those imported to excel) show just the rounded integer portion of the number. ie. following my example above, an original data entry of 0.7400 is now shown as 1. If anyone has another idea about what i might be doing wrong here, please share. Thank you!
0 Likes 0 ·
Scot Hauder avatar image Scot Hauder commented ·
When you change it to decimal you need to specify how many decimal places. If you leave it as decimal(18,0) it will round to 0 decimal places. The number on the left is the total number of digits (those on left and right of the decimal) and the number on the right is the how many decimal places you want to keep.
0 Likes 0 ·
yugmorf avatar image yugmorf commented ·
That worked a treat. Thank you!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.