Exporting Your Act! Data to Excel is as Easy as 1,2,3

·

Karen

act perpetual licenseMany Act! users routinely head to the Export Wizard to send their information not realizing that there is a much easier method for exporting Act! data. 

You’ll find the Export Current List to Excel icon at the top of the Contact, Group, Company, Opportunity, History and Task List views. If the button is “greyed out” it means one of two things:

·         You are not a Manager or Administrator of the Act! database, or you are a Standard user but do not have the permission to export to Excel.

·         You don’t have Excel installed on your machine or your version of Excel is not compatible with your version of Act!.  For example, you may be using Act! v25 but your Office version is 2010 or 2013.

With Act!, exporting your basic Contact Data to Excel is as Easy as 1,2,3:

  1. Create a Lookup
  2. Customize the List View to show the fields you want to include.
  3. Click the Export Current List to Excel icon.

In a matter of moments Excel will spring to life with a new spreadsheet containing a list of all your contacts with their corresponding field information like their physical and email addresses, phone numbers and website. For some of you  this might be all you need. However, many Act users are trying to  export their entire Act database to another CRM product and need to extract their Act Notes, Histories and Opportunities. This gets a bit more complicated but can be accomplished with a bit of SQL knowledge.

In order to extract all of your Act data it is important to understand the structure of a relational CRM database like Act. Most CRM’s are comprised of many “tables;” for example, Act consists of separate tables for Contacts, Secondary Contacts Companies, Users, Groups, Notes, Activities, Histories and Opportunities. The various tables are tied together using a globally unique identifiers (GUIDs).  As an example a Note may include two GUIDs: one to tie the Note to the correct Contact Record and another to indicate the User who created the Note.

If you are in need of a complete export of all your Act data, please call the friendly Act! consultants at Tech Benders at 561-470-5450 or send us an e-mail.

Comments

16 responses to “Exporting Your Act! Data to Excel is as Easy as 1,2,3”

  1. Karen Avatar
    Karen

    Hi Janet,

    I wish all of my questions were this simple! You are simply hitting the wrong icon button. The magnifying glass provides a list of all the Contacts that have an activity in the Task List; it does not include their Tasks. You need to click the Export to Excel icon button.

  2. Janet A. Avatar
    Janet A.

    When I go to export the task list I have selected, it’s sending the entire list instead of just the dates I need. I have been going in and customizing the date – then clicking the magnifying glass then on the drop-down menu I would click export, and I would get the list for the appropriate date(s) set and the columns I chose to print. Now when I follow those steps, it will populate the ENTIRE contact list in excel instead of the (current) 43 I am trying to print.

  3. Karen Avatar
    Karen

    Hi Evan,

    I have special tools that allow me to bypass the SA password…

  4. Evan Avatar
    Evan

    How do you manage to connect via SQL studio? Does this not require you to know the sa password?

  5. Karen Avatar
    Karen

    Rob,

    I’m assuming you’ve already tried exporting to .csv format; that usually removes most formatting. In Excel I use “clean.” When all else fails I resort to SQL Studio.

  6. Karen Avatar
    Karen

    Hi Rob,

    I think you’ve asked and answered your own questions: “Whatever ACT has done to their data is very puzzling and makes the export to Excel nearly a pointless exercise.” If the “Clean” feature doesn’t work then you might try using SQL Studio. It’s what I use when exporting out Act data.

  7. Rob Nobel Avatar
    Rob Nobel

    Karen, I don’t know what happened to my reply to yours, so I’m replying again.

    I am not moving to another CRM. I just want to export the data to Excel, but the exported data cannot be cleaned. I have tried Clean, Trim, Find and Replace without success. In fact, removing that pesky ? in front of the text completely deletes the entire content of the cell as if the text is hard wired to the ?. I can only remove the ? in WordPad and then open in Excel via import, but even then a lot of work needs to happen.

  8. Rob Nobel Avatar
    Rob Nobel

    Thanks Karen, but all I need do is regularly export the Task list. I’m very experienced in Excel and have tried a bunch of things as well as apply both the clean and trim functions to the date data.

    However, the issue is that ACT puts a question mark (?) in front of ALL exported data (not just the date field) and CLEAN won’t remove them. When I save the exported data to CSV and open the CSV file in Excel, the ? can be seen in front of all the data cells.

    It seems the ? is somehow linked to the rest of the data in that cell and each ? can be deleted one at a time. Which is a laborious task. I could probably write some VBA code to perform this task, but surely not 🙁

    Trying to do this via Find & Replace does NOT work, because when trying to replace the ?, the complete data in that cell is deleted. If the ? is replaced by say, #, then the entire cell becomes a bunch of #####.

    Whatever ACT has done to their data is very puzzling and makes the export to Excel nearly a pointless exercise.

    I really need an answer to this problem please.

  9. Karen Avatar
    Karen

    Hi Rob,

    You have two options. If the export is to move to another CRM product it’s probably best to have me do a full export of your Act data. The data, including Notes, Histories & Tasks, will come over in a series of spreadsheets none of which will contain the “wonky” formatting that you are referring to. If you are staying with Act and just want to export your Tasks on a regular basis then apply Excel’s “clean” formula to your data.

  10. Rob Nobel Avatar
    Rob Nobel

    When I export the task list to Excel, there seem to be hidden characters which prevent me from editing/formatting the data. I am particularly wanting to format the date column to DDD DD/MMM/YYYY, but that seems impossible. I can see the hidden characters when I save the exported file to CSV, but dealing with those now unhidden characters (actually “?”) is problematic.
    When I try to clean or trim the date column in excel, it does not help. In fact when clicking a cell and clicking in the formula bar, the data appears to go missing. Really weird!!! I believe exporting via the wizard into a CSV may be a work around, but surely, there’s a better solution for this?

  11. Karen Avatar
    Karen

    Thanks for reaching out! I am not sure what you are envisioning by exporting your Act! “contacts and history at once.” The data in a database is too big to fit on a single spreadsheet if that’s what you’re thinking. Each Act! table (notes, histories, contacts) will be in a separate spreadsheet. If you have tons of notes you might end up with more that one spreadsheet for the notes. But you will have every last drop of your data.

    If you tell me what you’re planning on doing with your data I might be able to give you a better answer. If you’re moving to SalesForce or Zoho I export the data in a way to make it easy to import into those products.

  12. Rich Avatar
    Rich

    I have 150 – 200 Act databases that I need to export into CSV files. Do you have an on prem application that could handle this? Unfortunately, due to the nature of our business we are unable to share these databases, it must be completed 100% on premise.

    Thanks

  14. Karen Avatar
    Karen

    Yes, there are ways to export your notes from Act but you must have special tools to do so. You could do it through QL Server Management Studio (SSMS)but it would probably take you hours to figure out how to get your data out in the format that you need or want. We do a full extraction of all your data at a cost of $500.

  15. James McCarty Avatar
    James McCarty

    Is it possible to export the ACT! notes to SSRS?

  16. Mike Rye Avatar
    Mike Rye

    I am trying to export my ACT DB, and I am wondering about the notes field.
    Is there any way to export those? I have tried three different ways to export data, and it’s appearing that I am not
    getting all of my data.

Leave a Reply

Your email address will not be published. Required fields are marked *