Get two pieces of data out of one column

Question

Get two pieces of data out of one column

Jody Claggett-376930

Ten Centuries

Points: 1152
More actions
September 28, 2008 at 12:50 pm

#124822

I have an excel file that has a unique ID column. I need to get the ID out of the column, but some of the rows also have two ID's in them (see examples below). This file is written by none database types and they don't have a workaround to put the ID's on separate rows.
My question is, what tools in SSIS can I use to parse this file to get both ID's as well as the ID's where there is only one ID per row? The complexity is that I only know the string length of the ID's. Sometimes the columns will have & and other times AND. The columns can also have additional spaces, basically this is manual entry and not very good entry at that.
Ideally I would like to run an SSIS package to spit out the ID's onto separate rows. Using the example below I would have 11 separate rows in a database table after the package is run.
Excel Data source:
[IDColumn]
ID1
ID2
ID3
ID4 & ID5
ID6 AND ID7
ID8 and ID9
ID10 & ID11
_______________________________
[font="Tahoma"]Jody Claggett
SQL Server Reporting Analyst[/font][/size]

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

I've only got the Express Edition, but the logic to get the data out should be easily adaptible.

you want to use a CASE statemnt for each of the variables; "&", "and" or any others...i don't think you need to repeat for upper and lower case.

try this:

SELECT DISTINCT * FROM (

SELECT

CASE WHEN CHARINDEX('&' ,[IDColumn]) > 1 THEN LTRIM(RTRIM(SUBSTRING([IDColumn],1,CHARINDEX('&',[IDColumn])- 1)))

WHEN CHARINDEX('and',[IDColumn]) > 1 THEN LTRIM(RTRIM(SUBSTRING([IDColumn],1,CHARINDEX('and',[IDColumn]) - 1)))

ELSE [IDColumn] END AS [UNIQUEIDColumn],

#Excel.*

FROM #Excel

UNION

--had to add the length of the search item + 1

SELECT

CASE WHEN CHARINDEX('&' ,[IDColumn]) > 1 THEN LTRIM(RTRIM(SUBSTRING([IDColumn],CHARINDEX('&',[IDColumn]) + 2,30)))

WHEN CHARINDEX('and',[IDColumn]) > 1 THEN LTRIM(RTRIM(SUBSTRING([IDColumn],CHARINDEX('and',[IDColumn]) + 4,30)))

ELSE [IDColumn] END AS [UNIQUEIDColumn],

#Excel.*

FROM #Excel) X

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jody Claggett-376930 Ten Centuries Points: 1152 More actions · Answer 2

Looks like this is going to work out perfectly. On first pass through it looks like it caught all of the ID's and put them on seperate lines like I was needing.

Thanks Lowell!

_______________________________
[font="Tahoma"]Jody Claggett
SQL Server Reporting Analyst[/font][/size]