spilting fields when import from excel??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear anyone...
When i import Excel file to Access.....
Example: "Name" in one column Excel....then it appears one column too in
Access.....but in my access....i need to spilt them into "First Name, Middle
Name, and Last Name"....how can i spilt them in Access when i import from
Excel since there are hundreds of data in the file/database....

Need help asap since im working on my database now...
Thanks......
 
Create This Function:
'-------------------------------------------
Function SplitName(ByVal s As String, ByVal Part As Integer) As String
Dim narr() As String
Dim i As Integer
narr = Split(s, " ")
If Part = 0 Then ' First Name
SplitName = narr(0)
ElseIf Part = 2 Then ' Last Name
SplitName = narr(UBound(narr()))
Else ' Middle Name
For i = LBound(narr()) + 1 To UBound(narr()) - 1
SplitName = SplitName & " " & narr(i)
Next
SplitName = Trim(SplitName)
End If
End Function
'----------------------------------------

Import the Excel data into a new table
Create an Append Query - something like:
INSERT INTO CONTACTS (FIRSTNAME, MIDDLENAME, LASTNAME)
SELECT SplitName(NameField,0),SplitName(NameField,1),SplitName(NameField,2)
FROM MyExcelData

HTH

Pieter
 
Do i have to use coding in order to make it work?....

i don get wat u mean by this part .....
Import the Excel data into a new table Create an Append Query - something
like: INSERT INTO CONTACTS (FIRSTNAME, MIDDLENAME, LASTNAME) SELECT
SplitName(NameField,0),SplitName(NameField,1),SplitName(NameField,2) FROM
MyExcelData

Would u mind explainin it in a simple ways ? Thank you very much....
 
It still cannot work.....do anyone mind to help me on this??
I've whole loads of data to import in Access database.....but due to
"Reserve" words.....i had been ask to spilt into "First Name, Middle Name,
Last Name" rather than "Name" itself......

but i tried the code that had been post...and it doesnt work.....anyone mind
2 help me step by step pls???...
Thanks a whole loads....
 
Back
Top