Removing a Excel header row

• drew..

  SSC Veteran

  Points: 254

  More actions

  May 17, 2011 at 11:45 am

  #250337

  Hi all

  Well, I don't know where to start with this one... Here goes anyway...

  I am importing an Excel file which has a header row. The header row's columns are in a Custom format (d mmm).

  For example (see below for the header row and first line of data):

  Resource NameProject 2 May

  John Smith#Resource Non Working Time.mpp1.0 d

  However, when clicking on the actual '2 May' cell within Excel it shows in the formula bar '02/05/2011'.

  Using SSIS, I use a Excel Source task and a OLEDB Destination task to import from Excel and export the data to a database table.

  Here is the problem, What is showing in the Destination database table for the headers after exporting are:

  Resource Name Project F3

  Why it isn't showing '2 May' or better still '02/05/2011' i do not know. I need for the header to show the actual date (02/05/2011) so to be able to use this column in a SQL query further down the line.

  Is there something I can do to convert the column, if so how? Can anyone help please as I am clueless on this one???

  Thanks in advance.

• steveb.

  SSC-Forever

  Points: 46733

  More actions

  May 17, 2011 at 1:11 pm

  #1326083

  what you could do is write a query to extract the data from excel rather than connecting to the worksheet directly,

  if you write a a select * query instead of connecting to the worksheet does the column header come back correctly or does is say F3?

  If it says F3 you could re-write the quyery to use an alias for that column like

  F3 as '2 May 2011'

• drew..

  SSC Veteran

  Points: 254

  More actions

  May 18, 2011 at 2:11 am

  #1326262

  Hi steveb

  I am not re-write the query using an alias for the column as each week when the package is run there will be a different date present in that column. If I used alias' I would have to write a new alias each week.

  As for using a SELECT to extract the data it has the same result as previously, i.e. the column has F3 in the header row.

  I can only see the Custom format is causing problems. I cannot change the Custom format so I need a workaround. Maybe using Conditional Splits and Derived Columns. I'm clueless on how to solve this one.

  Help???????

  Thanks.