[CentOS] excel parser (preferably perl)?

[Les Mikesell]

On 10/18/2010 6:16 PM, JohnS wrote:
>
> 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'

But the piece I want to script is the saving as csv in the first place. 
  Plus ways to work around the gunk that excel can put in a csv file and 
the date format that is nothing like what sql wants.

-- 
   Les Mikesell
     lesmikesell at gmail.com