Here is a handy tool if you have a data file in column format and need to get in a table - rows of data instead of a column of data. This example uses the field and for loops embedded in the second for loop to accomplish taking data from one column of data in rows of data.
In this example, I captured some data from the internet that I wanted to in the table. But as you can see the data in the column, not in rows like a database like that. So, quickly move the data into rows I created this code.
Create a new database. Then import a text file or use the information below.
Importing file as a text file into Access with a command file import. Access moves the data into a table with all the data in the first field. I named the table addrdata.
An example of a text file of data downloaded online.
Note:. Full text file is attached as an example of this e-mail if you want to use the
ACCESS health centers
3575 Macon Rd, Suite 15
Columbus, GA
Phone: 7065628225
ACCESS health centers
5 Concourse Parkway, Suite 825
Atlanta, GA
Phone: 7704810014
ACCESS health centers
WEST 120B College Street
Griffin, GA
Phone: 7702330023
ALCOVY REGIONAL Homecare
4168 Tate STREET
Covington, GA
Phone: 7707889403
AMEDISYS HOME health coving
9144 Highway 278 EAST
Covington, GA
Phone: 7707871796
AMEDISYS HOME health Macon
440 Martin Luther King Jr Boulevard, Suite 300
Macon, GA
Phone: 4787816653
AMEDISYS HOME health VALDOS
203 Woodrow Wilson Drive
Valdosta, GA
Phone: 2292450646
AMEDISYS NORTHWEST home health
29 caring way
Jasper, GA
Phone: 7066923491
Appalachian home health
1658 GOWDER DRIVE
Blairsville, GA
Phone: 7067455101
ARCHBOLD home health services
400 OLD ROAD Albany
Thomasville, GA
Phone: 2292276809
As you can see the data is all in one column and want to get the right to say that I can do to use an Access table. Fortunately, there are 4 rows of data and one blank line between each set of enterprise data. (If you're reading this article and there is a line space between each line in the example data, first remove the blank lines in a text editor, but one among the four lines for each company. In other words, leave a blank line between each set of four lines of data.) we can use it.
Then I make a table with these fields:. Name, Address, City, Phone all the text fields to put the data in the name of the table hcaddr
This module is used for string and loop rooted in the second for loop to accomplish taking data from one column of data in rows of data.
Create a new module and insert this code:
public function mktbl ()
On Error GoTo FuncError
DoCmd.SetWarnings true
Dim SQL as string, db As Database, REC As DAO.Recordset
Set db = CurrentDb ()
Dim DTA (5) As String, Integer Theatre as, x As Integer, y as Integer
SQL = "Select addrdata .* from addrdata;"
Set rec = db.OpenRecordset (SQL, dbOpenDynaset)
rec.MoveLast
rec.RecordCount CNT =
rec.MoveFirst
x = 0
for x = 1 to FC
for y = 1 to 5
If IsNull (REC (1)) then
DTA (y) = ""
else
DTA (y) = REC (1)
End If
rec.MoveNext
following
SQL = "INSERT INTO hcaddr (name, address, city, phone )"
SQL = SQL & "SELECT" & DTA (1) & "'as x1,'" & DTA (2) & "'as x2,'" & DTA (3) & , "'as x3,'" & DTA (4) & "'as x4;"
DoCmd.RunSQL SQL
x = x + 5
following
FuncError:
dbEvent Err.Description =
DoCmd.SetWarnings true
MsgBox "routine complete"
End Function
at the end.
When you open a module to use the / reference option and add the Microsoft DAO 3.6 Library or this code will not work.
I propose to save the module after you paste in the code. You can start the module by clicking the> arrow on the toolbar at the top or clicking F5 th.
Note: This code will fail if any of the records in a text file of data you have a quote (') in the field. Before you ask, that the city and the state can put in a separate area for a phone to remove the last line, but it is out of this example. Maybe the next one.
This should do it. Give it a shot and see how it works for you. This example was created with Access 2002nd As always, there are probably a million other ways to do the same thing, but this time it worked for me.