On Jul 14, 12:18*am, JB <jb...@yahoo.comwrote:
I haven't played with Access in ages and I would appreciate some
help on a project I'm doing...
I have a text file of addresses I need to create a mailing list from.
I know that if I create a list in excel I could then mail merge from
word. *Problem is, the text is in the traditional:
Name
Address
Address2
City, State Zip
I can't use the space delimiter because it wants to put each individual
line into it's own field. *Is there a way to either use a carriage return
as a delimiter (in access) or do I have a whole lot of cutting and pasting
coming up? *Thanks for any help. *:)
There is probably better way to import this file, but here is what I
wrote for this purpose.
Assuming you have table named "tblmport" with following fields:
ID (AutoNumber), PName, Address, Address2, City, State, ZIP (all are
Text type)
you can use bellow code to import data.
Call the code from Immediate window on VBA screen with:
? fimporttext("c:\import.txt")
Change path and file name accordingly to your text file path and name.
But, first of all, copy these functions into new module.
Function fImportText(ByVal txtFileName As String)
Dim iFile As Integer, strImportString As String
iFile = FreeFile
Open txtFileName For Input As iFile
Do Until EOF(iFile)
strImportString = strImportString & Input(1, #1)
Loop
strImportString = Replace(strImportString, Chr(13) + Chr(10), "|")
fWriteImportedText (strImportString)
Close #1
End Function
Function fWriteImportedText(strImportString As String)
Dim rs As Recordset, i As Integer
Set rs = CurrentDb.OpenRecordset("tblImport")
'Change table name in above line to match your table name
If Len(strImportString) = 0 Then Exit Function
i = InStr(1, strImportString, "|")
'j = 1
Do While i 0
rs.AddNew
rs!PName = Mid(strImportString, 1, i - 1)
strImportString = Right(strImportString, _
Len(strImportString) - i)
i = InStr(1, strImportString, "|")
rs!Address = Mid(strImportString, 1, i - 1)
strImportString = Right(strImportString, _
Len(strImportString) - i)
i = InStr(1, strImportString, "|")
rs!Address2 = Mid(strImportString, 1, i - 1)
strImportString = Right(strImportString, _
Len(strImportString) - i)
i = InStr(1, strImportString, ", ")
rs!City = Mid(strImportString, 1, i - 1)
strImportString = Right(strImportString, _
Len(strImportString) - i - 1)
i = InStr(1, strImportString, " ")
rs!State = Mid(strImportString, 1, i - 1)
strImportString = Right(strImportString, _
Len(strImportString) - i)
i = InStr(1, strImportString, "|")
If i = 0 Then
i = Len(strImportString) + 1
End If
rs!Zip = Mid(strImportString, 1, i - 1)
If InStr(1, strImportString, "|") 0 _
Or InStr(1, strImportString, ", ") 0 _
Or InStr(1, strImportString, " ") 0 Then
strImportString = Right(strImportString, _
Len(strImportString) - i)
End If
i = InStr(1, strImportString, "|")
rs.Update
Loop
End Function
There is no any error handling, but I hope you can manage to use it.
Code assume that data in text file have this format
Name
Address
Address2
City, State Zip
Name
Address
Address2
City, State Zip
Name
Address
Address2
City, State Zip
etc.
Regards,
Branislav Mihaljev
Microsoft Access MVP