473,320 Members | 1,902 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Question about an import...

JB
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. :)
Jul 13 '08 #1
2 1288
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
Jul 14 '08 #2
JB
ba****@gmail.com wrote in
news:f4**********************************@s50g2000 hsb.googlegroups.com:
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 retu
rn
>as a delimiter (in access) or do I have a whole lot of cutting and
pastin
g
>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
Thanks for the help. :)
Jul 16 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: djw | last post by:
I'm sturcturing my (relatively) large application into packages and am having trouble understanding one aspect of module/package paths. If I have a structure like: in /: appA.py __init__.py...
6
by: Jon | last post by:
Hi, The following four lines of code: import sys, os, re sentence = raw_input("Enter a sentence: ") capwords (sentence) print sentence gives me the following error: NameError: name...
16
by: dario | last post by:
Hi, Im new on phyton programming. On my GPRS modem with embedded Phyton 1.5.2+ version, I have to receive a string from serial port and after send this one enclosed in an e-mail. All OK if the...
1
by: Dan | last post by:
Could someone please help me with auto importing a series of data files into an Access table. I tried to follow code given below in a previous messagebut i'm getting error messages. Here's my...
1
by: deko | last post by:
Is there an Import Wizard for MSDE? In Access, when you select File >> Get External Data >> Import, you get a Wizard that lets you browse for a file to import by file type (txt, Excel, etc.). ...
3
by: David Hirschfield | last post by:
An xmlrpc client/server app I'm writing used to be super-simple, but now threading has gotten into the mix. On the server side, threads are used to process requests from a queue as they come in....
17
by: Eric_Dexter | last post by:
def simplecsdtoorc(filename): file = open(filename,"r") alllines = file.read_until("</CsInstruments>") pattern1 = re.compile("</") orcfilename = filename + "orc" for line in alllines: if not...
5
by: Steven W. Orr | last post by:
I'm trying again, since no response indicates that I'm not providing enough info. I have module M1 which has the following line in it: StartTime = safe_dict_get ( dic, 'starttime',...
5
by: sstidham | last post by:
I cannot understand for the life of me why this isn't working, but maybe someone can help me out. It should be a simple Sum. I have a table that is imported data from Excel. I need to total up...
17
by: Suresh Pillai | last post by:
I am performing simulations on networks (graphs). I have a question on speed of execution (assuming very ample memory for now). I simplify the details of my simulation below, as the question I...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.