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.

To respond on your own website, enter the URL of your response which should contain a link to this post's permalink URL. Your response will then appear (possibly after moderation) on this page. Want to update or remove your response? Update or delete your post and re-enter your post's URL again. (Find out more about Webmentions.)