creating multiple tables from one excel sheet

[tfstom]

I have a Sheet that needs to be put into 2 separate table

The sheets has the following columns (essentially):

id,desc,date1,date2,date3,date4

The sheet would look like:

ID   Name             Date1           Date2           Date3           Date4
1     Mark           12/15/05        10/2/05        9/7/04        10/12/05
2     Joe              08/15/04        02/03/05      9/12/04       08/15/05
3     Stan            12/01/04        09/20/05      3/15/05       05/21/05
6     Rick            01/01/05        11/12/05      4/12/04       07/15/05

I need to put "id,name" in one table and "id,dateDesc (date1,date2,etc -
would be in header),date"


Can this be done from DTS?

Thanks

Tom.

 

[donutman]

If you can't find a direct way, you could start by importing it into a single table, then run another procedure to insert the appropriate data to the two tables you want.
-Karl

 

[tfstom]

That is sort of what I did.

I was trying to make a more elegant way and more efficent way of doing it.

I know that I can select the columns that I want to copy. I wanted to set
up 2 tables to normalize the Excel flat file.

Company  Shift   StartTime  EndTime  Hours   NewYears     Memorial     July4     Labor

INEZ      1      6     14.5      8     03-Jan-05     31-May-04   05-Jul-04   06-Sep-04

INEZ     5     5     15.5     8    03-Jan-05     31-May-04     05-Jul-04  06-Sep-04

INEZ     13     6     18     8    03-Jan-05    31-May-04     05-Jul-04    06-Sep-04

INEZ     16     14.5     23     8   03-Jan-05   31-May-04     05-Jul-04    06-Sep-04

The above table is a portion of the actual excel sheet. I copy the whole sheet into one temporary file and then do a series of creates and inserts to create the 2 tables I need.

This is done in my last task of my DTS package.

if object_id('holidaysDetail') is not null
begin
 drop table vdw.dbo.holidaysDetail
end
go
if object_id('holidays') is not null
begin
 drop table vdw.dbo.holidays
end
go
create table vdw.dbo.holidaysDetail (Company varchar(20),Shift int, Name
varchar(30),ActualDate smalldatetime)
go
create table vdw.dbo.holidays (Company varchar(20),Shift int,StartTime
float, EndTime float,Hours float)
go
insert vdw.dbo.holidays (Company,Shift,StartTime,EndTime,Hours)
select Company,Shift,StartTime,EndTime,Hours from holidaystemp
go
insert vdw.dbo.holidaysDetail (Company,Shift,Name,ActualDate)
select Company,Shift,'NewYears', NewYears from holidaystemp
go
insert vdw.dbo.holidaysDetail (Company,Shift,Name,ActualDate)
select Company,Shift,'Memorial', Memorial from holidaystemp
go
insert vdw.dbo.holidaysDetail (Company,Shift,Name,ActualDate)
select Company,Shift,'July4', July4 from holidaystemp
go
insert vdw.dbo.holidaysDetail (Company,Shift,Name,ActualDate)
select Company,Shift,'Labor', Labor from holidaystemp
go
insert vdw.dbo.holidaysDetail (Company,Shift,Name,ActualDate)
select Company,Shift,'Thanksgiving', Thanksgiving from holidaystemp
go
insert vdw.dbo.holidaysDetail (Company,Shift,Name,ActualDate)
select Company,Shift,'ChristmasEve', ChristmasEve from holidaystemp
go
insert vdw.dbo.holidaysDetail (Company,Shift,Name,ActualDate)
select Company,Shift,'Christmas', Christmas from holidaystemp
go
insert vdw.dbo.holidaysDetail (Company,Shift,Name,ActualDate)
select Company,Shift,'NewYearsEve', NewYearsEve from holidaystemp
go
Create Unique Clustered Index PK1_Holidays on Holidays (Company,Shift)
go
Create Unique Clustered Index PK1_HolidaysDetail on
HolidaysDetail(Company,Shift,Name)
go

I was just curious if there was a better way to do this.

Thanks,

Tom.