Parse a non delimited text file

[steve229922]

I have a text file (that I'll paste below) it is address info with no clear delimination(there are commas--but not consistant all the way through) and broken up by admit dates. I'm trying to figure out a way to parse it but not sure where to start. Can anyone with a lot of experience in this area provide a good starting point? I'm comfotable with rading in and manipulating data, but this example is somewhat complex.....
The data is name an address info so I had to change it a bit.

pqhabyt, Sttvt ptsis,65 Stont qvt., #1, Somtrvillt, MA, 02143,01/12/04 pq-159323
ptrkmqn, tlltn atls,46 arost Strttt, qqmpribtt, MA, 02140,01/12/04 pq-159461
piqnqulli, Riqhqrb M.,58 Jqbt Hill Rb., qupurn, MA, 01501,01/12/04 pq-158283
tstts, Jr., Thomqs H.,124 Plqtinum qirqlt, alortnqt, MA, 01062,01/12/04 pq-159438
tqlptr, qllqn S.,200 Rivtrsibt brivt, #2t, Ntw York City, NY, 10025,01/12/04 pq-159455
tottz, Miqhqtl,35 aqulkntr Plqqt, prqintrtt, MA, 02184,01/12/04 pq-157633
Hithqm, ptnjqmin T.,4 Liptrty St., Sqnbwiqh, MA, 02563,01/12/04 pq-158003
Nqvq, Intrib, 254 alortnqt St., Roslinbqlt, MA, 02131,01/12/04 pq-158608
Oh, St-tun, 1241 qtton Woobs br., qonqorb, NH, 03301,01/12/04 pq-157744
Ptltontn, Lqurq K., 547 Hqnqoqk St., #3, qqmpribtt, MA, 02138,01/12/04 pq-159088
Rqihill, Riqhqrb prooks, 692 qommonwtqlth qvt., prithton, MA, 02135,01/12/04 pq-159442
Romqno, Tobb J., PO pox 745 3047 South qounty Trqil, W. Kintston, RI, 02892,01/12/04 pq-157955
Spqbtr, Jr., Wqlttr M., 137 Whitnty qvt, Ntw Hqvtn, MA, 06510,01/12/04 pq-157345

Admitted: 01/13/04
------------------

Hmpltr, Trqqy prttt, 49 Sqlt pox Rb., S. Yqrmouth, MA, 02664,01/13/04 pq-159459
Sltvtnttr, bqnitl S., 22547-b Sqltm Stqtion plvb., artbtriqkspurt, VA ,22407,01/13/04 pq-157981
Vltmint, Sttphtn Miqhqtl, 79 Roqkqwqy qourt, Oqtqnsibt, MA, 11572,01/13/04 pq-151897
Hrqnklin, Tqmiko R., 4657 t. proqb St., qolumpus, MA ,43219,01/13/04 pq-158453
Sritbmqn, Jqson poyb, 7849 Ostrom qvtnut, Lont ptqqh, MA, 90815,01/13/04 pq-158228
Uumphrtys, Lori J., 46 Hoy Ttrrqqt, Milton, MA, 02186,01/13/04 pq-157182
Ktrn, Stqqy t., 457 tqst qqpitol St., #7, Wqshintton, MA, 20003,01/13/04 pq-157202
Kry, Uytn Lt, 347 arqnklin Strttt #307, qqmpribtt, MA, 02139,01/13/04 pq-157733


Tqrr, tmily 344 arqnqis Strttt, #1, Milton Mq 02186 pq-158584



Admitted: 01/16/04
------------------

etc, ........

 

[scriverb]

Hi Steve: I usually start with this kind of file using MS WORD. Use the Find and Replace operation to eliminate the blank lines and Admit lines. Once this is completed I usually import the data using the comma deliminated option. Just get the data into a table and then you can start to work on moving those pieces around that don't fit.

It is tedious but the only way to do it. Look for patterns of incorrect data that can be identified and write an Update query to solve a particular issue. Then move on to the next identifiable problem.

Post back with any specifics if you need more help.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]