label import

J

Jeff

Is there a way to import a text.txt file where the data is in the format
below (not a flat file)?

John Anderson
23 Red Arrow Dr
Grand Blanc, MI 49302

Bill Johnson
Calvin College
2454 Cedar Chase
Denver, CO 43223


Jack Jones
4534 Northland Dr
Boise, ID 34254

etc...



Thanks in advance
 
R

Rick B

That would not be normalized database design. You would not want one field
to contain the City, State, and Zip. Those should each be separate fields.

Once you import the file into a proper table, it would be easy for you to
build a report to show the data as in your example.

Rick B

PS: I would never post my real email address in a newsgroup. Let the Spam
begin!
 
M

Mark

I don't think there's an easy way to parse the text file in that format. In
the past, I've had to import a label report like the one you describe into
Excel, and use some quick cell formulas to copy each piece of info into
their own columns in the row so each row contains the address info across
instead of down. Then it's a snap to import it into Access.
 
R

Rick B

Sorry, I misread your post. I would agree with Mark that it would be
easiest to start off in Excel. Copy A2 to B1, copy A3 to C1, etc, so the
data is in a horizontal format. Use text to columns to convert into
columns. Delete all rows where there is nothing in column B, etc.

Then import.

Sorry, I mis understood your first post.

Rick B
 
D

Douglas J. Steele

Personally, I'd use VBA text I/O to read the file in, and insert the data
into the table through code, rather than trying to do an automated import.

The trick, of course, is knowing when you've read everything about the
record. Somehow you need to know that Bill Johnson's address has the
additional piece of information in it that the other two don't, but that's
going to be a problem with the Excel approach suggested by the others as
well.
 
M

Mike Painter

Jeff said:
Is there a way to import a text.txt file where the data is in the
format below (not a flat file)?

John Anderson
23 Red Arrow Dr
Grand Blanc, MI 49302

Bill Johnson
Calvin College
2454 Cedar Chase
Denver, CO 43223


Jack Jones
4534 Northland Dr
Boise, ID 34254

etc...
Click to expand...
Not without code.
If it's a one time deal you can fix it in Word, then save it as a text file.
Work with a copy.
Make sure there are only two carriage returns between the end of one record
and the srat of the next.
Search and replace all the ^p^p with the pipe character "|" INCLUDE THE
QUOTES You now have
John Anderson
23 Red Arrow Dr
Grand Blanc, MI 49302"|" Bill Johnson
Calvin College
2454 Cedar Chase
Denver, CO 43223

Place a double quote at the beginning of the file. and the end.
"John Anderson
23 Red Arrow Dr
Grand Blanc, MI 49302"|" Bill Johnson
Calvin College
2454 Cedar Chase
Denver, CO 43223"
Search and replace all ^p with "," giving:

"John Anderson","23 Red Arrow Dr","Grand Blanc, MI 49302"|" Bill Johnson","
Calvin College","2454 Cedar Chase","Denver, CO 43223"

Search and replace the | with ^p giving:
"John Anderson","23 Red Arrow Dr","Grand Blanc, MI 49302"
" Bill Johnson"," Calvin College","2454 Cedar Chase","Denver, CO 43223"
Now you can import as a text file.

Breaking up the last name first name and address is better done in Access.

There is a problem with your example as some address have four lines.
If there are just a few correct them by hand in Word, if not you'll need to
add a field in Access and have more work.
 
Top