Code snippet: Export simplenews subscribers from drupal to CSV

Drupal LogoSome of you may know the newsletter module simplenews for drupal. If you’ve ever tried it, I am sure you soon realized, it’s a mess. The template functionality is too complicated and poor, emails are send via cron, you have no statistics and only poor ones with other extending modules…

Because of that, I think many people will change the newsletter system and maybe outsource it, so that you have to export your existing simplenews subcribers with username and some more data, than just the email address. Unfortunately there is no functionality for that build in  and so I wrote some tiny PHP (yes, I know – again! But the server did not support ruby, so there was no choice) script for doing that job.  From the simplenews module you can only export a CSV list of all the email addresses, that subscribed, but will not get any further user information, if the user comes from your community.

Here’s a script that takes the exported CSV and fetches the user name from the table, to write everything in another CSV file. With some easy modifications you can extend the script for fetching more data or not writing a second file – for me this was enough.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
< ?php
  define('SQL_USER', ''); // fill in your SQL user name
  define('SQL_PASS', ''); // fill in your SQL password
  define('SQL_SERV', 'localhost');
  define('SQL_DB',   ''); // fill in your drupal SQL database 
 
  // SQL connection
  $link = mysql_connect(SQL_SERV, SQL_USER, SQL_PASS);
  if( $link === false || mysql_select_db(SQL_DB) === false ) {
    echo mysql_error();
    return 1;
  }
 
  // Read in existing mail addresses
  $csvData = array();
  $fd = fopen('mails.csv', 'r');
  while( $data=fgetcsv($fd) ) {
    $csvData[] = $data;
  }
  fclose($fd);
 
  // Process the data and try to find a username in the database
  $out = '';
  foreach( $csvData[0] as $data ) {
    $name = '';
    $query = "SELECT `name` FROM `users` WHERE `mail`='$data'";
    $name = @mysql_result(mysql_query($query), 0);
    $out .= "$data,,$name\r\n";
  }
  $fd = fopen('output.csv', 'w+');
  fwrite($fd, $out);
  fclose($fd);
 
  // Close SQL connection
  if( $link !== false ) {
    mysql_close($link);
  }
?>

The mail addresses should be exported to a file named “mail.csv” or you have to change the file name in the code (line 16). After exporting the simplenews addresses and running the script you should find a file called “output.csv” in the execution directory of the script.

Similar Posts:

  • nursing schools

    Valuable info. Lucky me I found your site by accident, I bookmarked it.

  • Nice fill someone in on and this post helped me alot in my college assignement. Thanks you as your information.

  • I would like to exchange links with your site javacoffee.de
    Is this possible?

  • Jeff

    A couple of comments

    – if Simplenews is a mess, what would you use instead (that is open source and works with Drupal)?
    – you obviously have skills in PHP, perhaps you could offer suggestions to help improve Simplenews?
    I am not sure that Simplenews is a mess – it’s just simple and lacks features. But there are people in the Drupal community making a real effort to make it better.

    There are pay-by-the-month services which are great for people in the commercial world. But if your Drupal website is not about making money, a solution like Simplenews is a big help.

    Phplist is an alternative. It’s more sophisticated than Simplenews, but a little bit harder.

    The next choices are good, but are not designed for shared hosting and require at least a virtual server which most smaller community sites do not have.