Importing values from excel - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Importing values from excel

Author

Topic

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-01-12 : 16:01:45

Hello,

i need to populate one of my tables with values from an excel spreadsheet.

of the fields is a self incrementing key field, which is not in my excel
and another is "date last modified" which has a default of "getdate()" it's also not in my spreadsheet.

when i try to import data with those two fields missing, i get errors, am i supposed to hard code those fields into my excel? is there a work around?

Thanks!

dattatreysindol
Starting Member

20 Posts

Posted - 2010-01-12 : 22:51:58

Hi There,

I am assuming that you are using SSIS for importing the data into your SQL table. If both the incrementing column and the "Date Last Modified" fields are not there in your excel spreadsheet. Then since you mentioned that Key field is self incrementing which means its defined as an Identity column in the table. For the "Date Last Modified" field in the table definition add a default to this column as GETDATE() something like the below:

CREATE TABLE #TestTable(
KeyColumn INT IDENTITY(1,1) NOT NULL,
Column1 DataTypeX, -- These are columns from your excel spreadsheet
Column2 DataTypeY, -- These are columns from your excel spreadsheet
LastModifiedDate DATETIME DEFAULT GETDATE()
)

Hope that helps!

Dattatrey Sindol
http://mytechnobook.blogspot.com/

This information is provided "AS IS" with no warranties, and confers no rights.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 01:10:28

quote:
Originally posted by xrum

Hello,

i need to populate one of my tables with values from an excel spreadsheet.

of the fields is a self incrementing key field, which is not in my excel
and another is "date last modified" which has a default of "getdate()" it's also not in my spreadsheet.

when i try to import data with those two fields missing, i get errors, am i supposed to hard code those fields into my excel? is there a work around?

Thanks!

how are you trying to import? ssis or openrowset or bulk insert?

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-01-13 : 09:20:50

i'm using enterprise manager import wizard to import, here is an example of what's going on.

my table, for example, has the following fields:
id (self incrementing key field)
name
address
phone
date_modified (has a default value of getdate())

my excel spreadsheet only has
name
address
phone

how can i import it (hopefully using the import wizard) not to have to manually add the ID and the date_modified to the excel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 09:39:16

quote:
Originally posted by xrum

i'm using enterprise manager import wizard to import, here is an example of what's going on.

my table, for example, has the following fields:
id (self incrementing key field)
name
address
phone
date_modified (has a default value of getdate())

my excel spreadsheet only has
name
address
phone

how can i import it (hopefully using the import wizard) not to have to manually add the ID and the date_modified to the excel

you can selectivey include/exclude columns in transformations part of export import wizard. include only columns you want

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources