I converted our ACT database into Sugar using “Exporter for ACT”. This posting covers the basic steps I took to convert all of our data. To do this, you will need to be familiar with relational databases and have a copy of MS Access and Excel. It took about two business days to complete this conversion (hopefully it won’t take you so long).
Summary:
Sugar’s import tools are not sufficient for a comprehensive import from ACT. They do not allow you to assign ownership or link notes and activities to created contacts. We used Exporter for ACT to get the data into a readable table format, manipulated the data to look like Sugar’s tables, and then imported the data directly into Sugar’s MySQL database. Since ACT does not have the concept of Accounts or Leads, the export revolves around the Contact. We create accounts for the contact, and then attach notes, history, and activity records to the contact. In the process, we link the newly created accounts to the records, and assign ownership to the current record managers. As a result, the transition from ACT to Sugar is almost seamless for users. When your sales team logs in, they will immediately see their contacts, accounts, tasks, notes, etc ... and will spend less time getting frustrated with the new system.
STEP ONE: Find a nice database front end and data importer for MySQL
I downloaded DBManager from
http://www.dbtools.com.br. This is a simple MS Access type front end for MySQL. It allows you to edit the Sugar data and easily import data from MS Access. I am sure there are dozens of tools like this, but I’ll use this one for my examples.
STEP TWO: Get the data out of ACT
I downloaded (and paid $250) for a tool called “Exporter for ACT” (
http://www.jltechnical.com/Exporter.htm). For me, it was well worth the money. This tool converts your ACT database into an Access database. If you want to try it out first, you can download a trial version that exports up to 90 records. It spits the ACT database into the following tables:
CDB_CONTACTS (Contacts)
Used to create the contacts and accounts tables
NDB_CONTACTS (All contact notes)
Imported as notes into Sugar
HDB_CONTACTS (All contact history from ACT)
Imported as notes into Sugar
ADB_CONTACTS (All active activities, including calls, meetings, and tasks)
Used to create the calls, meetings, and tasks tables
The field that ties all of these records together is “X_CDBJOIN”, which is a primary key for the CDB_CONTACTS table.
STEP THREE (optional): Create users in Sugar
In order to keep records associated with the owner from ACT, I created all of the users in Sugar first. Then I exported the user’s table (using Sugar’s “export” button) into a CSV file. This gives you the “id” field from Sugar’s users table to use during the conversion process.
STEP FOUR: Create Contact and Accounts tables
Here is where I moved from Access to Excel, so that I could use some of Excel’s functions. I created an excel spreadsheet with a different worksheet for each of the tables that I planned to import into Sugar. In the first worksheet, I put the users table that I exported from Sugar. In the second worksheet, I imported the data from CDB_CONTACTS and labeled it “contacts”. The third worksheet, I labeled “accounts” and also put the data from CDB_CONTACTS.
Since ACT does not have the concept of Accounts, you have to create the Accounts table by eliminating rows with duplicate company names. I sorted the accounts worksheet by “Company” and then identified duplicates. (I used the formula (=IF(A3=A2,”Duplicate”,””)) to quickly identify the duplicates). I then deleted all of the rows with duplicate company names.
Next, I arranged the columns on the Accounts tab into the order of the columns in the Accounts table from Sugar (You can see this using DBManager). Change the column names to match the columns in Sugar. For the assigned_user_id column, I sorted the worksheet by “Record Manager” and then copied the id field from the users worksheet for each ‘Record Manager’ – this maintains account ownership when you import the data. For the accounts table, you have to create the primary key for the “id” field. I started using a combination of date, time, and numbers to ensure that they were unique (ie 200502070934000001) I then used the “fill handle”, or little black square in the bottom right corner of the cell, to copy and increment the key for each row.
Once I arranged the columns in the Accounts worksheet to match the Accounts table in Sugar, I moved to the Contacts worksheet and arranged the columns to match the Contacts table in Sugar. Use the “X_CDBJOIN” field for the “id” column. For fields that didn’t exactly map, I concatenated them together into one string using the CONCATENATE function and placed them in the ‘description’ column (For example ‘Last results: No Interest, Last Reach: 06/19/2003’). You cannot import formulas, so I copied and ‘pasted as values’ any columns that were calculated.
To link the Accounts and Contacts, I used the VLOOKUP function (see Excel help for details [i.e. =VLOOKUP(b2,Accounts!C:D,2,FALSE]). This allowed me to find the newly created account_id for each contact record. Once you have found the account_id for each contact, remember to copy and ‘paste as values’ the vlookup column.
Finally, I copied the Contacts worksheet into a new worksheet called accounts_contacts. The accounts_contacts table in Sugar links contacts to accounts by the primary keys. Create a new unique key for “id” and then delete all of the other rows except “contact_id” and “account_id”.
Now you have three worksheets (accounts, contacts, and accounts_contacts) that are ready to be imported into Sugar using DBManager. I found it easier to import them into Sugar from Access, so I created a blank Access database and imported the worksheets as tables. Then, using DBManager, I selected Tools > Data Management > Wizards Manager > MSDAO Import Data. You select your Access database, select the tables to import (uncheck the “create tables option”), ensure that the target table name in Sugar is correct, select your Sugar database, and then click OK.
You can now login to Sugar and see all of the contacts and accounts linked together.
STEP FIVE: Import ACT notes
I ran into speed bumps exporting NDB_CONTACTS into Excel. For some reason, the ‘Regarding’ field (which is the actual note) was cut off at 255 characters. If there is a better way to do this step, I hope someone else will post the solution. I ended up exporting the NDB_CONTACTS to Excel for manipulation, and then pasting some of the fields back into Access.
You can use X_NDBJOIN as the primary key (id) for notes. I exported the NDB_CONTACTS from Access to Excel. In Excel, I was able update the modified_user_id field and use VLOOKUP on X_CDBJOIN to populate the ‘parent_id’ field with the account_id from the accounts_contacts worksheet. Set ‘parent_type’ to “Accounts”. I used X_NOTE254 for the ‘name’ column.
Once all of the data was populated, I sorted the Access table and the Excel file by the primary key (to ensure that the rows were lined up) and then pasted the updated columns into the Access table. You can paste into Access by selecting a field and then using Edit > Paste (just as if you were in Excel). FYI, Access will only let you paste into about 3000 rows at a time.
Now, rename the columns in the Access database to match the columns in the notes table from Sugar. Finally, import the notes using DBManager’s MSDAO Import Wizard.
STEP SIX: Import History
In ACT, once a to-do, meeting, or call is completed, it goes into a history table, along with important field changes. Since Sugar does not store history in this manner, I chose to import the history into the notes table. Our sales team is very happy with that solution, because that is how they appear in ACT.
This table has the same problem as the notes table. When you export it to Excel, the ‘Regarding’ field cuts off at 255 characters; therefore, use the same approach to import them as the notes table (moving data to Excel for manipulation and then pasting the manipulated data back into Access). I concatenated the X_TYPE, Record Manager, and X_NOTE254 columns into one column for the ‘name’ column in Sugar. You can ignore the ‘Type’ column because the ‘X_TYPE’ column shows the string equivalent.
For details about parent_id and importing to Sugar, see step five.
STEP SEVEN: Import current activities
The tasks table in sugar is almost exactly like notes, but the meetings and calls allow multiple users and multiple contacts.
First, use X_ADBJOIN as the primary key (id). Go ahead while they are still one table and export the data to Excel to perform the VLOOKUP for account_id & assign assigned_user_id. Now, copy the account_id and assigned_user_id back into the ADB_CONTACTS table in Access.
Next, copy the table six times to create tables called: meetings, calls, tasks, calls_users, calls_contacts, meetings_users, and meetings_contacts. Use the X_TYPE field to delete the rows that do not belong in each table (i.e. Only leave X_TYPE “Meetings” in the meetings_users, meetings_contacts, and meetings tables).
Now arrange the columns so that they match the columns in each corresponding Sugar table. As always, the ‘Regarding’ field from ACT becomes the ‘description’ field in Sugar. For calls and meetings, the ‘duration’ field in ADB_CONTACTS is stored in minutes. Convert this into hours and minutes for the Sugar Database’s ‘duration_hours’ and ‘duration_minutes’ fields. In Sugar, the duration minutes only holds ‘0’ for :00, ‘1’ for :15, ‘2’ for :30 and ‘3’ for :45.
Once you have all of the tables ready for import, use DBManager’s MSDAO Import Wizard to pull them into Sugar.
YOU ARE FINISHED!
Good luck and feel free to post any questions.
HELP - Act import (field mapping)
Re: HELP - Act import (field mapping)
Linear Mode
