From MySQL to MailChimp via CSV

Don’t you hate disclaimers? I do, but before I do anything else, I must ask that you don’t use the techniques below unless you are emailing responsibly.

Today I needed to pull email addresses for people who had signed up to a thing out of MySQL and into MailChimp so that I could actually email them about the thing. MySQL actually has a very cute feature for exporting the results of an SQL query as a CSV file, which I had to look up to remember how to do it. It goes something like this:

My original query*

select u.username, u.email, a.account_name
from users u
inner join accounts a on a.user_id = u.id
where a.status_id = 2;

To write this to a file, we use MySQL’s SELECT ... INTO OUTFILE feature, so my query suddenly looks like this:

select u.username, u.email, a.account_name
into outfile '/tmp/users-extract.csv'
from users u
inner join accounts a on a.user_id = u.id
where a.status_id = 2;

By default, this is actually a tab-separated file rather than technically a comma-separated one, but there’s some excellent documentation on how to denote the different fields and lines if you need a different format. I found that the defaults emitted by my server (which is probably mostly vanilla settings) were digested perfectly easily by mailchimp.

If you need to create a CSV file from MySQL by exporting the results of a query – then I hope this post helps! If you have any additional tips, do leave a comment.

* not my actual query, as that went on at some length!

3 thoughts on “From MySQL to MailChimp via CSV

  1. [code]
    select
    concat(‘lastname’,char(44),’firstname’,char(44),’email’)
    into outfile ‘file.csv’;
    [/code]

    I generally use concat() to generate csv file for certain clients.

  2. While I succeded in importing from TXT with tab separated values,
    I am struggling to create a CSV that can import email, first name, last name.
    Can U please send me a CSV template of yours?
    With dummy data of course.

    Best
    Riccardo

Leave a Reply

Please use [code] and [/code] around any source code you wish to share.

This site uses Akismet to reduce spam. Learn how your comment data is processed.