Community
Showing results for 
Search instead for 
Do you mean 
Reply

Export all contacts and notes

New Member
Posts: 26
Country: USA

Export all contacts and notes

Good morning.  I need to export to a file (csv or such) all of our contacts and notes.  Is there a process to do this?  I cant seem to do an export that includes notes. 

Platinum Elite Contributor
Posts: 14,384
Country: Australia
New Member
Posts: 26
Country: USA

Re: Export all contacts and notes

[ Edited ]

I have a connection via OLE DB.  I can get hte contacts access and seperately i can get hte notes accessed... there is just not any order to the notes.... the notes seem random and do not have which contact they belong to.

 

ALOT Of the notes are just stuff like..

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Microsoft Sans Serif;}}

\viewkind4\uc1\pard\f0\fs16 not interested, too busy to trade\par

}

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Export all contacts and notes

You should have a UniqueID that shows where they're linked ... remember this will hook to another table as Notes can link to multiple contacts

The other stuff is the formatting of the text
New Member
Posts: 26
Country: USA

Re: Export all contacts and notes

The column headings which export are:

 

NOTE Created On
NOTE Edited On
NOTE Last Edited By
NOTE Note
NOTE Note Date
NOTE NOTETYPEID
NOTE Private Note
NOTE Record Creator
NOTE Record Manager
NOTE TYPES Active
NOTE TYPES Created On
NOTE TYPES Custom
NOTE TYPES Description
NOTE TYPES Edited On
NOTE TYPES Name
NOTETYPE NOTETYPEID

Is there a way to add an ID field to the lookup?

Employee
Posts: 1,163
Country: USA

Re: Export all contacts and notes

[ Edited ]

If you're using a version that has access to the 2.0 provider you should also have access to the CONTACT_NOTE table which links a contact to a note. I through together a simple query in visual studio that displays the contact name, the notes regarding text, note date, create date and edit date. 

 

I was able to plug it into the connection properties window in excel and get it to display the data

 

SELECT        DemoCopy.dbo.CONTACT.CONTACT, DemoCopy.dbo.NOTE.NOTE, DemoCopy.dbo.NOTE.NOTE_DATE, DemoCopy.dbo.NOTE.CREATE_DATE, 
                         DemoCopy.dbo.NOTE.EDIT_DATE
FROM            DemoCopy.dbo.CONTACT, DemoCopy.dbo.CONTACT_NOTE, DemoCopy.dbo.NOTE
WHERE        DemoCopy.dbo.CONTACT.CONTACTID = DemoCopy.dbo.CONTACT_NOTE.CONTACTID AND DemoCopy.dbo.CONTACT_NOTE.NOTEID = DemoCopy.dbo.NOTE.NOTEID

Obviously you'll need to change the name of the database your using, in this case DemoCopy is a copy of the demo database that I use for testing purposes. 

 

Hope this helps.

Matthew Wood
Act! SDK Support
Community Moderator
New Member
Posts: 26
Country: USA

Re: Export all contacts and notes

[ Edited ]

Youve been very helpful, just still a bit confusing since its more involved than i thought.

So where woudl that script go? i used jing to take screenshots of where im guessing it might be.

screenshot 1 (the properties window)

 

 

screenshot 2 (does command go wher ehte red arrow is pointing?)

 

 

Employee
Posts: 1,163
Country: USA

Re: Export all contacts and notes

[ Edited ]

Yes it does, you can simply paste it there, as I said however it likely doesn't include all of the data that you'd liike to see, however you can add any additional columns you'd like to include to the SELECT statement. 

 

I'm going to remove the images from your post however because in the connection string it displays your login.

 

Also I forgot to mention that you'll want to change the query type to SQL, otherwise it won't execute.

Matthew Wood
Act! SDK Support
Community Moderator
New Member
Posts: 26
Country: USA

Re: Export all contacts and notes

thank you very much.  plenty of information to start trying some things iwth.