[CentOS] excel parser (preferably perl)?

Tue Oct 19 16:48:14 UTC 2010
Toby Bluhm <toby.bluhm at alltechmedusa.com>

On 10/19/2010 11:17 AM, Les Mikesell wrote:
> On 10/19/2010 9:34 AM, Todd Denniston wrote:
>>> I'm getting tired of converting spreadsheets that someone else updates
>>> to csv so my perl scripts can push the data into a mysql database.  Is
>>> there a better way?  I haven't had much luck with
>>> perl-Spreadsheet-ParseExcel (and find it odd that yum prefers the .32
>>> version from epel over .57 from rpmforge anyway).  Is the current CPAN
>>> version better?  Or the equivalent java tools?  Or maybe a scripted
>>> OpenOffice conversion would be possible.
>>> Needs to deal with both xls and xlsx formats, the odd characters that
>>> are confused with quotes even after csv conversion, numbers with $'s and
>>> commas embedded, excel's date formatting nonsense, etc.
>> Would it cause more headaches than it would solve, for you to hook the excel folks directly to the
>> mysql db and have their changes take place immediately? Assuming a LAN environment here instead of
>> 'the only connection is email'.
> That's pretty much impossible in the near term anyway.  The bulk of this
> involves reconciling inventory data maintained by one set of people for
> financial purposes in a database I don't control with some others used
> for operational monitoring and management.  For example, we need to be
> able to report the current value of the set of equipment being used for
> a particular purpose - where servers are being shuffled around for
> different purposes all the time.  I'm using ocsinventory-ng for
> operational tracking because the agents keep it updated automatically
> but it only handles computers and by itself doesn't deal with cost or
> deprecation.  New requirements keep popping up as we go and I don't find
> out about them until someone sends me a spreadsheet with some new fields
> and a request to add them to the db and populate them so they'll be
> available in future reports.
>> Could you do the sanity checking you currently do by using some db functions?
> Maybe, but doing string operations in sql instead of using perl regexps
> seems a little insane by itself.
>> "MySQL Forums :: Microsoft Access :: Connecting MS Office, MS Excel, MS Access to MySQL using ODBC"
>> http://forums.mysql.com/read.php?65,148441,148441
>> * OK, I often come at problems from a different direction. *
> Yeah, the inconvenient parts could probably be done in vbscript or
> something on a windows box, pushing the results into the db through
> odbc, but I thought this would be a common enough problem that
> cross-platform tools would be available.  I am using some java stuff on
> the reporting side - maybe I should look there for conversion tools too.

I experimented a little with this sometime last year - seemed to work okay: