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. -- Les Mikesell lesmikesell at gmail.com
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? John
On Mon, Oct 18, 2010, 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 don't do much perl these days, having switched to python for most of my stuff. There is at least one python package for this: pypi.python.org/pypi/xlrd A google search on ``python excel reader'' came up with quite a few hits. Of course there are easy python dbi interfaces to mysql, postgresql, and other SQL databases as well. Bill -- INTERNET: bill at celestial.com Bill Campbell; Celestial Software LLC URL: celestial.com PO Box 820; 6641 E. Mercer Way Voice: (206) 236-1676 Mercer Island, WA 98040-0820 Fax: (206) 232-9186 Skype: jwccsllc (206) 855-5792 It is practically impossible to teach good programming style to students that have had prior exposure to BASIC: as potential programmers they are mentally mutilated beyond hope of regeneration. -- Dijkstra
On Mon, Oct 18, 2010 at 3:13 PM, Les Mikesell <lesmikesell at gmail.com> 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.Hi, Les. xlhtml has a switch, -csv, to output in Comma Separated Values chicago.sourceforge.net/xlhtml I am not sure if it'll do everything you want, it's a few years old, but may be worth a look. Best, -at
Les Mikesell wrote, On 10/18/2010 06:13 PM:> 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. >Would it cause more headaches than it would solve, for you to hook the excel folks directly to the mysql db and have their changes take place immediately? Assuming a LAN environment here instead of 'the only connection is email'. Could you do the sanity checking you currently do by using some db functions? "MySQL Forums :: Microsoft Access :: Connecting MS Office, MS Excel, MS Access to MySQL using ODBC" forums.mysql.com/read.php?65,148441,148441 * OK, I often come at problems from a different direction. * -- Todd Denniston Crane Division, Naval Surface Warfare Center (NSWC Crane) Harnessing the Power of Technology for the Warfighter
On 10/18/10 3:13 PM, 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 suspect it would be easiest to implement this as a set of Excel macros that connect to your mysql with ODBC and write the data directly to your database.
On Tue, Oct 19, 2010 at 12:13 AM, Les Mikesell <lesmikesell at gmail.com> 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.No idea about the csv from excel, but I have had good experiences with Text::CSV_XS. -- natxo
On 10/19/2010 09:13 AM, Les Mikesell wrote: ...> 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?...> 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.Looking at the changelog, version .57 of perl-Spreadsheet-ParseExcel fixes some of the above issues. Not clear from the above whether you have tried it or not. The developers would probably be very interested in any examples that break the parser. Kal