exporting MySQL data for mail merge

Report · Apr 27, 2011

Hi

I'm using MySQL to store email addresses for a mailing list. I've set up a page which simply displays the data which I can then copy/paste into Excel to merge with my newsletter. It is a bit untidy. It would be really nice to be able to have a button on the page which would export the table directly to a file. Does anyone know how I could do this?

Thanks

Eric Lewis

Report · Apr 28, 2011

I'm not sure if this is possible in older versions of Excel but for 2007/2010 you can install: http://dev.mysql.com/downloads/connector/odbc/

Then click on the Data tab in Excel click on Connections, setup the connection to your mySQL server. Then run a query as you would on a webpage to collect the data. Then you have the data in your Excel spreadsheet that you can manually refresh or set to auto-refresh on document open and you are set to do the mail merge. You will have to open Excel before Word to make sure the refresh happens, but that is a minor inconvenience.

If you have the Mac version the same is possible but MS does not allow ODBC drivers in Mac Excel without commercial 3rd party plugins. This is the biggest disadvantage the Mac version of Office has at the moment.

Report · Apr 28, 2011

Hi

I was hoping for something a bit simpler than that. My version of Excel (2007) allows you to get data from a table on a website page directly anyway, but the page that I want to set up is part of a website admin system. These people have little IT knowledge so a 'one click to download this list button' even as a plain text or csv file would be ideal.

Perhaps it's too much to ask?

Eric Lewis

Report · Apr 28, 2011

quantum503 wrote:
Hi
I was hoping for something a bit simpler than that. My version of Excel (2007) allows you to get data from a table on a website page directly anyway, but the page that I want to set up is part of a website admin system. These people have little IT knowledge so a 'one click to download this list button' even as a plain text or csv file would be ideal.
Perhaps it's too much to ask?
Eric Lewis

Yea the Excel grabbing a table from a webpage is really not as accurate as the mySQL. I tend to stay away from it because if pages get redesigned Excel can easily lose track of things whereas with the Data connection it is more reliable and queries against the database and is transparent to the end user once the document is configured.

Only other way would be to write to the file right from the query using: SELECT * INTO OUTFILE "pathto/myfile/onserver.csv" FIELDS TERMINATED BY "," ...

http://dev.mysql.com/doc/refman/5.0/en/select.html

Actual wording will vary by your setup and if you want to be more specific about the query that would make the operation more efficient (eg: only get a first name and email instead of all fields).

Report · Apr 29, 2011

Unfortunately MySQL is on a shared server that doesn't support that. But I have solved it by looping through each row of a query and passing the output to a function to write it to a text file that can be downloaded and used as the data source. Simple (like me) but effective.

Thanks for your help.

Eric Lewis