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.