import email accounts from csv

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

Tags:
  1. Jesse Norell

    Jesse Norell ISPConfig Developer Staff Member ISPConfig Developer

    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:

  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
     
  4. frank2018

    frank2018 New Member

    Thx a lot. I need such a script for "Mail Forward". Can you tell me, what i have to change? I have over 50 Email Forwards in a table i have to import. That would be great this easy way.
     
  5. Jesse Norell

    Jesse Norell ISPConfig Developer Staff Member ISPConfig Developer

    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.
     
  6. Glucz

    Glucz New Member

    In case someone runs into this as well.... The script above no longer works. I think it's more the fault of the API than the script itself.
    The error given is this
    mail_user_add returned remote_fault: Incorrect integer value: '' for column `dbispconfig`.`mail_user`.`purge_trash_days` at row 1

    It cannot be fixed by passing an extra param for this in the csv file. The API ignores this and defaults to '' (blank) instead of 0

    At one point I had plans to automate account creation via the API and found a lot of similar cases in other modules. It is very easy to create sites for example with the API that will then not get created due to parameter errors or when created would not show up in the admin area. Parameters have to be hunted down in the database and fixed manually. To get to the point, I think that API needs to check the input data for errors and should not take whatever is given and run with it. Hunting down the problems in the database and in the config files, reverting, and trying again is just too much hassle.
     

Share This Page