CiviCRM Community Forums (archive)

I am trying to keep a civicrm database in sync with a legacy system until we can shut that off. I need to do an update import which works for most fields of the contacts, but causes duplicate notes to be added each time I do the import.

What I would like to do is isolate the notes import entirely so I can clean it up. What is the best way to import notes and correctly associate them with existing contacts?

I would
1) delete all existing notes
2) perform contact updates but ignore the notes field
3) do a separate export of contacts from the legacy system with notes, then do an update/(fill?) import with that file.

If it matters, the legacy system is ACT! v. 6.0

By the way...I have never seen a more difficult CAPTCHA! And you still require it even after registering?

But how do I import the notes? It seems there can be multiple notes per contact...does it just add more if you have multiple rows in the csv import file that match the same email address? Should I update or fill? Is there a way to import so the existing notes are deleted?

I have a logical plan, but I do not know precisely how to execute it in civicrm.

There is no separate note import. You'll need to combine the notes into one big note and import it. Alternatively you can write a custom script that does note import using the v3 api

if you do so, please share your code, so others could reuse and benefit

lobo

Hi,

Did you ever get this sorted?

Due to various problems, I had to do multiple uploads, thinking that all fields would be overwritten. However, I've just seen that a lot of my entries have the same notes several times. I'd like to clear all the notes out, and then re-upload.

Andy

Thanks for the reply Stoob, but I was actually trying to remove duplicate notes rather than upload multiple notes.

In the end I did some SQL queries which seemed to work fine. These commands will remove any duplicate notes for the same contact ID:

create table tempnotes (id int);
insert into tempnotes (id) select id from civicrm_note group by contact_id,note;
delete from civicrm_note where id not in (select id from tempnotes);
drop table tempnotes
I dare say there's a tidier way of doing that with one SQL statement and temporary tables, but that worked for me.

This was with version 4.1.2

Also, I don't see why a simple delete from civicrm_note wouldn't work to delete all comments, as the user above wanted to do.