[solved] Scheduled daily CSV import

  • Zammad version: 2.9
  • Source: package
  • Operating system: Ubuntu 18.04.1 LTS
  • Browser + version: Firefox 65.0.2

Hello zammad community,

we are very happy with this nice piece of software, it works like a charm.
Now we would like to import our customers from the ERP system via CSV files. The import through the Webui is fine but we are seeking for a method to do this import automatically every day at midnight. Any posibility to do this with a shell script? I´m not so familiar with database imports over command line and I hope this community can help. Thanks a lot in advance.

Greetings

bernhard

Hm, Zammad features a REST API, and in theory it should be able to do everything the user interface can do. After all, Zammad’s user interface is completely written in JS and communicates with the backend exactly via this REST API. Unfortunately, the /api/v1/users/import endpoint seems to be undocumented: User — Zammad System Documentation documentation

I’m not sure if this implies that its use is unsupported, or if someone just forget to document it. Until proven otherwise, I’ll just assume the latter :slight_smile:

If you want to see how it works, just open your browser dev tools, go the network tab and check “preserve log”, and then run an import. You should see two requests:

The first one is the test run:

POST /api/v1/users/import
Form data:
file: (binary)
try: true

The response contains the information that is displayed in the “confirm import” button, along with a list of all parsed CSV records.

The second one is the actual import:

POST /api/v1/users/import
Form data:
file: (binary)
try: false

Practically the same response as from the first request, just try is now of course returned as false.

With this information, it should be pretty easy to automate the import with curl. try=true should enable easy testing, and when your script works reliably, you can probably skip the test run and just use the import request directly.

2 Likes

Hello sir,

thank you for your detailed and helpful reply. I´m trying to import the CSV file with the following command:

curl -X POST "https://some.site.de/api/v1/users/import?try=true" --user "username:password" -H "Content-Type: text/csv" -F file=@/home/user/test.csv

Authentication seems fine, no complaints but I get this error message:

{"error":"No source data submitted!"}

The CSV file is filled with test data and the first line of the CSV declares the objects to fill like
id,name,email etc.

What I´m doing wrong? Thanks for your help.

The problem is probably -H "Content-Type: text/csv". When I use the UI in my browser, it sends the following Content-Type:

Content-Type: multipart/form-data; boundary=----WebKitFormBoundaryIQeEX3YlGTcRRGms

You don’t want to specify this manually though, because curl needs to know the boundary. Just don’t set a Content-Type; this works for me:

curl -H "Authorization: Token token=mytoken" -X POST -F file=@user-example.csv 'https://zammad.example.com/api/v1/users/import?try=true'
1 Like

Great job! That did it for me. Now there is only one problem left.
The import responses:

{"stats":{"created":1,"updated":0},"records":[.........]"errors":[],"try":true,"result":"success"}

but I´cant find the imported user in the Zammad WebUI. Any further steps to do?

Ensure that your elasticsearch is up and running and the user are indexed. You then should find them.
You can check that as well by running the following in a rails console ( zammad run rails c):

User.find_by(email: '<EMAIL>')

If you can’t find the user there, check your logfile, something went wrong then.

Yes, set try=false :wink:

With try=true, you’re just running a simulation to check your import data. Only with try=false you’ll actually import the data.

Args, sorry, what a fail, I mixxed this up completely ;).
Now it´s working. Thanks for your help and patience!

2 Likes

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