TITLE: Using Plain Text to Scrub Spreadsheet Files
AUTHOR: Eugene Wallingford
DATE: September 11, 2011 11:27 AM
DESC: 
-----
BODY:
For a few years, I have been saving many <TT>.xls</TT> as plain
text.  This is handy when I want to program against the data
more often than I want to use spreadsheet tools.  It also makes
the data more accessible across apps and platforms, which is
good now and
<A HREF="http://www.cs.uni.edu/~wallingf/blog/archives/monthly/2011-09.html#e2011-09-09T14_19_09.htm">
   in the future</A>.
While doing this, I have came across this technique that I find
useful more generally.  Maybe you will, too.

People use spreadsheets used for two purposes, structuring data
and presenting data.  Both Excel and Apple's Numbers offer as
much or more functionality for presenting information as they
do for manipulating it.  For me, the presentation stuff often
gets in the way of organizing and manipulating, both by
cluttering the UI with commands I don't need to know about and
by adding formatting information to my data.  The result is a
UI more complicated than I need and data files much larger than
I need them to be.

When I run into one of those bloated files, I sometimes take a
round trip:
<UL>
<LI> export the file as <TT>.csv</TT> or <TT>.tsv</TT> </LI>
<LI> import that text file back into Numbers or OpenOffice as
     a spreadsheet file  </LI>
</UL>

The result is a clean data file, with the data and its basic
structure, but nothing more.  No text formatting, no variably
spaced rows or columns, and no presentation widgets.  When I
do work with the data in the spreadsheet app, it's unadorned,
just as I like it.
-----