[CentOS] excel parser (preferably perl)?
jses27 at gmail.com
Mon Oct 18 19:16:06 EDT 2010
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'
More information about the CentOS