Exporting Excell File Into A table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Exporting Excell File Into A table

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2003-03-18 : 19:22:15

Hi there

Curently I have a table struture like this:
1) ID int identity
2) Name VARCHAR(50)
3) Description VARCHAR(50)

The spreadsheet (xls) has only 2 columns which are:
1) Name
2) Description
with some data on it.

Currently if I want to import data from excel to SQL quickly, I have to delete the column ID first (so it's only 2 column left in the database) then I import the data using a normal import data. Once the data imported, then I create new column ID and put the identity = YES. Once it saves, the data will have identity number automatically.

This solution is fine if the we don't need the data anymore.
But what if I want to appending the data. I can't just delete this column ID ... cause all the reference it might need for other table.

SO ... is there any solution to tackle this issue? I realise that there is a tick option for "Enable identity insert" when importing file. But I have never made this work.

Any tips or direction or hints .. I'm really appreciated.

Thanks in advance
Isadewa

Edited by - valdyv on 03/18/2003 19:57:04

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-18 : 19:48:35

I don't understand why you need to delete the column in order to transfer the data. You don't have to transfer all columns. Just create a DTS package that transfers the data from SQL Server to Excel. Modify the transformation and exclude the column.

Tara

dewacorp.alliances

452 Posts

Posted - 2003-03-18 : 19:55:52

HI Tara

I need to import data from excel to SQL .. not the other away around

Isadewa

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-18 : 19:58:40

Just transform the data. Use the activex script instead of the copy data column one.

EDIT: Actually I just tested this scenario fine. I created a table where the first column is the identity. I then imported a spreadsheet into SQL Server and didn't send anything to the first column (I used the copy data column transformation instead of the activex script one that I suggested previously). Since it is an identity column, SQL Server automatically generated the identity values for me.

CREATE TABLE [Table1] (
[id] int IDENTITY(1, 1) NOT NULL
[column1] varchar (255) NULL,
[column1] varchar (255) NULL,

Tara

Edited by - tduggan on 03/18/2003 20:09:33

Edited by - tduggan on 03/18/2003 20:10:08

dewacorp.alliances

452 Posts

Posted - 2003-03-18 : 20:15:25

Can you explain this a bit more : "Use the activex script instead of the copy data column one"

Cause I can't see any reference regarding the acticex script?

The only option that I got when import data are:

1) Copy table and view from the source database (THIS IS WHAT I SELECT)
2) Use a query to specify the data to transfer

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-18 : 20:18:31

Well you must be using the wizard. But just reread my post because I edited it probably while you were reading it. You can do this through DTS. Just create a simple DTS package that transfers data from Excel to a SQL Server table. Then in the transformation, just don't specify that the identity column gets any data. You can use the copy data column transformation for this.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-18 : 20:19:40

BTW, if you are using the wizard, just save it as a DTS package (do not run it), then go in and modify it.

Tara

dewacorp.alliances

452 Posts

Posted - 2003-03-18 : 20:22:09

quote:

Just transform the data. Use the activex script instead of the copy data column one.

EDIT: Actually I just tested this scenario fine. I created a table where the first column is the identity. I then imported a spreadsheet into SQL Server and didn't send anything to the first column (I used the copy data column transformation instead of the activex script one that I suggested previously). Since it is an identity column, SQL Server automatically generated the identity values for me.

CREATE TABLE [Table1] (
[id] int IDENTITY(1, 1) NOT NULL
[column1] varchar (255) NULL,
[column1] varchar (255) NULL,

Tara

Edited by - tduggan on 03/18/2003 20:09:33

Edited by - tduggan on 03/18/2003 20:10:08

I did this before ... it's complaining that it can not put a NULL value into identity column. Did you tick the "Enable Identity Column".

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-18 : 20:28:46

No I didn't, I kept the default settings. I don't understand why in your scenario that SQL Server doesn't insert the identity data for you. Could you post a picture of your transformation screen from the Transform Data Task Properties object? Here is what mine looks like:

Tara

Edited by - tduggan on 03/18/2003 20:33:14

dewacorp.alliances

452 Posts

Posted - 2003-03-18 : 22:00:07

quote:

No I didn't, I kept the default settings. I don't understand why in your scenario that SQL Server doesn't insert the identity data for you. Could you post a picture of your transformation screen from the Transform Data Task Properties object? Here is what mine looks like:

Tara

Edited by - tduggan on 03/18/2003 20:33:14

Here is the screen dump:

dewacorp.alliances

452 Posts

Posted - 2003-03-18 : 22:13:07

quote:

BTW, if you are using the wizard, just save it as a DTS package (do not run it), then go in and modify it.

Tara

Thanks TARA

It works .... I basically use DTS then disbale the Enable Identity thingy. And it exports fine. Any reason why the Enable Identity has to be tick off ? Logically this should be on.

Anyway ... thanks for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-19 : 12:24:24

I don't know why that option would have to be turned off. When I set mine up, by default it was not on. I wonder why yours was on.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources