Hey guys, I have a file that I've slowly been awking, seding, and greping for data entry. I am down to pull the addresses out to insert them into an excel file. Each address is a few lines, but i want to put a semicolon delimiter in between each address so I can export the text file into excel and have a column of address that matches up with other customer info. Some of the customers' records do not contain address info, but all of them contain the field " CONTACT: Name" after the adress, like so:
Address line 1
Address line 2
CONTACT: Name
Address line 1
Address line 2
CONTACT: Name
Can anyone help me turn the CONTACT line into a delimeter, or suggest something simpler, I would GREATLY appreciate it as there are many entires. NOTE: Not all address contain 2 lines, some have 4, some have 0. ALL of them contain the CONTACT line
Try this:
awk '/CONTACT:/{print $2";"s; s="";next}{s=s?s";"$0:$0}' file > newfile
Regards
Hey Franklin, no dice on that, but I ended up figuring out a much simpler way to do it. I used:
sed 's/CONTACT.*/;/' file
to replace those lines with colons. I then deleted all newline characters with 'tr'. This left me with one line of addresses delimited by semicolons. I exported it into excel which gave me one row of 1282 addresses. Each address filled one cell on one line. I coped the whole row, and 'Paste Special'ed it into my master document using transpose to make it a column in excel as it could not handle the newlines in the delimited fields. I can export this file to CSV,TXT,XML etc., but is there an easy way to format the addresses so they aren't one line?
Post a portion of your input file and the desired output.
Regards
I have addresses like this
34 West Gutierrez Street Santa Barbara CA 93101; PO Box 6666 Santa Barbara CA 93120 USA United States;...
All one one line of a text file that I exported into Excel. The thing is, I need to import this into Quickbooks, and I want properly formatted addresses. Newlines in the text were problematic when importing, so I got rid of them. I thought I might be able to go back to when before I deleted newlines, and changed them into some random character, then somehow replace that character with a newline in excel, like so.
;
PO Box 6666
Santa Barbara CA 93120
USA United States
;
Not all the addresses have the same amount of lines, and some have no country line. the only other pattern I see is the that there should be a newline instead of a space which is right before the blank space in between "City" "State", and then another newline after the zip.
It's kind of complicated, but basically I just want to have a formatted address in ONE cell in excel, rather then one entire line of the address in one cell.
Hope this makes it clear, thanks in advance!
IMHO it's not possible to have several lines in one cell in excel or have I missed something?
You could be right, I was not able to hit enter in a cell to create a newline character. I am not yet sure if excel will take the address broken up by City State Zip etc. Thanks for your help though Franklin. I'll follow up on this when I find a solution... err maybe another feasible problem.