Import Products

The Import Products feature allows you to import a list of products and associated product 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 products.

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

The key field to updating or creating new items, is the Product ID. This is required for all product imports and must match the Product ID in Acctivate for updating existing records. A few types of common imports and what fields are required for each are listed below. Click on the linked text for each bullet point to download an example spreadsheet:

• Create new products: Product ID, Description, Cost Method, Control Type, Item Type, Product Class, Product Type, and Unit. Other fields to consider are List Price, List Price Type, Tax Code, Vendor, Quantity on hand, Unit Cost, and Warehouse. If any of these data points are the same for all items in the sheet, you can utilize the default mapping option rather than putting the value in the file. For example, you are creating all Inventoried products using Average Cost and Standard Control Type. Those 3 fields could be set as a default.
• Product Initial Balance: Product ID, Qty on Hand, Unit Cost, and Warehouse. The linked example sheet also shows a column for Bin Location. Leave blank if it’s not required for your import. This import can only be used for products not listed on any transaction in Acctivate.
• List price updates: Product ID, List Price, Price Type, and Unit.
• Adding/updating vendor pricing: Product ID, Vendor Name, Vendor Price, Price Unit, and potentially Vendor Product ID. NOTE: Vendors must be created in QuickBooks and synchronized prior to the product import if vendor information is mapped.

Note: Products listed on any transactions (including open sales and purchase orders) will have the following values locked for editing: Item Type, Control Type, Cost Method, and Stocking Unit.

Create New or Update Existing Product Import Template

1. Select File > Import Data to open Select Import Type [1/5] window.
2. Click Products from the Inventory 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.

Import Products: 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 tab. When 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, check out the Advanced Import Options article.
   • When updating product supplier / vendor records, make sure to set the option to Update ID in existing vend prod record to Yes.
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.

Import Products: 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 Vendor Name to correctly match the name listed in QuickBooks. When ready, click Begin Import to re-validate the data.

• Update mapped fields: This would be when column(s) is mapped incorrectly in the previous screen. For example, you’ve got Cost Method mapped to Control Type. 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 Products: 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.

Import Products: 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 products were setup properly.