January 12, 2010 at 2:15 pm
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!
January 12, 2010 at 2:30 pm
What tool are you using for the import? The Import/Export Wizard? OpenRowset? SSIS?
The answer will depend on which one.
Pretty much, it will boil down to handling the column mapping, but the details differ depending on the method.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2010 at 2:39 pm
i'm using the enterprise manager import wizard
January 12, 2010 at 3:20 pm
I cannot confirm what you describe:
Table definition:
CREATE TABLE [dbo].[Table_1](
[z] [int] IDENTITY(1,1) NOT NULL,
[a] [int] NULL,
[int] NULL,
[c] [int] NULL,
[d] [datetime] NULL CONSTRAINT [DF_Table_1_d] DEFAULT (getdate())
) ON [PRIMARY]
Excel file (grid pattern):
ABC
1abc
211121
321222
431323
Result after using SSMS wizard:
zabcd
1111212010-01-12 23:15:22.647
2212222010-01-12 23:15:22.647
3313232010-01-12 23:15:22.647
I can't see any problem...
January 13, 2010 at 7:21 am
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
January 13, 2010 at 7:30 am
You could do it by first importing into a staging table and then use
something like this
Insert into table1
Select name address phone,getdate()
from staging table
OR
You can create a linked Server to the Excel file and query it and insert into the actual table.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
January 13, 2010 at 9:44 am
tkacyndra (1/13/2010)
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
Did you read my previous post?
My sample looks almost identical to the situation you subscribe and I don't have any problem. Would you mind providing a sample xls file together with the table definition and the eror message you get?
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply