Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dachish
Starting Member
12 Posts |
 Posted - 2008-12-11 : 09:36:36
|
| All,I am trying to use a pretty simple Excel sheet as a data source in VS2005. It seems pretty straight forward but the results I'm getting seem very random and/or unreliable.Basically what I have done is taken the Excel sheet (which is about 7 columns and 200 rows) and created an ODBC connection under System DSN. That part is of course straight forward...not many options I can really take, but I added the connection, selected the Microsoft Excel Driver (I've also tried the "Driver so Microsoft Excel") and browsed to the .xls file, set the version to 97-2000 (which may be a problem since it's in 2003 format? but it looks like that drive supports up to 2007 since it says it supports .xlsx), made a name for the connection and then left the rest alone.So that seemed to work at first. I went into VS2005, made a new data connection, and it showed up. I selected it, BAM, it looked good. The table showed up, all the columns showed up under the tables, etc. The only oddity was the table, which was named "Depreciation" is now something like `'depreciation'`$But since it's only one table, I left it as it was.When I try to query a date, the results are amazingly inaccurate.For example, there are only about 4 results that are 2008. If I query "where fieldname > 01-01-2008" it will return 32 results. If I query "where fieldname > 11-01-2008" (which would only be ONE result if it was accurate) it gives me 36 results.So I've tried various things since then. I went back in the Excel sheet and made sure the date fields were "dates" instead of just text. I've recreated the file and retyped the first few columns and 5 rows, thinking maybe some hidden field was causing a problem. When I did that, I couldn't even get any data to show at all. I recreated the ODBC connection but when I try to query, the data connection is totally blank (which happens a lot and I can't find a rhyme or reason).So there might be a very specific setting I'm missing.Any idea? = ) |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-18 : 06:15:16
|
| In your date query - incase it is a format / region issue change date to long form, e.g. "12 January 2008" |
 |
|
|
|
|
|
|
|
Excel as a data source