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

[Mike Dent]

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
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