[Hidden-tech] Text Manipulation Problem

[Shel Horowitz]

Yup, a variant. I personally prefer tab-delimited to comma-delimited, for
just that reason. But my brain translated "csv" in David's original post as
"comma-delimited.


Shel Horowitz - "The Transformpreneur"(sm)
________________________________________________
Watch (and please share) my TEDx Talk,
"Impossible is a Dare: Business for a Better World"
*http://www.ted.com/tedx/events/11809
<http://www.ted.com/tedx/events/11809>**
<http://www.ted.com/tedx/events/11809>*
(move your mouse to "event videos")

Contact me to bake in profitability while addressing hunger,
poverty, war, and catastrophic climate change

Twitter: @shelhorowitz

* First business ever to be Green America Gold Certified
* Inducted into the National Environmental Hall of Fame

http://goingbeyondsustainability.com
http://transformpreneur.com
mailto:shel at greenandprofitable.com * 413-586-2388
Award-winning, best-selling author of 10 books. Latest:
Guerrilla Marketing to Heal the World (co-authored with Jay Conrad Levinson)

_________________________________________________

On Sun, Oct 22, 2017 at 9:28 AM, Don Lesser <dlesser at ptraining.com> wrote:

> Be careful if there are commas in your street addresses (101 Main St, Ste
> 3) which will throw off the import.
>
>
>
> I use a variant of Shel’s technique.
>
>
>
> Changing spaces to commas may screw up your names and addresses. I think
> you’ll end up with:
>
>
>
> John,Doe,(413),111-1111,123,First,St,Greenfield,01301
>
>
>
> Instead of replacing the paragraph marks with a comma, replace them with a
> tab (^t in Word Find and Replace). This will create a tab delimited file,
> .TXT, which eliminates the comma issue. This imports directly into Excel.
>
>
>
> You may have an issue since there are no blank spaces between addresses.
> If all the addresses are in Greenfield, you can search for 01301^p and
> replace it with 01301@#@ as described below.
>
>
>
> The result is kind of messy. Do you want the address all in one cell,
> instead of Address, City, Zip in separate cells? You won’t be able to do
> much with the import if it is all in one cell.
>
>
>
> Usually, I do something like this:
>
>
>
> Last paragraph mark in the address (if there is a blank line between
> addresses):
>
> Find: ^p^p
>
> Replace: @#@
>
>
>
> Paragraphs to tabs (^p to ^t)
>
> Find: ^p
>
> Replace: ^t
>
>
>
> @#@ back to paragraphs:
>
> Find: @#@
>
> Replace: ^p
>
>
>
> The @#@ is simply a pattern that usually doesn’t occur in most addresses.
> ^p and ^t must be lowercase.
>
>
>
> This gives me a tab delimited file with tabs between fields and a
> paragraph mark at the end of a record. Save as a text file with a .txt
> extension. That will import directly into Excel with few problems.
>
>
>
> John Doe^t(413) 111-1111^t123 First St Greenfield 01301^p
>
>
>
> If you can get a blank line between addresses, so much the better.
> Otherwise, you’ll have to do a find and replace on individual zipcodes.
>
>
>
> Similarly you’ll need to put a paragraph mark before Greenfield to get the
> street address separate from the City/Zip.
>
> Find: “Greenfield”
>
> Replace”^tGreenfield”
>
>
>
> Or otherwise separate the street address from the city-zip:
>
> Find: “ St “
>
> Replace: “ St^t”
>
> and so on for Road, Lane, Court, etc. The spaces on either side of St keep
> you from replacing “First” with “FirSt^t”.
>
>
>
> This works in Word  Mac or PC, I believe. Do not use wildcards.
>
>
>
> You can also use the Text to Columns function in Word to help separate the
> line. You can also use the LEFT(), MID(), and RIGHT() functions in Excel
> once you have the text imported. Excel has better tools for this than Word.
>
>
>
> If you drop the leading 0 in zipcodes, you can fix it with this: you can
> add an apostrophe to the beginning of a zip (Find 01013 Replace ‘01013) or
> use =TEXT(cell,”00000”) in Excel to create text with the proper zipcodes.
> Then Copy and Paste Values to remove the formula, cut and paste over the
> zipcodes in your text. See (https://www.ptraining.com/
> blog/mail-merge-problem-leading-zeroes-missing-from-zip-codes/) for more
> info.
>
>
>
>
>
> Don Lesser
>
> Pioneer Training, Inc.
>
> 139B Damon Road, Ste 8
> <https://maps.google.com/?q=139B+Damon+Road,+Ste+8+Northampton,+MA+01060+(413&entry=gmail&source=g>
>
> Northampton, MA 01060
> <https://maps.google.com/?q=139B+Damon+Road,+Ste+8+Northampton,+MA+01060+(413&entry=gmail&source=g>
>
> (413) 387-1040
>
> dlesser at ptraining.com
>
> www.ptraining.com
>
>
>
> *From:* hidden-discuss-bounces at lists.hidden-tech.net [mailto:
> hidden-discuss-bounces at lists.hidden-tech.net] *On Behalf Of *Shel Horowitz
> *Sent:* Saturday, October 21, 2017 10:04 PM
> *To:* David Greenberg <david.greenberg3 at gmail.com>
> *Cc:* Hidden-Tech Tech <hidden-discuss at lists.hidden-tech.net>
> *Subject:* Re: [Hidden-tech] Text Manipulation Problem
>
>
>
> If you want something less techie than Perl, try this, using Word's
> ability to search-replace non-alphanumeric characters:
>
>
>
>    1. Pop the text into Word. Save the file under another name in case
>    you mess up.
>    2. Using search-and-replace, change all instances of 01301 to
>    01301&^&^ (or any other string that would not show up in the data file)
>    3. change all (413
>    <https://maps.google.com/?q=139B+Damon+Road,+Ste+8+Northampton,+MA+01060+(413&entry=gmail&source=g>)<space>
>    to (413)<no space>
>    4. Change all remaining spaces to commas
>    5. Change all paragraph returns to commas put ^p into the Find box and
>    make sure the p is lower-case)
>    6. Change all &^&^ back to ^p
>    7. Scan the file for any tweaks you need, like changing ,, to just one
>    ,
>    8. Save as text
>
> Whole thing should take about three minutes.
>
>
>
> Shel Horowitz - "The Transformpreneur"(sm)
>
> ________________________________________________
>
> Watch (and please share) my TEDx Talk,
>
> "Impossible is a Dare: Business for a Better World"
>
> *http://www.ted.com/tedx/events/11809
> <http://www.ted.com/tedx/events/11809>
> <http://www.ted.com/tedx/events/11809>*
>
> (move your mouse to "event videos")
>
>
>
> Contact me to bake in profitability while addressing hunger,
>
> poverty, war, and catastrophic climate change
>
>
>
> Twitter: @shelhorowitz
>
>
>
> * First business ever to be Green America Gold Certified
>
> * Inducted into the National Environmental Hall of Fame
>
>
>
> http://goingbeyondsustainability.com
>
> http://transformpreneur.com
>
> mailto:shel at greenandprofitable.com * 413-586-2388
>
> Award-winning, best-selling author of 10 books. Latest:
>
> Guerrilla Marketing to Heal the World (co-authored with Jay Conrad
> Levinson)
>
>
>
> _________________________________________________
>
>
>
> On Sat, Oct 21, 2017 at 7:34 AM, David Greenberg <
> david.greenberg3 at gmail.com> wrote:
>
> I have a hard copy list of names, addresses and phone numbers. I can scan
> to PDF and then copy and paste to a text editor (BBEdit) or other file. I
> then need to manipulate the text so that I end up with a csv file that can
> be opened by a spreadsheet program. Tools that I have at my disposal
> include BBEdit (with Grep), a MAMP stack, NeoOffice (Mac version of
> OpenOffice) and FileMaker.
>
>
>
> Input looks like this:
>
>
>
> John Doe
>
> (413) 111-1111
>
> 123 First St Greenfield 01301
>
> Jane Smith
>
> 456 So Main Ln Greenfield 01301
>
> Jane Ann Smith
>
> (413) 222-2222
>
> 78 Main Ct Greenfield 01301
>
>
>
> Note that all addresses will include 'Greenfield 01301' and, *if *the
> data includes a phone number, it will start with '(413)'.
>
>
>
> Output should look like this:
>
>
>
> John,Doe,(413) 111-1111,123 First St,Greenfield,01301
>
> Jane,Smith,,456 So Main Ln,Greenfield,01301
>
> Jane Ann,Smith,(413) 222-2222,78 Main Ct,Greenfield,01301
>
>
>
> Any suggestions greatly appreciated. Thanks.
>
>
>
> David
>
>
> _______________________________________________
> Hidden-discuss mailing list - home page: http://www.hidden-tech.net
> Hidden-discuss at lists.hidden-tech.net
>
> You are receiving this because you are on the Hidden-Tech Discussion list.
> If you would like to change your list preferences, Go to the Members
> page on the Hidden Tech Web site.
> http://www.hidden-tech.net/members
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.hidden-tech.net/pipermail/hidden-discuss/attachments/20171022/9c36bcbd/attachment.html