Data Imports

Acctivate includes an import utility which can be used to create new data records or update existing data records. The import process is made up of three primary steps:

Mapping the data.
Validating the data.
Importing the data.

Mapping the data

The first step is to select the type of data to be imported. To select the type of data to import, open the import utility by going to File> Import Data. The Import Data - Select Import Type [1/5] window will appear. The following types of imports are available:

Document Type	Sub Document	Description
Customer	Customer	Used to create or update customer information.
Customer	Customer Product IDs	Used to associate a customer's Product ID with your Acctivate Product ID.
Inventory	Products	Create or update product information.
Inventory	Product Alt IDs	Add additional Alternate Product ID's and UPCs.
Inventory	Product Classes	Add or update Product Class information in the Configuration Manager.
Inventory	Product Components	Add or update (replace) a product's components. A product must be an assembly, kit or assortment.
Inventory	Product Prices	Used to add price codes and price rules.
Inventory	Product Types	Add or update Product Type information in the Configuration Manager.
Inventory	Receipts	Used to import in an Inventory Receipt transaction.
Inventory	Balance Adjustments	Used to import in an Inventory Balance Adjustment transaction.
Inventory	Lot/Serial Numbers	Used to import in Lot or Serial numbers. Creates and posts an Inventory Receipt transaction with the current date. This import will also create warehouses if the warehouse in the source file does not already exist. Typically used by the Acctivate Onboarding team; it's recommended that the Inventory Receipts import is used to create lot/serial numbers.
Inventory	Transfer	Used to import in an Inventory Transfer transaction.
Inventory	Warehouse Locations	Used to import in the list of available inventory locations for use on transactions. Only available if multiple locations are enabled.
Shipping	Shipment	Allows importing of a Package's tracking number and shipment date. The shipment and packages must exist in the Packaging Manager.

Select the Document Type

To select the type of data to import, click on the Document Type in the #1. Select Document Type section.

Next, you can select a saved mapping template in the #2. Select Mapping section, if one exists. If one exists, click Edit Mapping to edit the existing mapping; if one does not exist, click Create Mappings to create a new mapping.

Select the Source Spreadsheet

Whether you are editing an existing mapping or creating a new mapping, you will see the Import Data - Mapping Editor [2/5] screen. In the Source Spreadsheet section, use the lookup button to select the file for import. Acctivate can import in Data Files (.csv, .xls, .xlsx) or tab delimited text files (.txt). In the lookup window you may need to toggle the file type list to search for a specific file type.

If the file is a Workbook (.xls or .xlsx file type) with multiple sheets, you must select the Worksheet you wish to import in.

Check the First row of spreadsheet contains field names if the first row of your source file contains the column headers and should not be imported in. If you do want to import in the first row of data, uncheck this box.

Summary

The Summary tab allows you to enter a Template ID, name, and description so that you can save this template for future use. For example, if your vendor provides you a .xlsx file every quarter with updated product information and it's formatted the same, you could use the same template to quickly import in the updated information.

Field name	Field Length	Description
ID	Text (15 Characters)	Required to save this template. This is the unique ID for the template. This is also known as the Origin ID and can be viewed in the Order Manager window.
Name	Text (50 Characters)	Required to save this template. This is the user friendly name for the import template which is visible in the Document Type step.
Description	Text	This is an optional field that allows you to describe what information this template is importing.

Columns

The Columns tab allows to you map the data in the source file to an Acctivate data field. To map data from the source file to an Acctivate file:

Click on the Columns tab.
If necessary, expand a section to see the Acctivate data fields.
In the Mapped field column, click the dropdown to select which source file column should be mapped to the Acctivate field.

Refer to the Import Fields Resource page for more information on each field.

Note

Fields with an "R" next to it are required when creating a new record. Fields with a Key symbol next to it are the key fields that must be mapped in order to update records via an import. If updating records using an import, you only need to map the fields(s) with the Key symbol, but not the other required fields (unless you with to update those).

The Preview pane will give you a preview of the first 100 rows of data. The top row of column headers displays the source file's column names (if there are any). The row below that displays the Acctivate field that the column is mapped to.

The preview of the data to be imported will only be refreshed if the source file's file path or worksheet has been changed, or if the "First row of spreadsheet contains field names" option has been changed.

Default Values
Optionally, when using the import to create new data records (rather than updating existing data records), you can use the Default value field to set an Acctivate data field to a default value.

For example, let's say you have two warehouses in Acctivate; a "D" warehouse and an "F" warehouse. If you want to import in an inventory receipt, you must specify which warehouse it's for. The source file may not contain a "Warehouse" column, so instead you can set the "Default value" for the Warehouse field to be "F" if you want the imported receipt to use the "F" warehouse.

Conversions
In some cases, your source data may contain correct information, however it doesn't match the data in Acctivate. For example, you may export a list of products from your web store for import into Acctivate. The export may include a column for "Item Type" which has the value "Non Inventoried". Mapping this to the Item Type field in Acctivate and importing would fail due to Acctivate expecting the value "Non-Inventoried" (a hyphen between "Non" and "Inventoried"). Rather than modify the source file, you can use Conversions.

Click on the Columns tab.
If necessary, expand a section to see the Acctivate data fields.
In the Mapped field column, click the dropdown to select which source file column should be mapped to the Acctivate field.
While on that field, click Show Conversions at the bottom of the Columns tab.
In the "From" field, enter the value as it is in the source file. In the "To" field, enter the value that Acctivate would recognize.

Options

Once you've mapped your data, you can select certain import options by clicking on the Options tab.

Refer to the Import Fields Resource page for more information on the options available for each template.

Tip

The default import options are the recommended options and typically do not need to be changed.

Validate the data

Once you've mapped the data and reviewed the import options, you can begin the import which starts the data validation step. To begin the import and data validation step, click Begin Import. If there have been changes since your last save, you will be asked if you want to save. Choose yes or no. Prior to this step you have the option to click Save Mapping in the bottom left to save as you map. If you've chosen to save the mapping, you will need to enter a mapping ID if you haven't entered one on the Summary tab.

You will then see the Import Data - Data Editor [3/5] screen. Each row of data in the spreadsheet will be validated. The Statistics section in the left lower corner will display the Rows Passed, Rows Failed and Rows Total.

If all the rows passed: The Data To Import grid will display all the passed rows.
If one or more rows failed: The Data To Import grid will only display the failed rows. However, you can toggle the Show Only Invalid Rows to see all or just invalid rows. If viewing all rows, the "passed" rows will have the checkbox next to the row number checked; the "failed" rows will remain unchecked.

The Errors section next to the Statistics data will display the error that prevented the row from importing. Each row could fail for different reasons, so you must click on each failed row to see the specific message.

When you have failed rows, you have three options on how to proceed:

Click Cancel or Back to return to the mapping without importing anything.
Click Next and continue with the import, which will only import the rows that passed. After the import is complete you will have an option to save the failed rows to an Excel spreadsheet for further cleanup and re-importing.
You can make some corrections to the data in the Data to Import grid.

To correct data in the Data to Import grid, click in the field and edit it. For example, if you tried to import in "20 lb" into the Product's weight field it would fail because it's expecting a numeric value. In the Data to Import grid, you can change "20 lb" to simply "20". Once you move out of that field, the row will get re-evaluated and pass.

Once you're satisfied with the rows that have passed, click Next.

Import the data

The Import Data - Import Confirmation [4/5] screen will show the final Statistics; the number of rows that passed validation and failed. To continue, click Finish.

You will be prompted to create a Backup. While it's not required, it is strongly recommended that you create a backup prior to starting the import since some changes cannot be reversed.

Once the backup is completed (or if you skip it), the import will begin. The Import Data - Importing [5/5] screen will display the Statistics from before, the Records Created and Records Updated.

Once the import is complete you will receive an Import Complete message. If your import had any failed rows, you will receive the following message:

Import completed with exceptions. Would you like to create a spreadsheet with all rows that cannot be imported for cleanup and re-import?

Choose Yes to create a spreadsheet that has the failed rows in it. The spreadsheet will be formatted in the exact same layout as the original source file; however it will have a new column that lists the import error. This allows you to clean up the failed rows and use the same template to re-import them in. Choosing No will dismiss the message.

Click Close to close the import window.

Sales Order Import

Acctivate includes an import utility which can be used to create new sales orders from a text file (.txt or .csv).

Commonly, import files are generated from a web store. However, Acctivate does include a web store integration module for many popular web stores.

Create Template

To begin the creation of the Sales Order Import template, go to File → Import Sales Orders. This will open the Templates window. Click Create to create a new template. Alternatively, you can select an existing template and click Edit.

Add Template

In the Add Template screen, enter the following information:

ID: Enter a template ID. This name must be unique. It is limited to 15 characters. This name can be displayed in the Order Manager as the Origin ID.
Name: This is the user friendly name that will appear in the Web Store Sync window. This can be up to 50 characters and may contain spaces.
Notes: Optionally, enter any Notes. Notes entered here are only visible when editing this web store template.
Type: Select the Type of Sales Order.
Click Next.

Source

Import .txt or .csv file

Select the Type of Text Files under the Source #1 section.
Next to the Type field, select Delimited.

Optionally, you can use the Fixed-Length option. If selected, you must define the length used for each field. Check the Line Feed checkbox if lines are terminated with a line feed.
The PDG Shopping Cart (CSV) option is considered legacy and will not be discussed here.

Lookup and select your Source File.
Select the type of Delimiter used in the file. If importing in a .csv file, the delimiter will be "Comma".
If the 1st Row Field Names checkbox is checked, the first row of data will not be imported and that row of fields will be used as the column headers.
At lease one Source must be mapped. You can import up to three related source files at a time though. For example, if your order header information is in one file but your order details are in another file, you would use Source #1 and Source #2. There must be a related Key (usually the sales order number) to relate the files to each other.

Tip

The Text Qualifier is used to prevent a character that is being used as a delimiter from being recognized as a delimiter. For example, if your data is comma delimited, the text Acme, Inc would be split into two different fields since the words are separated by a comma. Enclosing the text in the qualifier (the default qualifier is double quotes) prevents that from happening. For example, "Acme, Inc" will get treated as one word and not split due to the comma.

Import from an Access database (Access 2000 .mdb)

Select the Type of Access Data under the Source #1 section.
Use the Source File lookup to select your .mdb database file.
Use the Table drop down to select your table.
At lease one Source must be mapped. You can import up to three related source files at a time though. For example, if your order header information is in one table but your order details are in another table, you would use Source #1 and Source #2. There must be a related Key (usually the sales order number) to relate the files to each other.

Note

Users also have the option to use a Pre Import Script. This is designed to manipulate data coming in from the source files if need be. Most of our customers do not see a need to manipulate the data from the source files, so this is rarely used. For assistance setting up a Pre Import Script, contact Support

After you've selected your source file(s), click Next.

Options

The options screen shows sales order import template options. Refer to the Import Fields Resource page for more information.

Click Next.

Preview

The Preview screen will give you a preview of the data to be mapped. If you have multiple source files mapped, you can preview each source file. Click Next.

Relationships

If you have more than one source file mapped, you must define the relationship between the two files. For example, if one files contains sales order header information and another contains sales order detail information, you would, presumably, relate the two files on the Sales Order Number field.

If you only have one source file mapped, you will not see this screen.

Click Next.

Mapping

The Mapping screen allows you to map a field from the source file(s) to an Acctivate field.

Expand a section to see the Acctivate data fields.
In the Mapped field column, click the dropdown to select which source file column should be mapped to the Acctivate field.

Refer to the Import Fields Resource page for more information on each field.

Note

Fields with an "R" next to it are required. The Web Order Number field (designated with the Key symbol) is required and used to associate order detail records together as the same order.

Default value
You can use the Default value field to set an Acctivate data field to a default value.

Conversions
You can convert data values in the source file to a value that will be recognized by Acctivate.

Click on an Acctivate field.
While on that field, click Show Conversions at the bottom of the Mappings window.
In the "From" field, enter the value as it is in the source file. In the "To" field, enter the value that Acctivate would recognize.

Once everything is mapped, click Next.

Tip

Click "Summary..." in the lower left-hand corner to generate a text file containing the mapping definitions. This can be sent to the Acctivate support team for help troubleshooting mapping/import issues.

Import

On the import screen, you will see either Import Orders (if the Provide confirmation before importing sales orders option is disabled) or Validate Orders (if the Provide confirmation before importing sales orders option is enabled).

Clicking Import Orders will begin the import process. Once all the rows are validated, if any warnings are encountered the import will pause and you can either exit the import window so that you can correct the warnings, or you can choose Complete Import to proceed despite the warnings.

If you click Validate Orders, the import will go through a validation step first. Whether warnings are encountered or not, once the validation step is complete you can either proceed by clicking Complete Import or you can exit the import window.

Warnings
If warnings are encountered and you choose to proceed with the sales order import, the orders will be imported in despite the warnings. For example, the source file may contain a Product ID which does not exist in Acctivate. In that case, you would receive a message similar to Could not find product in database: Product ID='BadProduct' (Subdocument 'order' Line #1). If you continue with the import, the order will be created and the product will be added as an "N" line type.

Tip

Sales order import logs can be found in the web store sync log folder by opening the web store sync log folder from the Help menu.