import email accounts from csv

Discussion in 'Tips/Tricks/Mods' started by Jesse Norell, Aug 7, 2019.

Tags:
  1. Jesse Norell

    Jesse Norell Well-Known Member

    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.
     

    Attached Files:

    ahrasis, Dan Trautman and till like this.
  2. Dan Trautman

    Dan Trautman New Member

    This is an amazing script. Thank you very much. You have saved me countless hours of manual entry that I don't have.
    As an aside for anyone else attempting this: you need to turn this into a script by adding .sh to the end of the file name (I'm using CentOS) and then run it.
     
  3. AndiStern

    AndiStern New Member

    Thank you very much, Jesse, for that script. Two facts worth mentioning:
    1. If you don't have passwords in clear text, you are not finished.
    2. mysql INTO OUTFILE clause somtimes omits closing doublequotes - so you have to inspect the resulting outfile
     

Share This Page