import from excel (1 Viewer)

[cardgunner]

I think this is an age old question but I have yet to ask it. I have a XLS spreadsheet I'm importing into a table in access. One of the column in excel has both the first and last name and middle intial sometimes such as Frank H. Urtz. I want to seperate this in access. firstname =Frank lastname=Urtz midinitial=H. How do I go about it?

I think I want to write a query saying give me everything up to first " ". Insert that into field1. GThe give me everything from the first ' " to the second " " and insert into field2 and so on.

Any ideas?

 

[dgkindy]

If the format is consistant use the Split function. You specify the delimiter and the string and it is split into the components.

 

[cpremo]

This is going to be harder than you think. You could use the instr function to find the First " " (blank character), but what about Dual Last Names? For example: Juan J. Hernandez Lopez. Then there's the J. William Smith variation.

This will be a problem.

Here is an example of the instr function:

The Field contains names like this - SMITH, MICHAEL JAMES


I this function to split the names:

********************************************
Function SplitName() As String

Dim LastName As String
Dim FirstName As String
Dim MiddleName As String
Dim Title As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "[tblRosterData]", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
rst.MoveFirst
Do While Not rst.EOF
If Not rst.BOF And Not rst.EOF Then
LastName = Left(rst![Name], (InStr(rst![Name], " ") - 1))
FirstName = Mid(rst![Name], (InStr(rst![Name], " ") + 1), 99)
rst![LName] = LastName
rst![FName] = FirstName
If InStr(rst![FName], " ") = "0" Then

Else
MiddleName = Mid(rst![FName], (InStr(rst![FName], " ") + 1), 99)
FirstName = Left(rst![FName], (InStr(rst![FName], " ") - 1))
rst![FName] = FirstName
rst![MI] = MiddleName
If InStr(rst![MI], " ") = "0" Then
Else
MiddleName = Left(rst![MI], (InStr(rst![MI], " ") - 1))
Title = Mid(rst![MI], (InStr(rst![MI], " ") + 1), 99)
rst![MI] = MiddleName
rst![Title] = Title
End If
End If
End If
rst.MoveNext
Loop

rst.Close
Set rst = Nothing

End Function
******************************************************

 

[Moniker]

Depending on the number of entries you have in Excel, in may be much easier to separate them in Excel (using the same InStr, Left, Mid, Right, etc. function) and then import it. If you have thousands of entries, this isn't the best thing to do, but if it's just a few hundred or less, take care of it in Excel first and the import will be smoother.

Those that said you're opening a can of worms, at least in spirit, are right. You'll get all sorts of combinations of Salutations, sir names, multiple last names, etc., and programming around them is a tedious activity at best. Going forward, the better solutions is forcing first, middle, and last into their own fields. I realize you're importing this stuff and probably had nothing to do with the data source, but get that rectified ASAP to avoid future issues. Otherwise, this will become an ongoing issue.

 

[Kirk]

I like the "text to colums" function in Excel. I use it to parse delimited data frequently. In this case there are two delimiters, spaces and commas, and I'm not sure if text to columns would grab all of that at once, but there is no coding involved.