[otrs] Re: Getting customer data in to OTRS, fields for address, OS, postcode etc?

Mike Dent schrieb:

On 5/10/07, Shawn Beasley wrote:

...

Mike Dent schrieb:

...

On 5/9/07, Shawn Beasley wrote:

...

Mike Dent schrieb:

...

On 5/8/07, Shawn Beasley wrote:

...

Mike Dent schrieb: > On 5/8/07, Shawn Beasley wrote: >> Mike Dent schrieb: >> > Hi, >> > I'm new to OTRS, so please forgive me, I've been using RT for a couple >> > of years >> > but it lacks some features which I hope otrs can help with. >> > >> > I'm currently trying to see how to set up customers (they dont need web >> > access >> > to otrs) in otrs. I can export my tickets from RT and create a CSV >> > with names, email, address etc. Can I import this in to OTRS? Can I >> > then add custom fields, so that >> > when a new customer is added (usually over the phone) I can populate >> > these fields? >> > >> > Many thanks >> > >> > Mike >> > _______________________________________________ >> > OTRS mailing list: otrs - Webpage: http://otrs.org/ >> > Archive: http://lists.otrs.org/pipermail/otrs >> > To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs >> > Support or consulting for your OTRS system? >> > => http://www.otrs.com/ >> > >> >> Check this out, with a little technichal understanding you will get it. >> >> ---I did :=) >> >> http://doc.otrs.org/2.2/en/html/x1536.html#customer-backend-db >> >> -Shawn >> > > > Shawn thanks for your reply. > So am I right in thinking the only way to add custom fields for > customers is to change > the mysql database direct? And then edit one of the perl files? > > How do I import 200 customers lets say in to the otrs customer database? > > thanks again > > Mike > _______________________________________________ > OTRS mailing list: otrs - Webpage: http://otrs.org/ > Archive: http://lists.otrs.org/pipermail/otrs > To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs > Support or consulting for your OTRS system? > => http://www.otrs.com/ >

Mike,

You can try the script included. I did it manualy in PHPMyadmin. And then as I found that I also needed more fields, I upgraded the information later (as well manually).

The script can be found in $OTRSHOME/scripts

and is called

syncuser_csv2otrs.pl

There are instructions included in the file.

I hope this helped.

--Shawn

Thanks Shawn, I tried a simple one line CSV file to import like

smitha,alan@smith.com,Alan,Smith,s3cr3t,Mr

And ran this with the /syncuser_csv2otrs.pl script. What I get is the following:-

root@rt3:/opt/otrs/scripts# ./syncuser_csv2otrs.pl -s /tmp/otrs_test.csv

Add user 'smitha,alan@smith.com,Alan,Smith,s3cr3t,Mr' Use of uninitialized value in concatenation (.) or string at /opt/otrs/Kernel/System/Log.pm line 131, <IN> line 1. ERROR: OTRS-syncuser_csv2otrs.pl-30 Perl: 5.8.8 OS: linux Time: Wed May 9 14:50:17 2007

Message: Need Firstname!

Traceback (4948): Module: Kernel::System::User::UserAdd (v1.55.2.2) Line: 219 Module: ./syncuser_csv2otrs.pl.O (v) Line: 103

DBI::db=HASH(0x859f094)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at /opt/otrs/Kernel/System/DB.pm line 223.

Does that look incorrect?

Once I get this right I assume I can add extra columns to the SQL and update the config file for OTRS? However I seem to be struggling with the basic import of one record?

Thanks

Mike _______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support or consulting for your OTRS system? => http://www.otrs.com/

In the example within the file, semicolons are used as the separator.

I assume this is your problem.

--Shawn

Thanks Shawn, changing my seperator in my simple test file to semicolons has got rid of a lot of errors, I now see:-

root@rt3:/opt/otrs/scripts# ./syncuser_csv2otrs.pl.OO -s /tmp/customer_user

This line does not look right. Why does your script name end in .00 did you change the perl script.

...

Add user 'smitha' DBI::db=HASH(0x859e30c)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at /opt/otrs/Kernel/System/DB.pm line 223.

However no entry is added to the table 'customer_user' , just the one test entry I added via the otrs web interface:-

mysql> select * from customer_user;

+----+---------+---------------+-------------+---------------+------------+------------+-----------+-----------+----------+---------------------+-----------+---------------------+-----------+

...

| id | login | email | customer_id | pw | salutation | first_name | last_name | comments | valid_id | create_time | create_by | change_time | change_by |

+----+---------+---------------+-------------+---------------+------------+------------+-----------+-----------+----------+---------------------+-----------+---------------------+-----------+

...

| 1 | bloggsj | joe@blogs.com | 1234 | blhikXCcnN83. | Mr | Joe | Bloggs | test user | 1 | 2007-05-09 16:08:37 | 1 | 2007-05-09 16:08:37 | 1 |

+----+---------+---------------+-------------+---------------+------------+------------+-----------+-----------+----------+---------------------+-----------+---------------------+-----------+

...

1 row in set (0.00 sec)

mysql>

Any clues?

thanks Mike _______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support or consulting for your OTRS system? => http://www.otrs.com/

? hmmm this is good. I am not too sure yet. Just the comment about the script in your email (see above) Maybe you changed the pl script that came with otrs? Maybe you have an error in your new script. Try the basic user script again. And then, BACK TO THE DRAWING BOARD. You can send me a copy of your sncuser_csv2otrs.pl to compare with mine if you like.

--Shawn

Hi Shawn, I managed to get everything imported. I used mysql client and used the command:-

mysql> load data infile '/tmp/otrs_test.txt' into table customer_user fields terminated by '\t' lines terminated by '\r' (first_name, last_name, address, postcode, phone1, email, customer_id, valid_id, create_time, create_by, change_time, change_by, login); Query OK, 195 rows affected (0.03 sec) Records: 195 Deleted: 0 Skipped: 0 Warnings: 0

This was after I created some extra fields in the mysql table with:-

mysql> alter table customer_user add address varchar (250); Query OK, 1 row affected (0.46 sec) Records: 1 Duplicates: 0 Warnings: 0

mysql> alter table customer_user add postcode varchar (250); Query OK, 1 row affected (0.19 sec) Records: 1 Duplicates: 0 Warnings: 0

mysql> alter table customer_user add phone1 varchar (250); Query OK, 1 row affected (0.20 sec) Records: 1 Duplicates: 0 Warnings: 0 ave mysql> alter table customer_user add phone2 varchar (250); Query OK, 1 row affected (0.20 sec) Records: 1 Duplicates: 0 Warnings: 0

mysql> alter table customer_user add os varchar (250); Query OK, 1 row affected (0.20 sec) Records: 1 Duplicates: 0 Warnings: 0

mysql> alter table customer_user add broadband varchar (250); Query OK, 1 row affected (0.44 sec) Records: 1 Duplicates: 0 Warnings: 0

Another thing which got me was the end of line difference in unix and PC! when importing the data to the table above I originally was using '\n', eventually I figured I should be using '\r' ! Even though I was working on a Mac with Excel.

So finally all my records are imported and I'm slowly getting to grips with it! Thanks Mike _______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support or consulting for your OTRS system? => http://www.otrs.com/

"I was working on a Mac with Excel" That was the problem!!!! I am glad I could help you a little. Thanks for the response. It is not often that one receives feedback when assistance is given. I try to always express my appreciation here in this list, because there are some who are very active and helpful. Have fun with OTRS. --Shawn