Text data with irregular data separators

[hpoincare]

Staff at my company have been spending hours retyping data into spreadsheets
from a text file export. The Data is repeating dual lines of information:
Last name, First name,... which then ends with the state "WASHINGTON" and a
carriage return followed by the next line.

Each entry is like this:


ADAM THOMAS 21688883 09/25/1966 2224 WASHINGTON


Act Code 13 Date Started 09/25/2004 10/23/2005 Exit Date


My thought was to create some sort of script that would replace the Tab
seperators with Commas and replace the carriage return after "WASHINGTON"
with a comma and then combine the two lines on one line. This would get rid
of the irregularities and I could then import the data into a spreadsheet.

Am I dreaming? If there is a practical solution, would someone kindly point
me in the right direction? This will save my staff countless hours. All
comments welcome no matter the relavance.

Thanks

 

[yshridhar]

You could have done in it Ms-word.
ctrl - h (replace)|special|select paragraph mark| with ,
(ctrl-h; what ^p; with ,)
will combine the two lines in word.
Hope this is what you are after.
regards
sreedhar

 

[Rick Rothstein \(MVP - VB\)]

No, you are not dreaming (tell them to stop typing); however, you need to
give us a little more data about this text file. Is there one (or more)
blank lines separating the first and second lines (like you showed in your
posting)? How about the next lines following this one... are there blank
lines separating it for the lines above them? Perhaps if you could
Copy/Paste say 5 records of information (10 lines from the file) into a
response, that would help us to know what your file looks like. I'm also
interested to know if there is any non-data text at the beginning and/or end
of the text file (if so, it will need to be skipped over, so you will need
to tell us details about them). Also, where is the is data going? By that I
mean (using your posted sample), does ADAM go in column A, THOMAS in column
B, 21688883 in column C, etc., or are there any columns being skipped? Any
other information you can think of would help. Details... we need details.
Better yet, if you have a website where you can post a copy of the file so
we can download it and know exactly what the file looks like.

Rick

 

[hpoincare]

DATE: 11/01/2007 Work First Program ----- Participant
Listing Report - WF721
Program Type : Standard Types Region Name : 2224
Period : 10/01/2004 TO 10/01/2005

Date
Participant Name Cust ID FIA Nbr Attended
Referral Code DIS REG County Term Type Term Dat
------------------------------------------------------------------------------------------------------------------------------------------------
ADAMS JOHN ADAMA0813 21688883 09/25/2004
WR_WRK_1ST_REF 17 2224 WA

Act Code 13 Date Started 09/25/2004 Est Exit Date
10/23/2005 Exit Date


ALLEN JIM ALLAM0310 71888563 08/14/2004
WR_WRK_1ST_REF 17 2224 WA

Act Code 13 Date Started 08/14/2005 Est Exit Date
09/14/2005 Exit Date 08/23/2004


1 08/24/2004
02/20/2005

ANDREWS BOB D ANDSA0322 61888806 08/14/2005
WR_WRK_1ST_REF 17 2224 WA

Act Code 13 Date Started 08/14/2005 Est Exit Date
09/14/2005 Exit Date 08/22/2004


1 08/22/2004
02/18/2005

ARRINGTON JIMMY J ARRJO1214 32812055 03/13/2004
WR_WRK_1ST_REF 17 2224 WA

Act Code 13 Date Started 03/13/2004 Est Exit Date
04/20/2005 Exit Date 04/02/2005 90 Day

This isn't exactly how the data appears. For example the words Exit Date are

 

[Rick Rothstein \(MVP - VB\)]

From what you posted, I am still unsure of how the data is laid. Would you
mind sending me the actual file so I can see exactly what is in it and how
it is physically laid out? Just remove the NO.SPAM stuff from this email
address...

(e-mail address removed)

Also, a question about the layout that I can decipher... I notice that in
what I think is the 3rd record there is some date information BEFORE the
record itself, but I don't see that kind of information in the first two
records... Am I reading that correctly? If so, do I need to do anything with
those dates or do I just ignore them?

By the way, you didn't answer my other question about where this information
goes... I'll be assuming Column A, B, etc. in order unless you tell me
differently.

Rick