[CentOS] excel parser (preferably perl)?

[JohnS]

On Mon, 2010-10-18 at 17:54 -0500, Les Mikesell wrote:
> On 10/18/2010 5:31 PM, JohnS wrote:
> >
> > On Mon, 2010-10-18 at 17:13 -0500, Les Mikesell 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.
> > ---
> > I think you are out of luck on that.  .Net has a whole world of Office
> > Goodies what a shame...
> > Extract the CSV Data then do a insert into MySQL.  Is that how you do it
> > now?
> 
> Yes, someone emails an xls or xlsx, I do a 'save as' csv, but it's not a 
> straight insert after that.  I read it into perl and do some checking 
> and conversions, depending on the data involved, then an insert or 
> update.  I expected the db to be the authoritative copy but I keep 
> getting batches of wholesale modifications to merge in so I'd like to 
> automate it a little more completely.
---
This is a really old way here: It can be scripted though..."LOAD DATA
INFILE" is the key here check it out.

LOAD DATA INFILE '/my.csv' REPLACE INTO TABLE `test` 
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'