Acctivate offers a built-in utility which allows users to import sales orders from text files (like Excel spreadsheets) or an Access database. The sales order import utility is designed to dramatically speed up the process of creating orders in Acctivate which were originally placed outside of Acctivate. This article is going to focus on importing orders using a text file source as this is the most common way our customers import sales orders. This is separate from our Web Store and EDI Modules.
This article will cover the process of setting up the Templates used for sales order imports, the mapping within those templates and the functions and capabilities of the sales order import. If the template is already set up, follow the steps in this article to import orders.
Step 1: Obtaining the Source File.
The first step in configuring Acctivate to import sales orders is to create or identify the source which Acctivate will use to import orders. This article will focus on a Text file (Excel spreadsheet) source delimited by comma. Note that any characters that may be included in the source file data should not be used to delimit the file. For instance, if a comma is used in a Product ID in the source file, comma delimited should not be used.
Step 2: Setting up the Import Template.
Acctivate allows for the creation of multiple templates, so a user performing the import can decide what source to pull data from. This means that a unique template could exist for different customers, EDI trading partners or for each web store shopping cart. Once a template is created, a user could edit, delete or mark a template to be inactive. When processing an import, a user will be prompted to verify the source file used to ensure that Acctivate is collecting the desired data.
To setup the template, first go to File > Import Sales Order. This is also the first step in processing order imports after the template is set up.
Next, click Create. Notice the Begin Import option on the bottom right on this window, this will be used after the template is set up to import orders.
Next, enter an ID and Name for the template. The Name will automatically match the ID as it’s entered, but this can be changed to be unique. Notes can also be added to the Template, if desired. Finally, the template Type must be selected. If this drop down menu shows both Sales Order as well as Web Store, the company is licensed to use the Web Store Integration module. This help article focuses on setting up imports independent of the Web Store Integration. If wanting to set up an integrated Web Store mapping using a web store’s XML-based API, check out this help article. For all other sales order imports, select Sales Order and click Next.
Next, is the Source section of the template setup. In this window, select the Type of the Source file(s), the location of the file(s) and define how Acctivate should read these files.
First, select the source Type. This can either be a Text File or an Access Database source. Again, the most common method used by our customers is the Text file (Excel spreadsheet) option. If using a shopping cart that exports data into an Access database, link based on a database Table. For further assistance setting up the import template using an Access database, contact Support.
Next, select how Acctivate should read the fields in the source file. The options include Delimited, Fixed Length or PDG Shopping Cart. Delimited is by far the most common option selected as text files in Excel already use delimiters. How the file is delimited can also be defined. For instance, a .csv file uses commas to separate data (CSV = comma-separated values). Comma, Semicolon, Tab and Other are the delimiter options. If selecting Other, the character used is manually defined. Acctivate also needs to know if the first row on the file contains field names, if so, select this option. If not, leave it blank. Note the PDG Shopping Cart option is a legacy option created specifically for the PDG shopping cart, before the Web Store module was created. This is mostly unused now.
If using the Fixed-Length option, define what the Fixed Length should be, as well as selecting if a Line Feed is used or not.
Next, select the Source File that is going to be used for the import. If using one Source File, only information in the Source #1 section of the Source window needs to be entered. To select the file, click on the three dots to the far right of that field to open a new window and browse to the desired file.
Acctivate offers an option to split order information (Ship to Address, Bill to Address etc), Header information (Order Number, Customer etc) and Detail information (Products, Quantities, Prices etc) into as many as three different files using a specific Key field to link the data between the source files. This Key is usually the Web Order Number. Acctivate does not require that the import use one, two or three files, but Acctivate supports this process if your shopping cart or e-commerce platform wish to export orders in multiple files.
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 the source file(s) are defined, click Next.
In the next window, configure the Options for the import template.
The Options are explained below, we typically recommend setting the Verbose Logging to Yes. This way, if an error is encountered in the import, it’s easier to find the source of the issue so it can be fixed.
|Create customer||Yes/No||When set to Yes, new Customers are created from the import. When set to No, a warning will show in the importing stating that no customer exists for the order, then the order may be created, but the Customer will be blank.|
|Create reminders for warnings (Business Alerts)||Yes/No||If warnings are received during the import, Acctivate can create Business Alerts for users to review the warnings.|
|Show advanced configuration settings||Yes/No||Generally used by our Support team. This section allows us to customize the way information is read during the web store import.|
|Verbose logging||Yes/No||Keep this option set to Yes. It allows access to all information available in the log files.|
After clicking Next from the Options page, the Preview page will populate where data in the source file can be reviewed and verified. The three tabs show a preview for each source file used. Any unused source files will simply display as “none” on the tab. Once you’ve verified the source data, click Next. Note in the screenshot below that the row on top is not considered a data row. This is because of the “1st Row Field Names” option that was set earlier on the Source window.
Here is an example if using multiple source files. Notice that the tabs show the other source files instead of “none.”
Step 3: Mapping data from your Source file(s) to Acctivate.
After clicking Next from the Preview window, the Mapping window will populate. This is where data in the columns of the source file will map to values in Acctivate. For a full list of fields included in the import mapping, with descriptions, check out this help article. Note the R to the left of some fields. This indicates that the field is Required.
Expand Order Document, then Order Detail and Order Header to show all of fields in Acctivate which can be mapped to from the source file. Most of these fields are not required, only the fields with an ‘R’ must have data in order to import. To map a field, simply click on the line, then use the drop down to select a column from the Source file under the Mapped field, or set a Default Value for that field using the drop down under Default value.
Setting a Default Value means that every line in the source file is going to use this defined value, even if that data does not exist in the source file at all. This is commonly used to define the Warehouse in the Order Detail section, Branch ID or Customer (if using a single customer for all imported orders) in the Order Header section of the import mapping.
Repeat this process of mapping fields from the source file, or defining Default Values, until all of the data you wish to have included on the orders created from the import are defined.
If using multiple source files, set the Relationship between each source file before mapping fields. In the screenshot below, for example, the Web Order Number is used as the Key (which is typical). The next options that could be used to relate the source files are set to “-none-” so they are not used. As many as four fields can be used to relate one source file to another. Once the related value is set, click Next to see the Mapping window.
Mapping fields using multiple Source Files is almost exactly the same as using a single file, with one difference. In the drop down which shows the values in the source file, each new file will start with _ROWNUMBER which is assigned by Acctivate. _ROWNUMBER indicates the start of a source file. All values under _ROWNUMBER are related to that source file, until another _ROWNUMBER is encountered which indicates the start of the next source file. Once all of the mapping is completed, click Next.
That’s it for the setup and mapping. Click Next from the Mapping window to populate a screen with an Import Orders button. Click on that button to begin the import. A progress bar will show the orders importing along with a listing of what is going on with the import. Notice in the screenshot below that the Web Order 684241 is created as Acctivate order number A1523T. Any errors or warnings in the import will show here (which is why enabling Verbose Logging in the Options can come in handy).
Once that first import is complete, click Close and a new window will pop up asking if you would like to save changes to the template. Select Yes to save the template, then go look at an order created by the import in Acctivate. Verify that all of the data looks as expected. If something is missing or in the wrong place, go back to the template and make the necessary changes.