Difference between revisions of "Csv import"

(CSV import)
(Speed)
 
(23 intermediate revisions by 4 users not shown)
Line 1: Line 1:
== Create OX Contexts and OX Users from CSV Data  ==
+
== Create OX Contexts and OX Users from CSV Data  ==
  
It is possible to create a batch of contexts and users with the "createcontext"  and "createuser" commandline tool to make the migration of existing data easier.  
+
It is possible to create a batch of contexts and users with the "createcontext"  and "createuser" commandline tool to make the migration of existing user accounts and tenants (OX contexts) easier.  
  
For this you need to export your existing userdata to a CSV (Comma-Separated Values) file with the following structure:
+
To accomplish this task, you have to execute 2 steps. First step is to create all needed OX contexts where your OX user accounts will later exist in. Second step is to create all OX user accounts inside the just created OX contexts.
  
Note: The first line defines the fields and have to be in every file you want to
+
For these 2 tasks, you need to 2 kind of data files.  
import. Due to linebreaks you will probably see more than 3 lines, just copy and paste the example to an editor of your choice or reduce the font size.
 
  
=== Generate new contexts  ===
+
One is which will hold all data needed to create new OX contexts including the context admin informations. And the second one must contain all of OX user account data. Both must be specified in CSV format. See the example CSV data below on this page.
 +
 
 +
In fact you have to know/provide the same data in the CSV file as you would need to create an OX user account via the Commandline tools or RMI/SOAP API.
 +
 
 +
Note: The first line in each of the 2 data files, defines the column names and has to be in every file you want use in the batch creation process. The column names must be exactly like "long options" of the commandline tools. You can check for all possible "columns/long options" via "createuser --extendedoptions" and "createcontext --extendedoptions".  You can either create 1 big files for all contexts and one big file for your user accounts, or you can create 1 file for each context and one file for all of the users inside this context. It´s totally up you how you want to process the files.
 +
 
 +
=== Creating new contexts  ===
 
If you need to initially create new contexts into OX via CSV import functionality, you have to create CSV data which cover the context informations and also the informations which are needed for the context admin user.  
 
If you need to initially create new contexts into OX via CSV import functionality, you have to create CSV data which cover the context informations and also the informations which are needed for the context admin user.  
  
In general, if you want to let the context admin use the OX GUI too, you also have to set  
+
<pre>
 +
contextid,addmapping,username,password,displayname,givenname,surname,email,quota,access-combination-name,language,timezone,contextname
 +
"100","loginmapping_100","contextadmin","contextadminpassword","Context Admin 100","Context 100","Admin","admin_100@mymailserver.org","1024","all","en_GB","Europe/London","ctx100"
 +
"101","loginmapping_101","contextadmin","contextadminpassword","Context Admin 101","Context 101","Admin","admin_101@mymailserver.org","1024","all","en_GB","Europe/London","ctx101"
 +
"102","loginmapping_102","contextadmin","contextadminpassword","Context Admin 102","Context 102","Admin","admin_102@mymailserver.org","1024","all","en_GB","Europe/London","ctx102"
 +
"103","loginmapping_103","contextadmin","contextadminpassword","Context Admin 103","Context 103","Admin","admin_103@mymailserver.org","1024","all","en_GB","Europe/London","ctx103"
 +
"104","loginmapping_104","contextadmin","contextadminpassword","Context Admin 104","Context 104","Admin","admin_104@mymailserver.org","1024","all","en_GB","Europe/London","ctx104"
 +
"105","loginmapping_105","contextadmin","contextadminpassword","Context Admin 105","Context 105","Admin","admin_105@mymailserver.org","1024","all","en_GB","Europe/London","ctx105"
 +
</pre>
 +
 
 +
This data is needed to create 5 new OX contexts including the mandatory contextadmin user. Modify the context id, password etc. for your needs.
 +
 
 +
To create the contexts from a CSV file, copy above data into new file called "contexts_list.csv" and execute following command:
 +
 
 +
<pre>
 +
$ /opt/open-xchange/sbin/createcontext -A oxadminmaster -P secret --csv-import contexts_list.csv
 +
</pre>
 +
 
 +
Once you have created these contexts, you should be able to login to OX GUI (if Database authentication Plugin is used) with username "contextadmin@<ID_OF_CONTEXT_OR_LOGINMAPPING>" and password "contextadminpassword".
 +
 
 +
In general, if you want to let the "contextadmin" use the OX MAIL MODULE, you also have to set  
 +
 
 +
<pre>
 
mail.properties:com.openexchange.mail.adminMailLoginEnabled=true
 
mail.properties:com.openexchange.mail.adminMailLoginEnabled=true
 +
</pre>
 +
 +
in the file
 +
<code>/opt/open-xchange/etc/groupware/mail.properties</code>
 +
 +
===Creating users inside contexts ===
 +
 +
<pre>contextid,adminuser,adminpass,username,displayname,givenname,surname,password,email,imaplogin,imapserver,smtpserver,quota,access-combination-name,language,timezone
 +
"105","contextadmin","contextadminpassword","pete_test","Pete Test","Pete","Test","secret","pete_test@mymailserver.org","pete_test","imap://imapserver:143","smtp://mailserver:25","1024","all","de_DE","Europe/Berlin"
 +
