February 10, 2011 at 6:33 am
Greetings. I am using OPENROWSET to import an Excel 2003 file into a staging table every day. One of the columns is a text field that displays the dates a roadblock was entered for each row. The problem is that sometimes there are multiple dates in the field that seem to be concatanated with a function on the DB2 server that generates the file. So, my OPENROWSET shows NULL for all rows. If I copy one of the cells that has multiple dates in it into the first row, it selects the entire column with no problems. For that matter, if I put any value in the first row it selects the rest with no problems. it seems as if SQL Server is deciding that the column should be a date format, but when it encounters an instance of two dates in one cell, it does not know what to do so it throws them all out. I have tried using CAST and CONVERT to no avail. I have put the file in c:\DateTest.xls, and have attached a copy of the file with only the problem column included. I sure would appreciate any insight. Thank you.
SELECT
[Road Block Date]
FROM OPENROWSET
(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=Yes;IMEX=1;Database=C:\DateTest.xls',
'select * from [sheet1$]'
)
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
February 10, 2011 at 12:29 pm
IIRC, this issue is caused by the Jet 4.0 / EXCEL registry setting for the TypeGuessRows parameter.
See http://support.microsoft.com/kb/189897 for details.
February 11, 2011 at 6:17 am
Thank you so much, that worked like a charm!
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
February 11, 2011 at 10:41 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply