[otrs] Re: Getting customer data in to OTRS, fields for address, OS,
postcode etc?
Shawn Beasley
shawn.beasley at dlh.de
Fri May 11 08:25:29 GMT 2007
Mike Dent schrieb:
> 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
> 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
More information about the otrs
mailing list