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

Mike Dent mcdent at gmail.com
Thu May 10 15:38:01 GMT 2007


On 5/10/07, Shawn Beasley <shawn.beasley at dlh.de> wrote:
> Mike Dent schrieb:
> > On 5/9/07, Shawn Beasley <shawn.beasley at dlh.de> wrote:
> >> Mike Dent schrieb:
> >> > On 5/8/07, Shawn Beasley <shawn.beasley at dlh.de> wrote:
> >> >> Mike Dent schrieb:
> >> >> > On 5/8/07, Shawn Beasley <shawn.beasley at dlh.de> 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 at smith.com,Alan,Smith,s3cr3t,Mr
> >> >
> >> > And ran this with the /syncuser_csv2otrs.pl script. What I get is the
> >> > following:-
> >> >
> >> > root at rt3:/opt/otrs/scripts# ./syncuser_csv2otrs.pl -s
> >> /tmp/otrs_test.csv
> >> >
> >> > Add user 'smitha,alan at 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 at 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 at 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

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


More information about the otrs mailing list