help with parse

[QuietRiot]

Not sure what to do. Right now from the import I only have one field in my table (field1).
It contains account number followed by 5,6, or 7 rows for the address. I would like to have it
On the same row multiple fields

It looks like this..

123456789-00
123 main st
Boston, ma
02216
124521555-00
Attn: tom
562 fake st
Boston, ma
02216

 

[John Big Booty]

How is this data currently store? In a word document, Excel file, Access Tables or something else?

How is it currently formatted?

 

[QuietRiot]

How is this data currently store? In a word document, Excel file, Access Tables or something else?

How is it currently formatted?

Its a text file report from an old unix system

 

[John Big Booty]

If you open the document in word you can use the find and replace command to find ^p (carriage return) with a comma.

If you do a Replace All make sure that you have a consistent number of carriage returns between each record (lets say two). That way once you have done a Replace All for ^p to comma, you can then do a Replace All for 3 commas to ^p so that you will now have one record per line.

After you first Replace All you data should look something like,

123456789-00,123 main st,Boston, ma,02216,124521555-00,Attn: tom,562 fake st,Boston, ma,02216,,,123456789-00,123 main st,Boston, ma,02216,124521555-00,Attn: tom,562 fake st,Boston, ma,02216
​

Then after your second Replace All it should look like;

123456789-00,123 main st,Boston, ma,02216,124521555-00,Attn: tom,562 fake st,Boston, ma,02216
123456789-00,123 main st,Boston, ma,02216,124521555-00,Attn: tom,562 fake st,Boston, ma,02216
​