Here is a handy tool if you have a file of data in a column format and you need to get it into a table format - Rows of data instead of a column of data.
This example uses an array and a For loop embedded in another For loop to accomplish taking the data from a single column of data to rows of data.
In this example I captured some data from the Internet that I wanted to make into a table.
But as you can see the data is in a column and not in rows like a database likes it.
So, to quickly move the data to rows I created this code.
Open a new database.
Then import the text file or use the data below.
Import the file as a text file into Access with the file import command.
Access moves the data into a table with all the data in the first field.
I named the table addrdata.
Sample data text file taken off of the internet.
Note: The entire text file is attached as an example to this email if you prefer to use it.
ACCESS HOME HEALTH 3575 MACON RD, SUITE 15 COLUMBUS , GA Phone: 7065628225 ACCESS HOME HEALTH 5 CONCOURSE PARKWAY, SUITE 825 ATLANTA , GA Phone: 7704810014 ACCESS HOME HEALTH 120B WEST COLLEGE STREET GRIFFIN , GA Phone: 7702330023 ALCOVY REGIONAL HOMECARE 4168 TATE STREET COVINGTON , GA Phone: 7707889403 AMEDISYS HOME HEALTH OF COVING 9144 HIGHWAY 278 EAST COVINGTON , GA Phone: 7707871796 AMEDISYS HOME HEALTH OF MACON 440 MARTIN LUTHER KING JR BOULEVARD, SUITE 300 MACON , GA Phone: 4787816653 AMEDISYS HOME HEALTH OF 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 ALBANY ROAD THOMASVILLE , GA Phone: 2292276809 As you can see the data is all in one column and I want to get it in the right rows so I can make it a usable Access table.
Fortunately there are 4 rows of data and one blank line between each set of company data.
( If you are reading this article and there is a line space between every line in the above example data, first remove the blank lines in a text editor except the one between the four lines for each company.
Another words, leave a blank line between each set of four lines of data.
)We can take advantage of that.
Next I create a table with these fields: Name, Address, City, Phone all as text fields to put the data into and named the table hcaddr.
This module uses an array and a For loop imbedded in another For loop to accomplish taking the data from a single column of data to 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, cnt As Integer, x As Integer, y As Integer SQL = "select addrdata.
* from addrdata;" Set rec = db.
OpenRecordset(SQL, dbOpenDynaset) rec.
MoveLast cnt = rec.
RecordCount rec.
MoveFirst x = 0 For x = 1 To cnt For y = 1 To 5 If IsNull(rec(1)) Then dta(y) = " " Else dta(y) = rec(1) End If rec.
MoveNext Next 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 Next FuncError: dbEvent = Err.
Description DoCmd.
SetWarnings true msgbox "routine complete" End Function Code end.
When you have the module open use the Tools/reference option and add the MS DAO 3.
6 Library or this code will not work.
I suggest saving the module once you have pasted in the code.
You can run the module by clicking on the > arrow in the tool bar at the top or selecting th F5 key.
Note: This code will fail if any of the records in the text data file have a single quote ( ' ) in the field.
Before you ask, yes the city and state could be put in separate fields and the word phone removed from the last line, but that is beyond this example.
Maybe in the next one.
This should do it.
Give it a shot and see how it works for you.
This example was created with Access 2002.
As always there are probably a million other ways to do the same thing, but this way worked for me.