Import customer user from an outlook CSV file

[xtheb]

Hello,

What is the best way to import customer users from an outlook CSV file ?

1.
I have already search on this topic but if I use script otrs.AddCustomerUser, I can't import Phone for example...

2.
I have tried to insert manually data in customer_user table but if I do that, the line doesn't appear in Customer User Management screen. Example :
insert into customer_user (login, email, customer_id, title, first_name, last_name, phone, fax, mobile, street, zip, city, country, comments, valid_id, create_time, create_by, change_time, change_by) values('toto@hotmail.com', 'toto@hotmail.com', 'totot@hotmail.com', '', 'Toto', 'TOTO PERSO', 'tel', '', 'telmobile', '', '', '', '', '', 1, '2015-04-28 18:00:00', 1, '2015-04-28 18:00:00', 1);

Could you help me please ?

Regards,
Sev

[crythias]

Schema

Code: Select all

CREATE TABLE group_customer_user (
    user_id VARCHAR (100) NOT NULL,
    group_id INTEGER NOT NULL,
    permission_key VARCHAR (20) NOT NULL,
    permission_value SMALLINT NOT NULL,
    create_time DATETIME NOT NULL,
    create_by INTEGER NOT NULL,
    change_time DATETIME NOT NULL,
    change_by INTEGER NOT NULL,
    INDEX group_customer_user_group_id (group_id),
    INDEX group_customer_user_user_id (user_id)

Code: Select all

CREATE TABLE customer_preferences (
    user_id VARCHAR (250) NOT NULL,
    preferences_key VARCHAR (150) NOT NULL,
    preferences_value VARCHAR (250) NULL,
    INDEX customer_preferences_user_id (user_id)
);

If you saw the original post you'd see the strong suggestion to *not* import csv if you can help it.

Note that creating customers does not create demographic information. Demographic info is created by the Map and stored in preferences. Or directly queried from a third party db.

[xtheb]

Hello,

yes I know that but I really need to import contacts from outlook.

I am developer so I can develop a script to import data in these 2 tables, do you think it will works ?

Is there other objects (table, view...) to update ?

Regards,
Sev

[crythias]

You might be able to modify otrs.AddCustomerUser.pl to match additional fields in your CustomerUser Map

Code: Select all

$Param{Source}         = 'CustomerUser';
$Param{UserFirstname}  = $Options{f};
$Param{UserLastname}   = $Options{l};
$Param{UserCustomerID} = defined $Options{c} ? $Options{c} : $ARGV[0];
$Param{UserLogin}      = $ARGV[0];
$Param{UserPassword}   = $Options{p};
$Param{UserEmail}      = $Options{e};

So if you have a UserPhone,

Code: Select all

$Param{UserPhone}      = $Options{1};
$Param{UserCity}       = $Options{2}; 

and then

Code: Select all

otrs.AddCustomerUser.pl {otherstuff not listed here} -1 '(555)555-1212' -2 'Miami'

[crythias]

viewtopic.php?t=12410

[xtheb]

Hello,

I really want to import via sql request but if I do that :
insert into customer_user (login, email, customer_id, title, first_name, last_name, phone, fax, mobile, street, zip, city, country, comments, valid_id, create_time, create_by, change_time, change_by) values('toto_h_fontaine@hotmail.com', 'toto_h_fontaine@hotmail.com', 'toto FONTAINE CORPORATION', '', 'toto PRENOM test', 'FONTAINE NOM TEST', 'tel test', '', 'telmobile test', '', '', '', '', '', 1, '2015-04-28 18:00:00', 1, '2015-04-28 18:00:00', 1);
insert into customer_preferences (user_id, preferences_key, preferences_value) values ('toto_h_fontaine@hotmail.com', 'UserLanguage', 'en_GB');
insert into customer_preferences (user_id, preferences_key, preferences_value) values ('toto_h_fontaine@hotmail.com', 'UserRefreshTime', '');
insert into customer_preferences (user_id, preferences_key, preferences_value) values ('toto_h_fontaine@hotmail.com', 'UserShowTickets', '25');

I can't see the new line in the AdminCustomerUser view ! Do you know why ? Because the sql tables are populated with good data....

Regards,
Sev

[crythias]

I really want to import via sql request

This is not supported at all.

[xtheb]

ok I have succeeded, we just have to have to reset cache with script : otrs.DeleteCache.pl