"104","contextadmin","contextadminpassword","sandra_linux","Sandra  Linux","Sandra","Linux","secret","sandra_linux@mymailserver.org","sandra_linux","imap://imapserver:143","smtp://mailserver:25","1024","all","en_GB","Europe/London"</pre>
  
in the file <code>/opt/open-xchange/etc/groupware/mail.properties</code>
+
This example has two users, pete_test and sandra_linux, which will be created in the previously created context 105 and in context 104. As you can see, it is possible to merge users from different contexts in a single CSV input file. Of course, it is also possible to create a CSV user file per context and use it after the context was created. It is now totally up to you, how you handle the workflow.
  
<pre>adminuser,adminpass,contextid,username,displayname,givenname,surname,password,email,imaplogin,imapserver,smtpserver,quota,access-combination-name,language,timezone
+
To import the users:
pete_test,secret,110,pete_test,Pete Test,Pete,Test,secret,pete_test@mymailserver.org,pete_test,imap://imapserver:143,smtp://mailserver:25,1024,all,de_DE,Europe/Berlin
 
sandra_linux,secret,111,sandra_linux,Sandra  Linux,Sandra,Linux,secret,sandra_linux@mymailserver.org,sandra_linux,imap://imapserver:143,smtp://mailserver:25,1024,all,en_GB,Europe/London</pre>
 
  
This will make two new contexts, 110 with pete_test and 111 with as the contextadmin and the only user.
+
$ /opt/open-xchange/sbin/createuser --csv-import myuserlist.csv
  
To create the contexts from a CSV file:
+
=== Additional attributes ===
 +
The examples cover only the most common attributes. If you want to import other information check the --extendedoptions for a list of possible options (this work both for createuser and createcontext):
  
  $ /opt/open-xchange/sbin/createcontext -A oxadminmaster -P secret --csv-import contexts_list.csv
+
  createuser --extendedoptions
  
=== Importing users to an existing context ===
+
You can use all double dash options directly in your field definition. All short options like -L have also a long optionname, in this example --addmappings.
<pre>adminuser,adminpass,contextid,username,displayname,givenname,surname,password,email,imaplogin,imapserver,smtpserver,quota,access-combination-name,language,timezone
 
oxadmin110,secret,110,pete_test,Pete Test,Pete,Test,secret,pete_test@mymailserver.org,pete_test,imap://imapserver:143,smtp://mailserver:25,1024,all,de_DE,Europe/Berlin
 
oxadmin110,secret,110,sandra_linux,Sandra  Linux,Sandra,Linux,secret,sandra_linux@mymailserver.org,sandra_linux,imap://imapserver:143,smtp://mailserver:25,1024,all,en_GB,Europe/London</pre>
 
  
oxadmin110 is the adminuser of the context 110 with the password secret. There will be 2 users  imported to the context (110), pete_test and sandra_linux. To import the users:
+
=== Speed ===
 +
The import speed heavily depends on your hardware and infrastructure. A test on a virtual machine (Laptop, 2.5Ghz, 2GB Ram, all services and OX on the same system) imported 1000 contexts in 37 seconds and additional 900 users in 20 seconds. Your mileage my vary depending on the amount of contexts/users.
  
  $ /opt/open-xchange/sbin/createuser --csv-import myuserlist.csv
+
Other installations have imported 560.000 user in 11 hours, which included the creation of 77.000 contexts. This was done by running 1 import shell for contexts and after that 1 import shell for creating users inside the contexts. After testing optimizations, the import of the 560.000 users was done in 2 hours and 20 minutes, by running 3 shells, each on a different node and reading a dedicated csv file. So, the user CSV list was splitted in 3 parts and each part was imported by a seperate shell at the same time. One import node was running with 32GB of RAM, the other two with 14GB of RAM. Each having a quad core CPU.
  
 
=== Definitions ===
 
=== Definitions ===
Language codes: you can use the same you have in the language menue for the user, like de_DE, en_GB, en_US, jp_JP ...
+
Language codes: you can use the same you have in the language menue for the user, like de_DE, en_GB, en_US, jp_JP ..., check [[Available_Translations]] for a list of what's supported.
  
 
A list of timezones:
 
A list of timezones:
 
