I am trying to import five test records from an Excel 2000 spreadsheet. One of the fields has a data type of 'number'. All of the fields for all of the records import just fine, except for one record in this particular field. Has anybody had a similar problem? Why would the DTS engine not pick up the one record? This has happened with other spreadsheets, and the missing fields appear to be arbitrary. Is there a solution for this?

Thanks in advance,
Eric

Hi there,

I'm not sure if this is exactly the answer to your problem, but I had
something similar where I would import an excel spreadsheet with no column
names, when I would map it to the table I was importing to it would use my
first row as the column headers.

In short there is a temporary fix for this (if this is your problem) where
if you right click on the open package, go to diconnected edit, then go to
Connections -> Microsoft Excel 97-2000 -> OLE DB Properties -> Extended
Properties. Where Property Name = Value, change HDR=NO.

I have done this, and it worked for me, however, for some reason I can't get
it to stick. I'll change it once, save it, and then go abck later, I'll have
to change it again.

Eric Bauer

"eric" <anonymous (AT) discussions (DOT) microsoft.com> wrote

fields for all of the records import just fine, except for one record in
this particular field. Has anybody had a similar problem? Why would the
DTS engine not pick up the one record? This has happened with other
spreadsheets, and the missing fields appear to be arbitrary. Is there a
solution for this?

Here are a couple of Excel "Quirks" when using it in conjunction with DTS
which may help both of you.

Excel treats First Row of Data as Headers
(http://www.sqldts.com/default.aspx?255)

Excel Inserts Null Values
(http://www.sqldts.com/default.aspx?254)

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Eric Bauer" <ericbauer (AT) nospam (DOT) earthlink.net> wrote

Thank you both for your replies. Those tips will actually come in handy. However, for this particular problem, the spreadsheet does have column headings, and the data types in the problem field are the same. The values in the five records for that field are:

1080626
1037323
1039553
1087489
1055079

Only 10373235 will not import. The field for that record in the SQL table holds a NULL value. (I am using SQL Server 2000). This one has me stumped

And as a further problem, these five records had null values for this field when I originally imported a spreadsheet of over 1000 records. I pulled the five records into a separate, new spreadsheet and tried to import again. This time, four of the records imported. Only 10373235 did not

Thanks again
Eri