import from excel

• Best-330874

  SSCrazy

  Points: 2350

  More actions

  February 21, 2007 at 8:38 am

  #76661

  Hi,

     i need help on this.

  I have a worksheet in which i have the following fields.How can i do the import IN sql server 2005

  so that every field (District, School Name , e.t.c)in the excel worksheet must be a seperate column in the database table.What i am trying to ask here is can somebody guide me how to resolve this issue.

  Thanks

  Excel worksheet is as follows with the following fields

  Column A                       Column B                                                  Column C

                  

                  

  District:                        School Name:                                             Project #    

                  

  Architect:                     Gen. Contractor:                                         Project Mgr:    

                  

  MSBA Audit Firm #           Project Start Date:                                      Project End Date:       

                  

  Approved Square Feet      Allowed Cost per Sq. Ft.                               Actual Cost per Sq. Ft      

                

                      

   

• February 26, 2007 at 8:00 am

  This was removed by the editor as SPAM

• Terry-239890

  Ten Centuries

  Points: 1387

  More actions

  February 28, 2007 at 10:19 pm

  #692503

  What you need is design of your project.

  1. Create data model
  2. Specify requirements
  3. Consider how to provide data
  4. Then worry about how to use SQL 2005 for your project.

  First data model. According to your information supplied, I can see couple of tables in the model, ie District, School, Project, Contractor. My best guess for the relationships among the tables is as follows:

  1. District : School --> 1 : Many. One district can have many schools.
  2. District : Project --> 1: many. One district can run many projects.
  3. School : Project --> unknown. Could be Many : Many.
  4. Project : Contractor --> 1 : Many. One project can hire many contractors.
  5. ContractorType : Contractor --> 1 : many. One contracttype can be associated with many contractors.

  Based on this very simple relationship, I can provide tables as follows:

  SET NOCOUNT ON

  if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Contractor]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

  drop table [dbo].[Contractor]

  if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ContractorType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

  drop table [dbo].[ContractorType]

  if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[District]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

  drop table [dbo].[District]

  if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Project]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

  drop table [dbo].[Project]

  if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[School]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

  drop table [dbo].[School]

  Create table dbo.District (

  [ID] INT Not Null,

  [Name] Varchar(50) Not Null,

  [Addrss] Varchar(100) Null,

  [Description] Varchar(300) Null -- add some more as you require

  )

  Insert Into dbo.District

  Select 1,'Milky','1 Milky Way, Forbidden City, NY','District 1' union all

  Select 2,'SQL','1 SQL Way, Forbidden City, NY','District 2' union all

  Select 3,'Oracle','1 Oracle Way, Forbidden City, NY','District 3' union all

  Select 4,'MySQL','1 MySQL Way, Forbidden City, NY','District 4' union all

  Select 5,'DBASE','1 DBASE Way, Forbidden City, NY','District 5'

  Create table dbo.School (

  [ID] INT Not Null,

  [DISTICT_ID] INT Not Null,

  [Name] Varchar(50) Not Null,

  [Type] INT Null,

  [Addrss] Varchar(100) Null,

  [Description] Varchar(300) Null -- add some more as you require

  )

  Insert Into dbo.School

  Select 1,1,'1Milky',1,'1 Milky Way Forbidden City NY','School1 in District 1' union all

  Select 2,1,'2Milky',1,'10 Milky Way Forbidden City NY','School2 in District 1' union all

  Select 3,1,'3Milky',2,'20 Milky Way Forbidden City NY','School3 in District 1' union all

  Select 4,1,'4Milky',3,'30 Milky Way Forbidden City NY','School4 in District 1' union all

  Select 5,1,'5Milky',4,'40 Milky Way Forbidden City NY','School6 in District 1'

  Create table dbo.Project (

  [ID] INT Not Null,

  [DISTICT_ID] INT Not Null,

  [Name] Varchar(50) Not Null,

  [Type] INT Null,

  [MSBA_Audit_Firm_No] Varchar(50) Null,

  [Start_Date] DateTime Null,

  [Finish_Date] DateTime Null,

  [Description] Varchar(300) Null, -- add some more as you require

  [Approved Square Feet] Float Null,

  [Allowed Cost per Sq Ft] Money Null,

  [Actual Cost per Sq Ft] Money Null

  )

  Insert Into dbo.Project

  Select 1,1,'1Milky School Project',1,'#123456','2006-02-15',null,'1 Milky Way Forbidden City NY - School Project 1',1500, 1500,null  union all

  Select 2,1,'2Milky School Project',1,'#123457','2006-04-15',null,'10 Milky Way Forbidden City NY - School Project 2',2000, 1900,null

  Create table dbo.Contractor (

  [ID] INT Not Null,

  [PROJECT_ID] INT Not Null,

  [Name] Varchar(50) Not Null,

  [Type] INT Null,

  [Start_Date] DateTime Null,

  [Finish_Date] DateTime

  )

  Insert Into dbo.Contractor

  Select 1,1,'Pink',1,'2006-02-15',null  union all

  Select 2,1,'John',2,'2006-03-15',null  union all

  Select 3,1,'Paul',3,'2006-04-15',null  union all

  Select 4,1,'Ringo',3,'2006-05-15',null  union all

  Select 5,1,'David',3,'2006-06-15',null

  Create table dbo.ContractorType (

  [ID] INT Not Null,

  [Name] Varchar(50) Not Null,

  [Decsription] Varchar(200) Null

  )

  Insert Into dbo.ContractorType

  Select 1,'Project Manager',null  union all

  Select 2,'Architect',null  union all

  Select 3,'General Contractor',null 

  If you like to, I will continue on with this data mode later.