I've written some simple VC++ apps to access data in an Excel spreadsheet using ODBC. But I was thinking something like using perl's Win32::ODBC would be simpler. It should be fairly straight forward to read the data from Excel and store it back in MySQL. I think the O'Reilly "Perl DBI" book has a few example you could piece together. Like I mentioned, there is probably a much simpler way to get what your after, but this could be an interesting mini project.
dave -----Original Message----- From: Richard Miller [mailto:[EMAIL PROTECTED] Sent: Thursday, November 13, 2003 11:37 AM To: BYU Unix Users Group Subject: Re: [uug] Import text file (tab or comma separated) into MySQL da tabase I have ODBC set up, but I wasn't aware that anything I could get data FROM Excel TO MySQL that way. I am only familiar with using Excel and MS Query to read data FROM MySQL. Do you know something I don't know in this regard? Richard On Nov 13, 2003, at 11:07 AM, Tucker, David wrote: > You could write an simple program to read the data in from Excel via > ODBC > and then save each field back to the MySQL database. May not be the > simplest method, but it would be an interesting exercise. > > dave > > -----Original Message----- > From: Richard Miller [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 12, 2003 8:05 PM > To: [EMAIL PROTECTED] > Subject: [uug] Import text file (tab or comma separated) into MySQL > database > > > When I switched to Mac OS X (from Windows) earlier this year, I saved > all of my personal email in an Excel spreadsheet. (It was a ridiculous > process: I had to convert it from Outlook Express to Outlook to Access > to Excel.) In this huge spreadsheet, each line is a single email > message, with columns for "To", "From", "Subject", "Body", etc. > > I now want to get the email into a MySQL database using mysqlimport. > Mysqlimport can import comma- or tab-separated files, which Excel can > generate. My problem arises because the body of each email has > newlines after each paragraph (\r on Mac OS X), which is precisely the > character that is used to separate records (messages). > > Here's an example of what an Excel-generated, tab-separated file looks > like: (It has the header and two records.) > > To \t From \t Subject \t Body \r > [EMAIL PROTECTED] \t [EMAIL PROTECTED] \t Hello! \t Hi Richard, > \r > How are you doing?] \r > I just wanted to let you know... \r > ... \r > [EMAIL PROTECTED] \t [EMAIL PROTECTED] \t Thanks \t Mr. Miller, > \r > Thanks for switching to Mac OS X earlier this year. \r > Sincerely, \r > Steve \r > .... \r > > Because the message column has line breaks in it, when I import it into > MySQL, the body only reads "Hi Richard," or "Mr. Miller," and excludes > the rest of the message. > > If I knew what the newline character was in Excel, I would replace it > with some dummy character that I could expand later. If not, it looks > like once I export to a tab-separated file, it's too late to > distinguish between newlines in the message body and newlines that end > each record. > > Any suggestions? > > Richard Miller > > > ____________________ > BYU Unix Users Group > http://uug.byu.edu/ > ___________________________________________________________________ > List Info: http://uug.byu.edu/cgi-bin/mailman/listinfo/uug-list > > ____________________ > BYU Unix Users Group > http://uug.byu.edu/ > ___________________________________________________________________ > List Info: http://uug.byu.edu/cgi-bin/mailman/listinfo/uug-list > > ____________________ BYU Unix Users Group http://uug.byu.edu/ ___________________________________________________________________ List Info: http://uug.byu.edu/cgi-bin/mailman/listinfo/uug-list ____________________ BYU Unix Users Group http://uug.byu.edu/ ___________________________________________________________________ List Info: http://uug.byu.edu/cgi-bin/mailman/listinfo/uug-list
