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@xxxxxxxxx
_______________________________________________
CentOS mailing list
CentOS@xxxxxxxxxx
http://lists.centos.org/mailman/listinfo/centos
