March 2, 2010 at 6:30 am
Greetings all. I am having a problem with OPENROWSET with an Excel file. One of the columns is a code that is, in some cases, four numbers, like 8705, and in other cases it is four letters like 'ABCD'. When I select from the file using OPENROWSET, the fields with the numeric codes return NULL in all cases, but the letter codes display correctly. I have tried formatting the Excel file as text and general, as well as using CAST and CONVERT to varchar for the numeric codes, but nothing I am trying can make them display. Has anyone else experienced this issue?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
March 2, 2010 at 6:41 am
This is a really very old issue with excel and data providers. To overcome, you have to set the extended property "IMEX" of provider to value of 1. This property tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text.
For e.g.
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
--Ramesh
March 2, 2010 at 8:09 am
Thank you Ramesh, that solved my problem.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
March 3, 2010 at 10:48 am
Does this setting also take care of using DTS to import spreadsheets in situations where you have a column that looks like this:
A
___________
A
AB
ABCD
ABCDE
ABCDEF
Or maybe the same thing with numbers, don't have an exact example off the top of my head.
If you attempt to 'import' that spreadsheet into your database as a table in SQL 2000, you'll get a column that is only wide enough for the first value (or maybe it samples the first x values, can't remember). The gist of it though is that to get the spreadsheet to import properly I've always had to add a bogus line at the top of my excel sheet that ensures that the data type chosen is wide enough. I don't believe specifying column types matters here either. So in the above example, to fix the problem, I've always had to add a row at the top so that my spreadsheet really looked like:
A
___________
AAAAAAAAAA
A
AB
ABCD
ABCDE
ABCDEF
Then once the spreadsheet was imported I'd go in and delete the first row. It works, but it's a pain and it'd be nice to have a real solution.
March 3, 2010 at 2:06 pm
I don't think I've ever experienced that problem Seth. I usually save Excel files as text files then use bulk insert. That is cumbersome though, when I only need to do a one time upload. After messing around with OPENROWSET for the past few days, I think it is my new first choice over DTS or bulk insert for one time deals. However, I have already experienced this one problem, and it was easily overcome, so there could be more just waiting to manifest themselves.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
March 17, 2010 at 7:35 am
IMEX=1 seting after...
xp register modify
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]
importmixedTypes = Text
TypeGuessRows = 10000 // default 8
March 21, 2010 at 10:25 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply