Adding or Modifying Customers via Data Import

The Import Customers feature allows you to import a list of customers and associated customer information easily and quickly into Acctivate from an Excel, CSV, or tab-delimited text file without having to type everything into the system manually. This import can be used to either create new or update existing customers.

Before importing, you must first have a file to import. It is up to you on how to get this information: a spreadsheet sent from an outside CRM utility, a flash drive containing a file from a lead generator with all (or most) of the relevant information you need to setup new customers, or an export from the Acctivate database to update existing customers. Check out this page for a full list of available fields, as well as this example spreadsheet. The order of the column headers does not affect the import utility.

The key field to updating or creating new customers, is the Customer ID (or Customer Name). This is required for all customer imports and must match the Customer ID in Acctivate for updating existing records. Here are a few types of common imports and what fields are recommended:

• Create new Customers: Customer name, billing address as separate columns (address 1, city, etc.), phone number, email, branch, and salesperson ID.
• Update existing Customers: Customer name and any field needing updates as separate column(s).
• Create Jobs: The parent customer record must already be in Acctivate before creating and assigning jobs. Once the parent exists, you can use the import to assign/create job records by having a column with the following format for customer ID/name – Parent Customer:Job Name. Acctivate will create the new job based on the value after the colon.
• Ship To Locations: Customer name repeated down the column for each new ship to location ID. Ship to location ID is unique per customer. Both fields are required for additional ship to locations. Make sure to also have columns for ship to address 1, address 2, city, state, zip, and tax category.
• Customer Contacts: Customer name repeated down the column for each new customer contact. We recommend mapping contact ID, contact name, email, phone, and title.

Create New or Update Existing Customer Import Template

1. Select File > Import Data to open Select Import Type [1/5] window.
2. Click Customer from the Customer folder in the #1. Select Document Type section.
3. To create a new template, click Create Mapping. When modifying an existing template to use a different spreadsheet or tab, select the proper template in the #2. Select Mapping section > Edit Mapping.

Mapping Editor [2/5]

1. Choose the Excel (xls, xlsx) spreadsheet, tab-delimited text, or CSV file you wish to import in the Filename box within the Source Spreadsheet section.
   • After selecting the file, the information will be displayed in the Preview window on the right-hand side of the screen. Each column will be labeled A, B, C, and so on, depending on how many columns there are in your file.
2. Select proper Worksheet if multiple tabs exist.
3. Toggle the First row of spreadsheet contains field names checkbox depending on the values of the first row on the worksheet. If the first row contains the titles of each column, check the box to view the titles as they are in the file. The titles themselves do not affect the import process.
4. For new templates, assign an ID, Name, and Description in the Summary tab to identify what you’re importing. Use relevant, unique information for easy recognition in case you wish to go back and reopen the saved mapping at another time. The Name and Description will appear on the prior window for selection.
5. To map the spreadsheet columns to fields in Acctivate, start by selecting the Columns tab. The Preview section of the mapping editor shows you which column has been mapped to each field in Acctivate. The top row shows your spreadsheet headers, and the second row shows the field it is mapped to. We recommend naming the columns as something relatable to the field you wish to import into, but it is not required.
6. Click on the specific Acctivate field you wish to map to make that row active and choose the column from your spreadsheet in the Mapped field section.

7. Repeat this process until you have mapped all fields in your spreadsheet you wish to import.
8. If needed, update the Options Most customers leave as is, but they can be used to tell Acctivate how to handle duplicates or missing information. For more information on these options, go here.
9. Once mapping is complete and options set, Save Mapping.
10. Click Begin Import to start the validation process, after reviewing the information mapped in the Preview window.

Data Editor [3/5]

The third window, called the Data Editor will open showing data validation results per row. Depending on what you want to view in the Data to Import data grid, check or uncheck the checkbox near the top that reads Show Only Invalid Rows.

Near the bottom of the window in the Statistics section, the number of passed and failed rows will be shown, as well as a total row count. Please review this information before moving forward. If rows failed (unchecked), an explanation of the error will be displayed in the Errors section, as you select each line. Make sure to save any updates to the mapping.

There are a few options for resolving errors:

• Edit the data: This will not update your spreadsheet, but it will allow you to modify the data to get the row to pass. This can be a selection from a drop down based on configured lists, typing the correct value in the field, or using a lookup to select the proper value.
• Convert the values in the mapping: Click the Back button > select Columns tab > find and select the Acctivate field needing a conversion > Show Conversions. This gives you the ability to “convert” values in the spreadsheet from incorrect to correct data. Based on the error shown above, we can setup a conversion for the Salesperson ID to correctly match the name from QuickBooks. When ready, click Begin Import to re-validate the data.

• Update mapped fields: This would be when a column is mapped incorrectly in the previous screen. For example, you’ve got Sales Rep mapped to Branch. Click the Back button and update the mapping to use the proper columns. When ready, click Begin Import to re-validate the data.
• Complete the import: Continue with the import and use the Exception spreadsheet to resolve errors and re-import failed rows. This is detailed in the Importing [5/5] window section.
• Cancel import to update sheet: Close the Import Data utility, update your spreadsheet, and start the import over.

When ready, click the Next button.

Import Confirmation [4/5]

The fourth window, Import Confirmation will open. Before importing the data, review the statistics given in this window. If you approve the provided statistics, click the Finish button. To correct failed rows, click Back and run through steps listed above.

You will be prompted to backup your database before importing. Database backup is strongly recommended. To do so, click the Start Backup button. Follow through with the prompts until backup is complete.

Importing [5/5]

The Importing window will open. This window is where the actual data is being imported into Acctivate. A progress bar will show progress as the data is imported into Acctivate. Importing may take a considerable amount of time depending on the size of the file being imported and the speed of your system. Never end task on an import. There could be a large list of rows causing it to seem locked up or frozen or an exception spreadsheet being created for any errors. If no errors, a pop-up window will appear when the Import process is complete. Click OK on this pop-up window.

If there were errors, you will be prompted to create an exceptions spreadsheet. This spreadsheet can be used to cleanup any bad data and re-imported without having to manipulate the original spreadsheet. Each row in the exception spreadsheet will list the specific error that caused it to fail. Click Yes and select a location to save it.

Statistics (as described in the previous steps), as well as, the number and types of records created and updated in Acctivate will be given in the window. If a spreadsheet was created with the failed rows, the Directions Section will appear in this window. It tells the user where the file was saved so it can be opened in another program (i.e. Excel) to make your changes and import the modified spreadsheet. Click the Open button and the spreadsheet will open with all the failed rows, which will include the row number as it relates to your original spreadsheet and exceptions (a brief explanation of why each row failed the import).

Close the import utility and spot check a few items to make sure the customers were setup/updated properly.