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.

Hi Thomas,

You can use the Transformation Tab of the DTS Task to select the columns
that you want to import in the SQL Table from the Excel Worksheet

For the Columns that you want to skip, select the transformation and click
on Delete.
This will make sure that the columns that are not selected, will not be
imported in the SQL Table.

HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

Ashish Ruparel [MSFT]" <v-ashrup (AT) online (DOT) microsoft.com> wrote

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.