[SOLVED] Problem with importing CSV file

Infos:

Important:
If you are a Zammad Support or hosted customer and experience a technical issue, please refer to: support@zammad.com using your zammad-hostname / or company contract.

  • Used Zammad version: 2.9.x
  • Used Zammad installation source: package
  • Operating system: Linux Debian 9
  • Browser + version: Google Chrome 62.0.3202.89

Expected behavior:

  • Succesful import of users from CSV into Users of Zammad.

Actual behavior:

  • Not importing at all.
    Errors:

Result: failed

  • Line 4: unknown record with id ‘5’ for User.

Why is that? When I change id of ‘5’ to ‘4’ it suggests UPDATING user with id 4. But I want the import operation to create missing users.

What am I doing wrong?

Steps to reproduce the behavior:

  • Add users like this:

id,login,firstname,lastname,email,web,phone,fax,mobile,department,street,zip,city,country,address,vip,verified,active,note,last_login,out_of_office,out_of_office_start_at,out_of_office_end_at,roles,organization
3,login@mail.com,Bartek,Anonymous,login@mail.com,"","","","","","","","","","",false,false,true,"",2019-03-06T11:24:40Z,false,Admin,
4,login2@mail.com,Adam,Anonymous,login2@mail.com,"","","","","","","","","","",false,false,true,"",2019-03-06T13:14:40Z,false,Client,
2,login3@mail.com,Anna,Anonymous,login3@mail.com,"","","","","","","","","","",false,false,true,"",2019-03-06T09:28:40Z,false,Client,

And then try to import (from CSV or pasting content):

id,login,firstname,lastname,email,web,phone,fax,mobile,department,street,zip,city,country,address,vip,verified,active,note,last_login,out_of_office,out_of_office_start_at,out_of_office_end_at,roles,organization
5,login4@mail.com,John,Anonymous,login4@mail.com,"","","","","","","","","","",false,false,true,"",2019-03-06T09:28:40Z,false,Client,

Could you run the following commands within a Zammad console? ( zammad run rails c)

User.find(4)
User.find(5)

If they are already set and don’t have the email from above, this will get troublesome.
Also please check the production.log from Zammad, as it might hold the reason.

Another hint:
ids 1 and 2 are reseved (1 for system and 2 for Mrs Braun).
id 3 is the admin user you added during running the wizard.

Also please note that mail addresses have to be unique. This is the reason why you can please around with the ID as much as you like, if Zammad already knows the mail address, it does not care about it.

My guess is that the IDs you’re trying to use are already in use.

irb(main):010:0* User.find(4)
=> #<User id: 4, organization_id: nil, login: “adres@email.pl”, firstname: “Imie”, lastname: “Nazwiśko”, email: “adres@email.pl”, image: nil, image_source: nil, web: “kl00000”, password: “$argon2i$v=19$m=65536,t=2,p=1$0jCW9/wvVsWN1S/XuHZo…”, phone: “TelefonS”, fax: “”, mobile: “TelefonK”, department: “”, street: “”, zip: “”, city: “”, country: “”, address: “Adres”, vip: false, verified: false, active: true, note: “”, last_login: nil, source: nil, login_failed: 0, out_of_office: false, out_of_office_start_at: nil, out_of_office_end_at: nil, out_of_office_replacement_id: nil, preferences: {“locale”=>“pl”}, updated_by_id: 3, created_by_id: 3, created_at: “2019-03-06 12:37:25”, updated_at: “2019-03-06 13:57:47”>
irb(main):011:0> User.find(5)
ActiveRecord::RecordNotFound: Couldn’t find User with ‘id’=5
from (irb):11
irb(main):012:0>

And snippet of production.log when trying to import:

I, [2019-03-08T11:59:58.182082 #411-47237601291740] INFO – : Started POST “/api/v1/users/import” for X.X.X.X at 2019-03-08 11:59:58 +0100
I, [2019-03-08T11:59:58.188895 #411-47237601291740] INFO – : Processing by UsersController#import_start as JSON
I, [2019-03-08T11:59:58.189000 #411-47237601291740] INFO – : Parameters: {“data”=>“id,login,firstname,lastname,email,web,phone,fax,mobile,department,street,zip,city,country,address,vip,verified,active,note,last_login,out_of_office,out_of_office_start_at,out_of_office_end_at,roles,organization\r\n5,login4@mail.com,John,Anonymous,login4@mail.com,”","","","","","","","","","",false,false,true,"",2019-03-06T09:28:40Z,false,Client,", “try”=>“true”}
I, [2019-03-08T11:59:58.216277 #411-47237601291740] INFO – : Completed 200 OK in 27ms (Views: 0.3ms | ActiveRecord: 4.2ms)
I, [2019-03-08T12:00:00.714218 #417-47025074085320] INFO – : execute Channel.fetch (try_count 0)…

Okay, please leave out the ID (just start with ,) and remove the last , on each line.
Our CSV-Sample should give you a hint how the data format should look like. Also please note that Excel loves to destroy that format (just in case you’re using that).

The following example works immediatly:

id,login,firstname,lastname,email,web,phone,fax,mobile,department,street,zip,city,country,address,vip,verified,active,note,last_login,out_of_office,out_of_office_start_at,out_of_office_end_at,vcard,roles
,test1,Test,"User 1",testuser1@domain.tld,"","","","","","","","","","Superduper Inc.",false,false,true,,,false,,,,Admin
,test2,Test,"User 2",testuser2@domain.tld,"","","","","","","","","","",false,false,true,"",,false,,,,Customer

One side note, especially when using Role assignment: Ensure the role exists, otherwise Zammad might handle the data unexpected.

I will definitely try that, that’s for sure.

The only mishap with this solution for me is that I will loose id numbers, which corresponds to my CRM.
I’ll guess I need to map them as ‘note’ field in database and use differently.

If you need something like a customer ID, you should use a custom field that you can map to your CRM.
You can never ensure that the ids will stay “in sync”, as I could simply write a mail as non customer to your Zammad and would cause your IDs to be out of sync.

That’s true.

I got question - if I import 100 clients, and for example I will have in database client with corresponding e-mail address: test@domain.com and he writes an e-mail to Zammad from his account - I assume it will join the ticket with that client, right?

Yes, that’s correct. If Zammad knows the mail address, it will assign the customer account with the used mail address.

1 Like

Thank you for your support. Issue solved.

1 Like

This topic was automatically closed 120 days after the last reply. New replies are no longer allowed.