why the identity values don't enter while importing data from Excel

why the identity values don't enter while importing data from Excel

Post by Jenn » Sat, 09 Nov 2002 22:03:06

I am trying to import an Excel file filled with data to a
table in SQL Server. The 1st column of my table in the DB
is an identity, so I don't include this column in my Excel
file and assume that when the import happens, it will
trigger the addition of the idetity values and insert the
right values automatically, like what happens when we
use "insert" statement manually. But it does not work this
way! The import utility is smart enough to figure out the
one-to-one mapping for all columns, and "ignore" the first
column. But when the import starts, it fails and complains
the 1st column in the db can't be inserted NULL.

HOw can I work around this?

Thank you in advance.


why the identity values don't enter while importing data from Excel

Post by Bill Hollinshead [MS » Sun, 10 Nov 2002 02:16:22

Hi Jenny,

It sounds like you are actually running into
http://support.microsoft.com/default.aspx?scid=KB;EN-US;236605. You can
confirm this by creating another table in SQL Server with the same number
of columns as the current table, but using a character data type (such as
varchar(33)) instead of the current table's data types, and by ensuring all
of the character columns (on this new table) allow nulls. If an import into
this new table has nulls in columns for which the original table does not
allow nulls (check the original table via sp_help or via scripting a create
table to a new window), then the above article is applicable.
Alternatively, you can inspect the Excel spreadsheet for mixed data types,
and if seen then the above article is applicable. If the first row of the
spreadsheet is a header row, then make sure to specify that the first row
is a header. I think you are using DTS Import Wizard, but if not (are you
using DTS Designer or a linked server?) and/or if you are still uncertain
about what to do, please provide us with the name of tool being used (and
the DTS package if DTS is being used) , a sample Excel spreadsheet with
data that fails to import, and the schema (the create table statement) for
the destination SQL Server table. I am afraid that most spreadsheets allow
mixed data types while most databases do not <g>.


Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.