importing Excel to Sql Server

[GeeWond3r]

I'm able to append excel data into Sql Server by 'import data', but I'm stuck at the point where I can't import when there exists an Identity in the existing sql table. I've "ignored" the field, but that didn't work. How would I go about resolving this dilemma?

 

[JayKusch]

When Importing, you will come to a screen called "DTS Import/Export Wizard". Should have column headers with "Source", "Destination" and "Transform". Click on the box below the Transform header. If the "Enable Identity Insert" box is checked ... UNCheck it. Should be good to go!

Thanks

J. Kusch

 

[GeeWond3r]

tried that, didn't work. It gave me the error "Cannot insert the value NULL into column 'someID', table 'Southwind.dbo.myTable'; columns does not allow nulls. INSERT fails."

myTable has a primary identity column 'someID' that automatically increments itself, plus a 'firstname' column. I want to insert excel data into db 'firstname' only, and the db should generate the next 'someID' # for that record. It shoud be a simple process, but i dunno... .

 

[MDXer]

If the identity column in your SQL DB does not allow null values into the identity column(which it shouldn't) then when you enable identity insert it will fail for records in excel that have no ID. One way to work around this would be to load your excel records into a staging table that was a column for column match with you excel file. Load the records from excel into this table. Next step would be to insert either all records where Identity in excel is null or enable identity insert and insert those with ids then turn off identity insert.

Either way you may ben in for some trouble down the line.
What happens when you have 1000 rows in your SQL table all with IDs and then you try to import an ID of 50? If the id isn't being used for anything your probably better off not importing the ID column from excel and allowing SQL to assign the ID or don't make the SQL Column and Identity column.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[GeeWond3r]

this sucks!

 

[JayKusch]

SO ... your excel sheet that you are importing only has 2 columns ... SomeID and firstname ... Correct?

Thanks

J. Kusch

 

[GeeWond3r]

2 columns... keeping things simple!

In my excel sheet, there are more than 2 columns. But I'm only importing one column (a column of firstnames let's say) into the database and at the same time, the db shud auto-generate the someID column of its table.

 

[MDXer]

if your only importing 1 column then why bring in the excel ID column. import only the column(s) you need and ignore the excel ID column and allow sql to generate the ID.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[GeeWond3r]

I'm NOT bringing an excelID column. I did ignore it, but that didn't work. Read my 1st posting!

 

[JayKusch]

OK ... I am TOTALLY confounded! Here is what I did as a test to mirror what I have read from your posts so far.

I created an excel worksheet w/ 3 columns for good measure.
Col1 = MyID
Col2 = LastName
Col3 = Stuff

I entered 4 records in the excel to look like this

1 Smith ExtraStuff1
2 Arnold ExtraStuff2
3 Meyers ExtraStuff3
5 Kusch ExtraStuff4

I then create a table in SQL w/ 2 Columns:
Col1 = TableID (Set as a seed value of from 1 by 1)
Col2 = LastName VarChar(50)


I then entered 7 records in. I just entered the last name because the identity seed created the TableId as each record was added.

After the additions my table now has seven records and the max TableId is 7.

I ran the Import wizard to import the excel spreadsheet into my table.

When the "Select Source Tables and Views" screen came up I chose "Source" as "Sheet1$", I set the "Destination" to be my target table ...

I then hit "Transform" and in the mappings I set under "Source"

F1 to <ignore>.
the second item under "Source" I set to LastName
the third item under "Source" I set to <ignore?

*** I also UNCHECKED the "Enable Identity Insert"

I then ran the Import and BANG!!! the three new records are added and my max TableID in now 10.

Does this mirror the EXACT steps you are following.





Thanks

J. Kusch

 

[Shal2]

I think I came across this problem, but my table was empty to start with, and I was getting the same error. What I did was, add a test row (just one) to the table. Then the DTS import worked fine.

Hope this helps,
Shal