Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text data with irregular data separators
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text data with irregular data separators
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 "hpoincare" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text data with irregular data separators
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text data with irregular data separators
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 Date ------------------------------------------------------------------------------------------------------------------------------------------------ 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 on one line. I hope this helps "Rick Rothstein (MVP - VB)" wrote: 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text data with irregular data separators
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transfer Excel data into Word, including text box data | Excel Discussion (Misc queries) | |||
number formatting/separators | Excel Worksheet Functions | |||
Can I take a huge irregular text file and place it into columns? | Excel Discussion (Misc queries) | |||
Convert irregular data to monthly equivalent? | Excel Discussion (Misc queries) | |||
how to remove separators from numbers | Excel Discussion (Misc queries) |