| Author |
Topic |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2004-01-08 : 11:47:25
|
| Hi. I query several XLS spreadsheets via linked server out of necessity, I have no choice in the matter.I have seen some weird stuff happen in the past, but this new quirk is the strangest yet.When I query this linked xls for a SINGLE record (ID = 64.1) based on the Excel ID column, which SQL Server 7.0 sees as a FLOAT data type (and excel has typed as NUMBER, two decimal places), ZERO results are returned every time the ID in question is a DOT ONE, i.e. 64.1, 65.1, 66.1, 67.1. However if I query for a single DOT TWO record (ID = 64.2), no problems, records are returned.I can get sql to return 64.1 if, for example, I do a BETWEEN 64 and 65, or a <= 64.2. However <=64.1 returns NO records!So, SQL Server does see 64.1 as a number, it just seems to be ignoring it when I look for it via ID = 64.1.Any ideas?thx. |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-01-08 : 12:27:31
|
| What font of those xls sheets? |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-08 : 12:40:29
|
| ok...so it's linked and you have no choice..can't part of you're process be to import it in and work with it there...been seeing LOTS of quirky things latelyBrett8-) |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-01-08 : 13:23:26
|
| font of the xls is arial 10pt. xls sheet is set to display at 75% of original size.re: importing into sql, have tried in the past via DTS but sql, in some cases, randomly changed values, so i gave up trying.importing is an attractive option to me, however. Any suggestions on how to import the XLS into sql while being certain that SQL is not randomly changing data values, i.e. it will leave the XLS as-is?thx |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-01-08 : 13:45:51
|
| Think the 64.1 is only displayed as 64.1 but stored as e.g. 64.099999999999.Or (better) if SQL Server considers it as FLOAT then he has rightfor rounding error...... |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2004-01-08 : 13:55:58
|
| Is 64.1 the first or last value in the ID column on the spreadsheet? It could be that Excel SQL Server has defined rows for the linked Excel table and this value is not within the defined rows. I have not used Excel spreadsheets as a linked table before, but I have found this to be a problem when using ODBC to read from an Excel spreadsheet with an ASP application.As far as importing into Excel and keeping the values from changing, the only way I have found to accurately do this every time is to save the Excel spreadsheet as a deliminated text file and then import into SQL Server.I hope this might help. |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-01-08 : 14:38:59
|
| stoad,correct, but, (I forgot to mention), some of the DOT ONES do come back normal, i.e. 123.1 will be returned when queried and it is same data type in Excel, nothing different.in the past one problem I had was spaces before and/or after the IDs, but took care of that with RTRIM/LTRIM. |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-01-08 : 14:47:45
|
quote:
some of the DOT ONES do come back normal, i.e. 123.1
It's absolutely does not matter. "Tomorrow" and the 64.1 can go "correct". |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-08 : 15:00:51
|
| Create a work table where a the columns are varchardelete from it, then import it...see what you get and tell us...we can then go from thereBrett8-) |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-01-08 : 15:42:14
|
| stoad,I think your last comment hits the nail on the head, so to speak.I can see that the time has come to revisit the idea of importing into SQL as text and getting away from Excel linked servers altogether. I think I have enough ammo to push this through, exhibit A being the current example where a record which should be part of the query result set is left out for no reason other than Excel decided to hiccup.thx. |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-01-08 : 15:47:18
|
Ah.. steelkilt.. that's OK.. (Just was going to post this:I checked it. This is result of select * from ex...[sheet1$](the 1st column in xls of type NUMBER (two decimal places),the 2nd - of TEXT type and the 3rd - GENERAL):a b c----------------------- ------------- ---------------------- 64.099999999999994 64,1 64.099999999999994123.10000000000001 123,1 123.100000000000010.10000000000000001 0,1 0.10000000000000001 And what now?...... float is float......... |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-08 : 15:58:00
|
quote:
Originally posted by X002548
Create a work table where a the columns are varchardelete from it, then import it...see what you get and tell us...we can then go from thereBrett8-)
Good idea Stoad!Brett8-) |
|
|
|
|
the record is there, but SQL can't find it!