On 10/19/2010 11:17 AM, Les Mikesell wrote: > On 10/19/2010 9:34 AM, Todd Denniston 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. >>> >> >> 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'. > > That's pretty much impossible in the near term anyway. The bulk of this > involves reconciling inventory data maintained by one set of people for > financial purposes in a database I don't control with some others used > for operational monitoring and management. For example, we need to be > able to report the current value of the set of equipment being used for > a particular purpose - where servers are being shuffled around for > different purposes all the time. I'm using ocsinventory-ng for > operational tracking because the agents keep it updated automatically > but it only handles computers and by itself doesn't deal with cost or > deprecation. New requirements keep popping up as we go and I don't find > out about them until someone sends me a spreadsheet with some new fields > and a request to add them to the db and populate them so they'll be > available in future reports. > >> Could you do the sanity checking you currently do by using some db functions? > > Maybe, but doing string operations in sql instead of using perl regexps > seems a little insane by itself. > >> "MySQL Forums :: Microsoft Access :: Connecting MS Office, MS Excel, MS Access to MySQL using ODBC" >> http://forums.mysql.com/read.php?65,148441,148441 >> >> * OK, I often come at problems from a different direction. * > > Yeah, the inconvenient parts could probably be done in vbscript or > something on a windows box, pushing the results into the db through > odbc, but I thought this would be a common enough problem that > cross-platform tools would be available. I am using some java stuff on > the reporting side - maybe I should look there for conversion tools too. > I experimented a little with this sometime last year - seemed to work okay: http://www.artofsolving.com/opensource/jodconverter