[CentOS] excel parser (preferably perl)?

[Les Mikesell]

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.

-- 
   Les Mikesell
     lesmikesell at gmail.com