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 |
|
clweeks
Starting Member
1 Post |
 Posted - 2007-06-29 : 16:47:14
|
| Hi all, I've just started playing with data importing from Excel.Since I know how to do linked servers, that's where I started. I created a link, querried the spreadsheet and got some strange results where data appearing in the spreadsheet returns as NULL in QA.So, I noticed that I could use OPENDATASOURCE and OPENROWSET and tried each of them. They're returning the same data (in different column orders) also with strangely intermittant NULL values instead of the data that I see in the source spreadsheet.I bet that this is something easy once you know what's going on. Anyone know what's going on?Not that I think it'll matter, but the queries I'm using for these three cases are:select * from EXCEL_A...EPIQ202$ where SUBSCRIBER_ID = '50664582103'select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=e:\EPIQ202.xls;Extended Properties=Excel 8.0')...EPIQ202$select * from openrowset ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:\EPIQ202.xls', 'SELECT * FROM [EPIQ202$]' )Thanks a bunch for your time,Chris |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-30 : 20:34:05
|
| Seems excel column has mixed values of number and characters so sql doesn't know which data type the column is. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-07-07 : 04:22:12
|
| If the cell has both numbers and characters, then you need to prefix single quote for all your numbersMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
data errors querying to .xls