Importing Excel sheet with vertical and horizontal data

Question

Importing Excel sheet with vertical and horizontal data

pnr8uk

SSCommitted

Points: 1823
More actions
August 2, 2016 at 8:20 am

#328770

Does anyone know how I can get the values in the first column of the spreadsheet to pertain to the data in the second column.
The first column is a merged column with the data heading set vertically. The data column in the first column relates to all the data in the second column.
So the source file looks like the above however I want to load the data as:
ADVERTISING & MARKETING Radio
ADVERTISING & MARKETING Television
ADVERTISING & MARKETING Newspapers
ADVERTISING & MARKETING Magazines etc
Anyone any ideas or pointers to handling this kind of layout?
Many thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 245813 More actions · Answer 1

What happens when you try to read it in SSIS? Will it parse, or do you get an error?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

pnr8uk SSCommitted Points: 1823 More actions · Answer 2

Hi All

I found the solution right here on SQL Central, after lots of heartache, frantic Googling and numerous SSIS testing.

The answer is here:

We had this on this site however the topic heading was awful and I just happened to find it as I thought it was related to something else.

https://ericwisdahl.wordpress.com/2009/08/02/ssis-retain-values-from-previous-rows/

Phil Parkin SSC Guru Points: 245813 More actions · Answer 3

pnr8uk (8/2/2016)

Hi All

I found the solution right here on SQL Central, after lots of heartache, frantic Googling and numerous SSIS testing.

The answer is here:

We had this on this site however the topic heading was awful and I just happened to find it as I thought it was related to something else.

https://ericwisdahl.wordpress.com/2009/08/02/ssis-retain-values-from-previous-rows/%5B/quote%5D

Here is a clickable version of your link, for any lazy SSC readers 🙂

https://ericwisdahl.wordpress.com/2009/08/02/ssis-retain-values-from-previous-rows/[/url]

I knew about this technique, that's why I asked the question I did. But well done on tracking it down yourself.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.