Community
Showing results for 
Search instead for 
Do you mean 
Reply

Clean up database with Excel Macros without losing notes, activities, or history.

Loyal Listener
Posts: 6
Country: United States

Clean up database with Excel Macros without losing notes, activities, or history.

I have an ACT! 11.0 database with about 3,000 records.  I need to clean up certain fields in certain records.   I've been able to do this with a database with no notes, activities or history by doing the following:

 

export records to comma delimited (.csv) file

(delete those records from the database)

 

save the .csv as an .xls file

 

manipulate data in certain columns using Excel macros.  (This allows me to do things I could not do in ACT!)

 

save resultant .xls as a comma delimited (.csv) file

 

import into the database (or an empty copy of that database)

 

I've tested the above successfully with a newly created database.  It works fine.  Now I need to do the above with a a database that has notes, activities, and history, which I can't afford to lose.  How might I do this?

Copper Elite Contributor
Posts: 158
Country: Brazil

Re: Clean up database with Excel Macros without losing notes, activities, or history.

Hi,

If I were you I would use Exponenciel "Automatic Fields Calculation" (http://www.exponenciel.com/index.aspx) which costs about U$70,00.

Let tell you that I am an ACT (and Exponenciel) user !

Automatic Fields Calculation allows you to run calculation series and in your case it would just be a matter to run a calc to replace fields you assign on your lookup with "" (nothing).

It has an evaluating copy (complete).

I´ve being dealing with exports and imports with several formats. You will have to avoid some special characters to run smoothly. It works but will take you some time.

I gave up!

Some add-ons save me a lot of time.

Mac, Jorge A. Mac Genity
Using ACT! by Sage 2013 Pro Version 15.1.108.0, on Windows 7
Intel Core I7-2600 CPU @ 3,40 GHz
12,0 GB - 64 bits - NVidia GForce GTX 360 SE
Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Clean up database with Excel Macros without losing notes, activities, or history.

ACT! can't update fields for records already in the database via import except via the SDK.

 

There are quite a number of ways to do what you're trying. The best way will depend on what fields you're updating and the type of updates...

Loyal Listener
Posts: 6
Country: United States

Re: Clean up database with Excel Macros without losing notes, activities, or history.

[ Edited ]

Thank you for the responses.  I should have been a little clearer about what I meant by "clean up certain fields in certain records."  We have contact records only, no company or group records.  We'd like to be able to grind out mailing labels from ACT!, but the data came from another program and the company names in the contact records look something like this:

 

Smith Company - OD2186
Clambake Inc. - OD79
Jones Consulting
Campbell Group - OD5126
Trinkets Are Us - OD3954
 

 

They need to look like this: 

 

Smith Company
Clambake Inc.
Jones Consulting
Campbell Group
Trinkets Are Us
   

 

We've had other similar ACT! databases that we've cleaned up using export/Excel macros/import,  but by the time I got this database, there was already a body of notes, activities, and history. It appears it would be time-consuming to automate this task.  Since it's a one-shot, we can get someone to correct them manually.

Message Edited by message on 10-14-2009 11:22 AM