Regular import to Addressbook from CSV file?

[Mgr_Peter_Tuharsky_M]

Hallo, guys.
I’d like to ask You whether somebody has experience with regular Addressbook imports, preferably automatised. We have a CSV file that is regularly generated by script. We want to import it, also regularly, to the EGw Addressbook.
The problem is, we cannot manage to make it work well.

Most of the contacts in the CSV do not change. However, we want those that are changed (eq. phone number) to be updated in Addressbook. Moreover, if those newly missing in CSV would get automatically removed from AB, would be nice. However, none of that works for us. So far the best we achieved, that it added contacts that were missing before, but those already existing don’t change at all.

Other approach would be, delete whole addressbook and import it again. Not nice:
-it bogs DAV synced clients (all contacts disappear and are synced again as new)
-it never worked well when import is done as EGw-ed cron job.

We attempted to use the import conditions (one of the last steps in import definition), but also failed miserably wondering, how the whole thing works :-)

Is there any documentation about the topic, please? Or a good advice?

[StefanU]

Hi Peter.

Most of the contacts in the CSV do not change. However, we want those that are changed (eq. phone number) to be updated in Addressbook.

That shouldn’t be a problem.

Moreover, if those newly missing in CSV would get automatically removed from AB, would be nice.

It’s more like a problem.
There is no function that checks each address book entry to see if there is an entry with the same name in the import file and then deletes the affected record.
Since data changes, this would only be possible with certainty if you could uniquely identify the records. For example, by means of a data record ID.

You could probably program such functions.

Also, you must be careful with the deleted records, but also in general, if you have used the address data (address book etc.).

-it never worked well when import is done as EGw-ed cron job.

Hmmm. Works for me.

Is there any documentation about the topic, please?

No. There are far too many variations to describe it reasonably.

Or a good advice?

Yes. Let our support help you in this case.

Regards
Stefan

[bbegw]

Hi Peter,

any import needs a unique identifier which is used in the condition for update or insert of the data from the csv file. If the direction goes from some external system into EGroupware, you should check, if that system generates an unique id. Add a customfield for that “external_id”. For the first import you may need to use an other criteria to get the external_id into the existing contacts. Or you once delete all contacts in that adressbook import adressbook and restart importing with the “correct” definition and condition.
The condition should be like that:
If “external_id” exist “True --> update (stop)”, “False --> insert (stop”.

For deleted entrys, you can either:
a) use the option "Delete Addressbook entries Not in the CSV list"
b) or use a second import which contains the “external_id” of the entrys which should be deleted with an other definition: If “external_id” exist “True --> delete (stop)”, “False --> none (stop”.
Case “a” requires that you only import into one selected addressbook and nobody else add anything in that addressbook. Case “b” also works, if you have other contacts in that addressbook.

If you like me to check an example csv and the definition you use, please use our supportcenter to share the data. Hope it helps to understand how the import works in general, and that from my experience the most mistakes are done by NOT using unique identifier. E.g. condition on emailadress may not be unique!

Regards
Birgit

[Mgr_Peter_Tuharsky_M]

Thank You Brigit, I will take Your advice and try to implement it.

[Mgr_Peter_Tuharsky_M]

For the record (and information for other users), after some experimenting, Contact ID seems to work this way:

The contact_id is auto-generated by EGroupware and it is a number. Each new contacts gets new ID in incremental order.
When exporting a contact to CSV, the ID is by default stored in the first column named Contact ID.
When exporting to VCF, the ID constitutes the UUID part of standard UID string. It looks like this:
UID:addressbook-contact_id-long_hash
e.q. addressbook-48638-da41872037544631a169db89a02b831b

When importing contacts, the ID (named e.q. external_ID) can be used to identify the contact that EGw already has in database, thus allowing to apply changes to contact instead of creating a new one.
One can map the external_ID as the Contact ID upon import, however the import IGNORES any ID that does not exist in database yet. So, it is not possible to externally create a new ID and force the EGW to make it its own legitimate contact_ID. Only the ID that already exists in EGw is taken into account.

In order to allow some unique identification of imported contacts, one may choose some other unique attribute of the contact. For example, if exporting from AD, the userPrincipalName attribute. We have chosen to map the attribute to the Label field, that is not visible anywhere, yet can be used to define filter in the way Brigit proposed: If “Label” exist “True --> update (stop)”, “False --> insert (stop”.

[RalfBecker]

When importing contacts, the ID (named e.q. external_ID) can be used to identify the contact that EGw already has in database, thus allowing to apply changes to contact instead of creating a new one.

One can map the external_ID as the Contact ID upon import, however the import IGNORES any ID that does not exist in database yet. So, it is not possible to externally create a new ID and force the EGW to make it its own legitimate contact_ID. Only the ID that already exists in EGw is taken into account.

@bbegw That makes sense to change, as it is already handled in EGroupware when the contact is eg. created by a vCard import, CardDAV or ActiveSync. Maybe reject is if it is to short …

Ralf

[Mgr_Peter_Tuharsky_M]

Hi, Ralf

Thank You for Your input. Upon import definition, there is also a notification that ID cannot be changed by import. I was assuming that this is related to the mentioned behaviour, and that it is by-design that EGw dosen’t want to accept external ID to become its internal contact_ID.
Our ID was 6-digit, seemingly enough. Upon import, the contact has been given the next increment of the last internal contact_ID, instead of the external_ID.

[bbegw]

Hi Peter,
my idea was a bit different i was suggesting to always import the id into “external_id” as you may have an contact_id which is manually inserted and you probably do not want to match the imported “contact_id” with that manually created different contact, which has by chance the same “contact_id”.

So i was sugggesting having something like that in the csv:
external_id (always coming from the other system), name, organsiation, phone

the import maps “external_id” to the customfield “external_id” and the check for update or add is always on that id… So it does not matter, if the contact_id already exists or not.

For sure, this is different if you are tlaking about accounts stored in LDAP or AD, which are kind of similar used as adressbook-contacts, but for a pure adressbook-import from a different source, i would not use the contact_id.

Ralf thought that we “offer” to import “contact_uid”, but thats not in the definition as a allowed field. This would allow to be inserted with what ever you have. The contact_id is defined in the database as “autoincrement” and therefor we are not allowing to set it by import.

Birgit