March 9, 2010 at 12:54 am
Hi there
I have a column C1 in Excel which contains data as follows:
C1
------
111-222-3333
Mark Ben
Steve Ryn sryn@client.com
222-333-4444 michael douglas
In the above sample data, the same column C1 contains phone numbers, email address and also name of persons. And few times the combination of phones and names and email address.
Now the challenge is split the data from this column to different columns say Phone, Email, EmployeeName. The source data is in Excel Format.
Phones
--------
111-222-3333
222-333-4444
Emails
--------
EmployeeNames
-------------------
Mark Ben
Steve Ryn
Michael Douglas
Could somebody help me in getting this fixed. I think scripting in SSIS or T-SQL mechanism (by moving the excel data to some staging sql server table and from there we can, if possible) can be used to handle the situation but I am not much into scripting. If macros in excel are capable of doing it, I can opt for it as well.
Thanks in Advance
Sunny
March 9, 2010 at 1:56 am
A possible approach is to use a scripting component to undouble the column. In other words, make sure that each data item has its own row (so no rows with name and telephone number or name and e-mail). You can do this by searching for spaces and the characters - and @, but it will be hell to debug 🙂
For example, what with a name like Jean-Paul Gaultier? You could test for numeric substrings to find the telephone numbers.
Assign another column with an indicator, such as e for e-mail, n for name and t for telephone number. Then you can sort the rows very easily with a conditional split and send each flow to a different OLE DB Destination. (or you could do everything in scripting, but that makes everything harder to maintain).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply