No. of Recommendations: 1

You wrote, But what if you’re confronted with a file such as the following and want to pull out just the tickers? ...

What's so special about that list? I just copied the list into a text file and saved it as a .CSV file. Then I loaded it into OpenOffice/Calc using the "Text Import" dialog. For separators, I selected "Other" and gave the text "-–". Each character in that string gets treated as a separator, so it digested both types of dash characters. The only problem with this technique is that some of the fund descriptions contain dashes and get broken up. You can address that by quoting the affected descriptions before importing.

Another technique is to use a text editor that offers regular expression search and replace. For instance, Notepad++ is a free open source editor and quite a good general purpose tool for editing text. In this case, you can just do a search using the regular expression of " [-–] " and replace with ",". This converts space-wrapped dashes (of either type) into a comma, making the text a normal comma-separated (.CSV) file.

FWIW, regular expressions can be very handy in converting one person's presentation into something more digestible for a program. The problem is that you often have to go through a lot of trial and error to get the expression right if the conversion is complicated or consists of a lot of "moving pieces." Sometimes its just easy to massage the dataset manually; but if you're doing it over and over again, figuring out the right set of expressions can look and feel like you're casting some magic spell... ;-)

BTW, Excel and OpenOffice/Calc handle this import in exactly the same manner. In fact OpenOffice/Calc mimics Excel pretty closely. Most functions available in Excel are available in OpenOffice. The problem with OpenOffice is that much of it is written in Java and it's primarily intended to be portable - not fast or efficient. This makes OpenOffice a fairly poor choice if you either have an old/slow machine with little memory or you have to manipulate really huge documents.

- Joel
Print the post  


What was Your Dumbest Investment?
Share it with us -- and learn from others' stories of flubs.
When Life Gives You Lemons
We all have had hardships and made poor decisions. The important thing is how we respond and grow. Read the story of a Fool who started from nothing, and looks to gain everything.
Community Home
Speak Your Mind, Start Your Blog, Rate Your Stocks

Community Team Fools - who are those TMF's?
Contact Us
Contact Customer Service and other Fool departments here.
Work for Fools?
Winner of the Washingtonian great places to work, and Glassdoor #1 Company to Work For 2015! Have access to all of TMF's online and email products for FREE, and be paid for your contributions to TMF! Click the link and start your Fool career.