General discussion


multi-line text into Access

By john.a.wills ·
I have a .txt file with 4 records for each person. The first record is the person identifier. The second record is just the name, but with blanks in it. The next is a multifield address. Then there is a record with the amount of money involved. I want to get each person's data into a row in an Access table. I would like to do this as far as possible with Access' external data facility.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by sgt_shultz In reply to multi-line text into Acce ...

open Access, choose New database (that will create a new blank database). then choose File Get External Data and browse to your .txt file. follow the screen and it will help you import your data. you can save the resulting table under any name you like. then try the Form and Report Wizards to look at and change the data...

Collapse -

by sgt_shultz In reply to

the Help files in Access are pretty good...

Collapse -

by john.a.wills In reply to

The import wizard is inadequate to get 4 records into 1 row. As I have 1000s of rows to produce, manual change of the data is not on.

Collapse -

by dryflies In reply to multi-line text into Acce ...

Does your data look like this?
George Foreman
1234 west poorman, hollister CA

Janet Jackson
2345 East hollywood, richgirl CA

If it does, then you will have to work in two steps. First create a filter that takes your data file and converts it to a format that is edible for M$ access. You can do this in perl, php, VBScript or any of a number of dialogs. The goal of theis filter is to read in four lines, stitch the four lines into one using a delimiter, and then write out one line to a temporary file. in pseudo code it would be:
open input file
open temp file for output
read line into string 1
read line and concatenate to string 1 with a leading comma
read line and cat to string 1 with a leading comma
read line and cat to string 1 with a leading comma.
write string 1 to temp file
repeat until input file is processed (eof)
close input file and output file

Now your data is all on one line per record and you can use the get external data functionality to process the temporary file. you do not have to use a comma, but I would match what is used in the address line to separate the city and state from the street address. I would use the transferText macro command since you can do it programmatically instead of having to click buttons.
Almost done.
Now you may want to split name into first and last or the address might be delimitted differently so that city and state come in as a single field, if for example they are separated spaces like the names are but the street name and number are also separated by spaces. once you have the records into a table you can either concatenate fields together to create a new field (use build to enter the following.)
fieldone: [testable]![streetnum]+" "+[testable]![streetname]

or you can split one field into two by using the VB string functions.

Collapse -

by dryflies In reply to

not enough room, had to add comment. :-)
Watch your data because if some of the entries have middle names and others don't, you may end up with trouble. but eventually, by splitting and combining you will end up with the field structure you desire.
please rate my answer. if there is something you do not understand let me know.

Collapse -

by john.a.wills In reply to

The first stage could probably be carried out with something we have here, but we overcame the problem by adjustment of the program producing the file.

Collapse -

by john.a.wills In reply to multi-line text into Acce ...

This question was closed by the author

Related Discussions

Related Forums