By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,652 Members | 1,696 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,652 IT Pros & Developers. It's quick & easy.

Help With Converting List to Database

P: n/a
I have a single column list in the following format:

John Smith
123 Anystreet
Anytown, state
zip
Tel: 333-555-1212
Fax: 333-555-2121
Dave Jones
321 Main Street
Nextown, state
zip
Tel: 444-555-1234
Fax: 444-555-4321
[And so on]

I need to convert to a normal database structure. I can separate the cities
and states once its converted, I just need to know how to make appropriate
columns.

Thanks very much.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Are you certain there are no missing lines? That is, you are going to have
to rely on the order of the input data, and if a name line were missing, you
are off-track from there on.

Otherwise, the Open statement, LineInput # statement, and the Close
statement are needed to read the information, and you can open a DAO
recordset on a table that you have predefined use the .AddNew to create a
new record , set the values into the Fields as you read them a Line at a
time, and use .Update when you either reach .EOF or detect the next name.

Seems to me the most likely problem you might run into (as you may not be
able to rely on every entry having every line) would be someone who didn't
have a Fax: or Telephone: entry to "alert" that you need to look for the
name to begin the next entry.

Larry Linson
Microsoft Access MVP

"news1.sympatico.ca" <no**@sympatico.ca> wrote in message
news:4i*****************@news20.bellglobal.com...
I have a single column list in the following format:

John Smith
123 Anystreet
Anytown, state
zip
Tel: 333-555-1212
Fax: 333-555-2121
Dave Jones
321 Main Street
Nextown, state
zip
Tel: 444-555-1234
Fax: 444-555-4321
[And so on]

I need to convert to a normal database structure. I can separate the cities and states once its converted, I just need to know how to make appropriate
columns.

Thanks very much.

Nov 12 '05 #2

P: n/a
Thanks Larry. I'm confident that in this case I can rely on the order and
that there are no missing lines. There are blank lines if not data exists.

Can you provide further instruction in terms of code? I can get the list
into Access but from there I'm not too sure.

"Larry Linson" <bo*****@localhost.not> wrote in message
news:vD****************@nwrddc01.gnilink.net...
Are you certain there are no missing lines? That is, you are going to have
to rely on the order of the input data, and if a name line were missing, you are off-track from there on.

Otherwise, the Open statement, LineInput # statement, and the Close
statement are needed to read the information, and you can open a DAO
recordset on a table that you have predefined use the .AddNew to create a
new record , set the values into the Fields as you read them a Line at a
time, and use .Update when you either reach .EOF or detect the next name.

Seems to me the most likely problem you might run into (as you may not be
able to rely on every entry having every line) would be someone who didn't
have a Fax: or Telephone: entry to "alert" that you need to look for the
name to begin the next entry.

Larry Linson
Microsoft Access MVP

"news1.sympatico.ca" <no**@sympatico.ca> wrote in message
news:4i*****************@news20.bellglobal.com...
I have a single column list in the following format:

John Smith
123 Anystreet
Anytown, state
zip
Tel: 333-555-1212
Fax: 333-555-2121
Dave Jones
321 Main Street
Nextown, state
zip
Tel: 444-555-1234
Fax: 444-555-4321
[And so on]

I need to convert to a normal database structure. I can separate the

cities
and states once its converted, I just need to know how to make appropriate columns.

Thanks very much.


Nov 12 '05 #3

P: n/a
"news1.sympatico.ca" wrote
Thanks Larry. I'm confident that in this
case I can rely on the order and that there
are no missing lines. There are blank lines if
not data exists.

Can you provide further instruction in terms
of code? I can get the list into Access but
from there I'm not too sure.


This worked for me in Access 2003, using the data you provided and a few
more in the same format, one with a blank line for Fax number. (I had used
the code from http://www.mvps.org/access/api/api0001.htm, contributed by Ken
Getz, to locate the file path and name, which I passed to the Sub in
argument pstrFile.) Note that it has no error handling, which you should
include in a production application, but which may obscure the main point in
a demo such as this!
Public Sub ImportNameAndAddress(pstrFile As String)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strLine As String
Dim intFn As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblPeople", dbOpenDynaset)
intFn = FreeFile
Open pstrFile For Input Access Read As intFn
Do While EOF(intFn) = False
Line Input #intFn, strLine
rs.AddNew
rs("PersonName") = Trim(strLine)
Line Input #intFn, strLine
rs("PersonStreet") = Trim(strLine)
Line Input #intFn, strLine
rs("PersonCityST") = Trim(strLine)
Line Input #intFn, strLine
rs("PersonZIP") = Trim(strLine)
Line Input #intFn, strLine
rs("PersonPhone") = Trim(strLine)
Line Input #intFn, strLine
rs("PersonFax") = Trim(strLine)
rs.Update
Loop
rs.Close
Close intFn
Set rs = Nothing
Set db = Nothing

End Sub
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.