Multiple line ascii to Access

[joell165]

I need to convert an ascii file of multiple lines per record to an Access table. The problem I have is when I do the conversion information that should be all in one record becomes multiple records. Each record in the ascii file looks similar to this:

Smith, John 3462 (212)123-4567 456-67-5673
Male 23 Main Street Sep 2,3,4,5,6,9,15,21,22,24
Oct 1,3,6,24
Nov 2,3,4,9,17,

As you can see, not only is the information on multiple lines (between 3 and 7)data overlaps from line to line.

I am at a total loss.
Thanks.

Joel

 

[beetee]

This data offends on several levels
1) it is not normalized (you want to have a table of dates, a table of people, and a table of dates associated with people)
2) it has lots of implied information (e.g. the various comma separated numbers are all associated with the same date).

Because of this, I would be tempted to clean the data before importing it, or write some VB code to do the importation (or C++ code to clean the data if you are so inclined).

Also, there used to be a program called 'Monarch' that could deal with information like this (I used it back in 1997 or so).