Break Imported Full Names Into Multiple Fields
From the Inside Sesame Help Desk
I have a supplier who sends me a customer list as an ASCII delimited text file that I want to import into my application. I know how to set the Import Spec to make the data land in the correct fields. The problem is that the formatting of the most important field - the customer's name - is inconsistent with itself as well as with the database. It's always in one field - sometimes in a last name then first name format, and sometimes the other way around. I've even found a few records that have only have a last name. I want the data to wind up in two fields - a LastName field and a separate FirstName field. I can't change the way the data is supplied. Do you have any ideas that could help me?
Connie
You've defined the problem well. Even though the data isn't consistent in the way you'd like, it's at least consistent enough so you can work with it. If the name is supplied only as a last name, it will have no commas or spaces. If the name has a comma in it, it's in the last name, first name form. And if there's a space but no comma, it's in the first name last name form.
Now that we know this, we can import all the data into the LastName field, then run the following Mass Update on the records. This update program has a typical If Then Else structure. You have to ensure it runs in the correct sequence. First you want to find if it's just the last name (no commas, no spaces). If it is, you leave it as the last name and stop. (The else statements don't execute.) If that isn't the case, then you see if it contains a comma. This indicates that the last name is first, the first name is last, and you must split and reorder them, remove the comma, then stop. If not (else), if there's a space, the first name is first and the last name is last, so you need to split them and put them in the proper fields. Here's the program that will do it all. Save it as a named mass update so you can use it the next time you get more data from that supplier.
var vName as string vName = LastName If @Instr(vName, ",")=0 and @Instr(vName, " ")=0 //No Commas, no spaces { LastName = vName } Else { If @Instr(vName, ",")>0 // comma indicates last, first { LastName = @left(vName, @Instr(vName, ",")-1) FirstName = @Mid(vName, @Instr(vName, ",")+1, @len(vName)-@Instr(vName, ",")) } Else { If @Instr(vName, " ")>0 // no commas, just a space { FirstName = @left(vName, @Instr(vName, " ")) LastName = @Mid(vName, @Instr(vName, " ")+1, @len(vName)-@Instr(vName, " ")) } } }