[http://en.wikipedia.org/wiki/Zone.tab Wikipedia:Zone.tab]
 
[http://en.wikipedia.org/wiki/Zone.tab Wikipedia:Zone.tab]

Latest revision as of 10:58, 20 September 2011

Create OX Contexts and OX Users from CSV Data

It is possible to create a batch of contexts and users with the "createcontext" and "createuser" commandline tool to make the migration of existing user accounts and tenants (OX contexts) easier.

To accomplish this task, you have to execute 2 steps. First step is to create all needed OX contexts where your OX user accounts will later exist in. Second step is to create all OX user accounts inside the just created OX contexts.

For these 2 tasks, you need to 2 kind of data files.

One is which will hold all data needed to create new OX contexts including the context admin informations. And the second one must contain all of OX user account data. Both must be specified in CSV format. See the example CSV data below on this page.

In fact you have to know/provide the same data in the CSV file as you would need to create an OX user account via the Commandline tools or RMI/SOAP API.

Note: The first line in each of the 2 data files, defines the column names and has to be in every file you want use in the batch creation process. The column names must be exactly like "long options" of the commandline tools. You can check for all possible "columns/long options" via "createuser --extendedoptions" and "createcontext --extendedoptions". You can either create 1 big files for all contexts and one big file for your user accounts, or you can create 1 file for each context and one file for all of the users inside this context. It´s totally up you how you want to process the files.

Creating new contexts

If you need to initially create new contexts into OX via CSV import functionality, you have to create CSV data which cover the context informations and also the informations which are needed for the context admin user.

contextid,addmapping,username,password,displayname,givenname,surname,email,quota,access-combination-name,language,timezone,contextname
"100","loginmapping_100","contextadmin","contextadminpassword","Context Admin 100","Context 100","Admin","admin_100@mymailserver.org","1024","all","en_GB","Europe/London","ctx100"
"101","loginmapping_101","contextadmin","contextadminpassword","Context Admin 101","Context 101","Admin","admin_101@mymailserver.org","1024","all","en_GB","Europe/London","ctx101"
"102","loginmapping_102","contextadmin","contextadminpassword","Context Admin 102","Context 102","Admin","admin_102@mymailserver.org","1024","all","en_GB","Europe/London","ctx102"
"103","loginmapping_103","contextadmin","contextadminpassword","Context Admin 103","Context 103","Admin","admin_103@mymailserver.org","1024","all","en_GB","Europe/London","ctx103"
"104","loginmapping_104","contextadmin","contextadminpassword","Context Admin 104","Context 104","Admin","admin_104@mymailserver.org","1024","all","en_GB","Europe/London","ctx104"
"105","loginmapping_105","contextadmin","contextadminpassword","Context Admin 105","Context 105","Admin","admin_105@mymailserver.org","1024","all","en_GB","Europe/London","ctx105"

This data is needed to create 5 new OX contexts including the mandatory contextadmin user. Modify the context id, password etc. for your needs.

To create the contexts from a CSV file, copy above data into new file called "contexts_list.csv" and execute following command:

 $ /opt/open-xchange/sbin/createcontext -A oxadminmaster -P secret --csv-import contexts_list.csv

Once you have created these contexts, you should be able to login to OX GUI (if Database authentication Plugin is used) with username "contextadmin@<ID_OF_CONTEXT_OR_LOGINMAPPING>" and password "contextadminpassword".

In general, if you want to let the "contextadmin" use the OX MAIL MODULE, you also have to set

mail.properties:com.openexchange.mail.adminMailLoginEnabled=true

in the file /opt/open-xchange/etc/groupware/mail.properties

Creating users inside contexts

contextid,adminuser,adminpass,username,displayname,givenname,surname,password,email,imaplogin,imapserver,smtpserver,quota,access-combination-name,language,timezone
"105","contextadmin","contextadminpassword","pete_test","Pete Test","Pete","Test","secret","pete_test@mymailserver.org","pete_test","imap://imapserver:143","smtp://mailserver:25","1024","all","de_DE","Europe/Berlin"
"104","contextadmin","contextadminpassword","sandra_linux","Sandra  Linux","Sandra","Linux","secret","sandra_linux@mymailserver.org","sandra_linux","imap://imapserver:143","smtp://mailserver:25","1024","all","en_GB","Europe/London"

This example has two users, pete_test and sandra_linux, which will be created in the previously created context 105 and in context 104. As you can see, it is possible to merge users from different contexts in a single CSV input file. Of course, it is also possible to create a CSV user file per context and use it after the context was created. It is now totally up to you, how you handle the workflow.

To import the users:

$ /opt/open-xchange/sbin/createuser --csv-import myuserlist.csv

Additional attributes

The examples cover only the most common attributes. If you want to import other information check the --extendedoptions for a list of possible options (this work both for createuser and createcontext):

createuser --extendedoptions

You can use all double dash options directly in your field definition. All short options like -L have also a long optionname, in this example --addmappings.

Speed

The import speed heavily depends on your hardware and infrastructure. A test on a virtual machine (Laptop, 2.5Ghz, 2GB Ram, all services and OX on the same system) imported 1000 contexts in 37 seconds and additional 900 users in 20 seconds. Your mileage my vary depending on the amount of contexts/users.

Other installations have imported 560.000 user in 11 hours, which included the creation of 77.000 contexts. This was done by running 1 import shell for contexts and after that 1 import shell for creating users inside the contexts. After testing optimizations, the import of the 560.000 users was done in 2 hours and 20 minutes, by running 3 shells, each on a different node and reading a dedicated csv file. So, the user CSV list was splitted in 3 parts and each part was imported by a seperate shell at the same time. One import node was running with 32GB of RAM, the other two with 14GB of RAM. Each having a quad core CPU.

Definitions

Language codes: you can use the same you have in the language menue for the user, like de_DE, en_GB, en_US, jp_JP ..., check Available_Translations for a list of what's supported.

A list of timezones: Wikipedia:Zone.tab