ansaurus

Question

Answer 1

+1 A:

As bogeymin has already said - either get the data to CSV (or convert it easily from Excel) to manipulate it. If you're on Windows, then have a look at using Notepad++ to break apart the last column.

Or... (and I'd probably do this), insert it into the database as it is (even if you insert into a dummy field, not the one you eventually want to use), then use the string manipulation functions in your varient of SQL to make either update statements, or more insert statements (whatever you need). Cerainly, MS-SQL Server can do this using things like SUBSTRING, PATINDEX etc etc...

Tom Morgan 2010-09-29 13:00:46

Hi Tom, I've converted it to a CSV file, and now have it open in Notepad++ but I'm not sure what to do from here. Any ideas?

TaraWalsh 2010-09-29 13:10:58

are you wanting to import just the first (or second) name/email, create seperate insert statements for each name/email combination, or insert into seperate columns (name1,name2,email1,email2). I think, from your initial post, you're looking for the third: so I'll go on that assumption until I hear back from you. Let me play with it in Notepad++...

Tom Morgan 2010-09-29 13:32:40

hmm... grumble grumble at Notepad++! My Regex isn't what it should be, and i've run out of time really for this. But you could Regex looking for [a-z],[A-Z] and turn it into [a-z]','[A-Z]. This turns the values into two columns, but doesn't do anything about the columns. Hence my question about whether it's always names - as that makes it easy to add the extra cols.But... I still say it's probably easier to move it to SQL first. That way, you can deal with the problem in isolation, and target your string manipulation at just the emails and the names...

Tom Morgan 2010-09-29 13:56:16

Answer 2

+1 A:

What about when there are three e-mails/names? With shown data it should be easy to do

select replace(substring(substring_index(`Personnel`, ',', 1),length(substring_index(`Personnel`, ',', 1 - 1)) + 1), ',', '') personnel1,
       replace(substring(substring_index(`Personnel`, ',', 2),length(substring_index(`Personnel`, ',', 2 - 1)) + 1), ',', '') personnel2,
from `pubs_for_client`

The above will split the Personnel column on delimiter ,.
You can then split these fields on delimiter ( and ) to split personnel into name, position and e-mail

The SQL will be ugly (because mysql does not have split function), but it will get the job done.

The split expression was taken from comments on mysql documentation (search for split).

You can also

CREATE FUNCTION strSplit(x varchar(255), delim varchar(12), pos int) returns varchar(255)
return replace(substring(substring_index(x, delim, pos), length(substring_index(x, delim, pos - 1)) + 1), delim, '');

After which you can user

select strSplit(`Personnel`, ',', 1), strSplit(`Personnel`, ',', 2)
from `pubs_for_client`

You could also create your own function that will extract directly names and e-mails.

Unreason 2010-09-29 13:57:15

ansaurus

tags:

views:

answers:

Parse SQL file to separate columns

related questions