On 5/10/07, Shawn Beasley <[EMAIL PROTECTED]> wrote:
Mike Dent schrieb:
> On 5/9/07, Shawn Beasley <[EMAIL PROTECTED]> wrote:
>> Mike Dent schrieb:
>> > On 5/8/07, Shawn Beasley <[EMAIL PROTECTED]> wrote:
>> >> Mike Dent schrieb:
>> >> > On 5/8/07, Shawn Beasley <[EMAIL PROTECTED]> 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,[EMAIL PROTECTED],Alan,Smith,s3cr3t,Mr
>> >
>> > And ran this with the /syncuser_csv2otrs.pl script. What I get is the
>> > following:-
>> >
>> > [EMAIL PROTECTED]:/opt/otrs/scripts# ./syncuser_csv2otrs.pl -s
>> /tmp/otrs_test.csv
>> >
>> > Add user 'smitha,[EMAIL PROTECTED],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:-
>
> [EMAIL PROTECTED]:/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 | [EMAIL PROTECTED] | 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
_______________________________________________
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/
