Email Mass importation

Discussion in 'General' started by marc.bechade, Jan 5, 2012.

  1. marc.bechade

    marc.bechade New Member

    Hi everybody,
    I have to migrate a full mail system from a service provider solution to a dedicated company server.
    I've created a csv file with username, password, domain.
    I could add quota to this csv file if required.
    Is there any way to import this file to create all mailboxes instead of manually create each user mailbox into ISP interface ?

    Many thanks for your help.
  2. falko

    falko Super Moderator ISPConfig Developer

  3. till

    till Super Moderator Staff Member ISPConfig Developer

    There is a mailbox configuration import tool available that imports the mailbox details from a csv file.Please see attached .zip file. It conains a example csv file and the tool incl the install script. The tool gets installed into the tools section of ispconfig.

    Attached Files:

  4. marc.bechade

    marc.bechade New Member

    Hi Falko,
    Many thanks for your help.
    I think I wasn't clear enough about my question, so sorry about that.
    With the suggested tools, I will be able to transfer existing emails from the previous email box to the new one. And that's great.
    The thing is I have around 600 users to shift from our actual email system to the new one (managed through ISP config).
    So my goal is to be able to mass import email users automatically into ISP by using a csv file containing:
    username, password, domain, quota
    This method will avoid me to manually recreate one by one each user into ISP Config.
    So my question is to know if there is any way to recreate email users from any type of file into ISP config.

    Many thanks for your time and help.

    Kind regards.

  5. till

    till Super Moderator Staff Member ISPConfig Developer

    Please see post #3 in this thread.
  6. marc.bechade

    marc.bechade New Member

    Sorry Till, We were writting at the same time.
    I'm having a look and let you know.
    Many thanks for your help.

  7. marc.bechade

    marc.bechade New Member

    Great, this is doing exactly what I want.
    Many Thanks again for your help ;)
  8. marc.bechade

    marc.bechade New Member

    Hi again ^^,
    I've used Till's script to create and import my email users.
    After checking some accounts with the webmail, I've noticed passwords aren't setup properly.
    Looking at the example, my csv files seems to look good. I've tries to add a space before/after the password to login the webmail but still doens't work.
    If I change the mailbox password using ISP, it's working fine for some boxes, and for some other I've got a "imap error" when I try to log into the webmail.
    Should I use encrypted password instead of clear password into the csv file ?
    Or any clue ?

    Kind regards
  9. till

    till Super Moderator Staff Member ISPConfig Developer

    Clear password in the file is ok and there should be no space in front or after the password.
  10. marc.bechade

    marc.bechade New Member

    I've respected the file format:
    login name; full name; mail address;password;mailbox quota
    Mailboxes are correctly created using mass import tool (email domain is automatically created too).
    If I try to connect to any emailbox through webmail, I have "bad login" error.
    I I change the password and try to connect again, no problem I can access.
    So at this time, I can't see any error in my csv file or any problem.
    Do you have any idea of why this problem is happening ? and any idea of how I can import properly the password or fix the issue ?
    Changing manually each user password will be a real hassle, so if there is any other way, I will be happy.
    Many thanks for everything.

  11. till

    till Super Moderator Staff Member ISPConfig Developer

    The tool worked fine for me in the past for importing mail accounts for our customers. The tool is provided as it is for free and without any warranty. You can e.g. debug the code to see if the password string contains any additional chars or whitepscaes etc. before the password is encrypted.
  12. marc.bechade

    marc.bechade New Member

    Error found, Database wasn't populated properly.
    In fact username was empty into db.
    I've fixed that by running
    on ispconfig db.
  13. flyingscubakev

    flyingscubakev New Member HowtoForge Supporter

    I just tried the zip file and could not log in. When I checked the db the username ("login" in the database) was missing. I'm not sure where to run that update command and if it complete.

    This is the insert command from the import script, again I'm not sure but isn't the section to add the login missing?

    $insert_data = "(sys_userid,sys_groupid,sys_perm_user,sys_perm_group,sys_perm_other,server_id,
    			VALUES ('$sys_userid','$sys_groupid','riud','riud','','$server_id','".$user.'@'.$domain."','$password_enc',
  14. marc.bechade

    marc.bechade New Member

    I've run
    update `mail_user` set login=email where login=''; 
    directly into my server shell.
    You can login into your sql server by using
    mysql -u root -p
    once logged, run the command line.


    log into phpmyadmin, and run the command from there.

    Kind regards
  15. flyingscubakev

    flyingscubakev New Member HowtoForge Supporter

    Thanks for clearing that up. I went ahead and tried the following additions to the original script and it worked. I hope that I didn't forget anything or mess it up.
    I added the following to the section that had other lines that are similar;

    $tmp['login'] = $login_name;
    Then I added "login" between "email" and "`password`", and then "'$login_name'" between '".$user.'@'.$domain."' and '$password_enc' of the $insert_data line.

    $insert_data = "(sys_userid,sys_groupid,sys_perm_user,sys_perm_group,sys_perm_other,server_id,
    			VALUES ('$sys_userid','$sys_groupid','riud','riud','','$server_id','".$user.'@'.$domain."','$login_name','$password_enc',
    Again, hope that is all that is required and I'm not misleading anyone with wrong code in the wrong place! (I'm new at this :))
  16. Bookworm

    Bookworm New Member

    I'll attest to the fact that the script is broken when it comes to the 'login name' field.

    flyingscubakev's changes _do_ fix the issue in the import script. Otherwise, the field in mysql is actually blank, which is why nobody can log in. As soon as you change the password, it updates the user - duplicating the email address to the login name field. So it's not actually changing the password that 'fixes' the problem.

    Now I get to do a purge on the database so I can get the emails back in order again. They're extremely scrambled at the moment.
  17. st2xo

    st2xo New Member

    Try this patch on /etc/dovecot-sql.conf and restart dovecot - without the changes from flyingscubakev
    It resolved exaktly the same behaviour/problem on my system as described in this thread on the german forum.
  18. maileh

    maileh New Member

    how to run these script

    Please can you give detail instruction on how to use this attached files ???
  19. maileh

    maileh New Member

    Please can you send me the detail of how to use this attachment file to migrate mailbox.
  20. till

    till Super Moderator Staff Member ISPConfig Developer

    This is a tool to import mailboxes from a csv file, it does not mifrate mailbox contents. for migrating mailbox data, see the other psts in this thread e.g. about imapsync or you copy the mailbox data directly.

Share This Page