import email accounts from csv

This is an example script for importing email accounts from a csv file. To use it you need a remote api user setup in ISPConfig (set name/password/url in the script), and you need to have already created the client and added the mail domain (Email > Domain), this will only create the email mailboxes. It is safe to re-run on the same csv/accounts, it will only import ones that do not already exist.

The csv file needs to have header names as the first row, and it uses field names to match the api parameters, with a few additions: 'bcc' is simply combined with 'cc' when imported, 'virus_lover' and 'spam_lover' are Y/N flags used to select the spamfilter policy. Extra fields in the csv and order of the fields shouldn't matter, though it can report csv errors better if the first field is 'email'. It'll look like:

Code:

"email","password","quota","name","cc","bcc","move_junk","autoresponder","autoresponder_text","virus_lover","spam_lover"
"[email protected]","insecure","150","API User Insert: Standard Mailbox","","","yes","no","this is vacation text, although vacation is not enabled","N","N"
"[email protected]","insecure","150","API User Insert: Mailbox with move_junk off","","","no","no","this is vacation text, although vacation is not enabled","N","N"
"[email protected]","insecure","150","API User Insert: Mailbox with vacation","","","yes","yes","this is vacation text, with vacation enabled","N","N"
"[email protected]","insecure","150","API User Insert: Mail Forward","[email protected]","","no","no","this is vacation text, although vacation is not enabled","N","N"
"[email protected]","insecure","150","API User Insert: Mailbox with forward via cc","[email protected]","","yes","no","this is vacation text, although vacation is not enabled","N","N"
"[email protected]","insecure","150","API User Insert: Mailbox with forward via bcc","","[email protected]","yes","no","this is vacation text, although vacation is not enabled","N","N"
"[email protected]","insecure","150","API User Insert: Mailbox with virus_lover","","","yes","no","","Y","N"
"[email protected]","insecure","150","API User Insert: Mailbox with spam_lover","","","yes","no","","N","Y"
"[email protected]","insecure","150","API User Insert: Mailbox with virus_lover and spam_lover","","","yes","no","","Y","Y"

For my particular use case, I was exporting email accounts from a DTC hosting panel, though this should adapt fairly easily to other panels with data stored in mysql. Assuming the new server is a cleaner install and less likely to have been hacked in times past, I am careful to only allow credentialed access from the new to the old, not vice-versa, so I exported the csv file on the old DTC server, scp'd the file to the new server (from cli on the new server), and ran this import script on the new server. CSV was exported from the 'dtc' database with (this is of course specific to the DTC hosting panel):

Code:

(select 'email', 'password', 'quota', 'name', 'cc', 'bcc', 'move_junk',
'autoresponder', 'autoresponder_text', 'virus_lover', 'spam_lover' )
union
(select fullemail, passwd, quota_size, IFNULL(memo, ''),
IFNULL(redirect1, ''), IFNULL(redirect2, ''), spam_mailbox_enable,
vacation_flag, vacation_text, virus_lover, spam_lover
   INTO OUTFILE '/var/lib/mysql-files/DOMAIN_pop_access.csv'
   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
   ESCAPED BY '"'
   LINES TERMINATED BY '\n'
from pop_access where mbox_host = 'domain.tld'
)

You should change the domain to match the one(s) you are exporting (or remove the entire 'where' clause, it should work fine with email addrs from multiple domains), and optionally the csv file name. If you can't write the csv to /var/lib/mysql-files/, check for a usable path with: SHOW VARIABLES LIKE "secure_file_priv";

For developers, the most interesting piece is the apiCall() function, which builds on restCall() from rest_example.php, see the various examples of use throughout the script. I'll send a merge request to include in upstream examples.

Each new email account gets sent the ISPConfig welcome email, which is an issue for this client, so I stopped postfix before importing, then removed all the welcome messages from mailq before starting up postfix again.

Be sure to shred your source data (all copies of the csv and data on the source server, if applicable) when you're done.

 

The example script above uses mail_user_add and mail_user_get to read/add mail_user entries, there are similar api calls for mail forwards like mail_forwarding_add and mail_forwarding_get; those of course need different data fields sent to the api call, check the docs for info.