Micronet Adaptor (2.0-2.9) Adaptor Routine: Text File Order Import

The Text File Order Import routine allows orders to be imported into the Accounting Connector against the Micronet adaptor, and optionally into the Micronet Distribution System (Micronet for short) for versions 2.0 to 2.9.

The orders are read from text files that contain order data and product data on each line. These text files are directly readable by humans in a Windows application such as Notepad or Excel. Typically these files contain a file extension like .CSV (Comma Separated Value) or .TXT (Text File).

Topics

  1. Prerequisites
  2. How It Works
  3. Routine Setup: Creating a new Text File Order Import routine
  4. Routine Setup: Configure Data Fields
  5. Routine Setup: Configure Settings
  6. Routine Settings
  7. Routine Data Fields
  8. Routine Message Meanings

Prerequisites

Please make sure you have read through the Get Started/Overview and the Connector Adaptor Micronet (version 2.0-2.9) documents before reading further on.

How It Works

The Text File Order Import routine looks for files in a specified filesystem location. It opens files in the directory that match the specified allowed extensions set for the routine.

It tries to read and parse the file data that's been made up of orders and products. Each row in the file consists of one order and one product. If multiple rows contain the same order ID then a single order will be created in the connector, containing multiple products from the given rows.

The routine's file parser works by first putting all the order data into a table form, based on character(s) that denote each column of the file data as well as each row.
Once a file's data has successfully been parsed into a table, then the routine will then try to match the data of each column name in the first row of the file, containing the same name configured in the connector routine's Data Fields settings. 

If the fields match up correctly then the routine will extract out each row of data into orders for the Connector (excluding the first row, since it has the column names). To do this each row must set the Order ID for the order, as well as the product Item ID.

If the Accounting Connector orders have been successfully created, they will be then saved to the connector. Optionally checks can be performed to check that the products in the order exist in the adaptor's defined Micronet, as well as optionally check if any pricing matches in Micronet.

If product and pricing checks succeed, or are not required, and the adaptor is automatically set to import orders into Micronet once they have been created in the connector, then the routine will try to generate the order files for Micronet's DataConnect application. DataConnect can then import the order file into Micronet, based on the adaptor's settings.

After all files in the directory have been read through, the routine can optionally send out an Email notification (in HTML form) containing a report of all the order files that were attempted to be imported.

Routine Process Diagram


Process flow diagram of text file imports into the Connector.
The diagram shows the process of importing text files into the connector with the Micronet adaptor routine.

Steps of the Routine Process:

  1. Find and parse the order files from a specified directory.
  2. Insert the orders from each file into the Accounting Connector.
  3. Move each text file into the success directory once all orders in it have been successfully saved to the connector. Put the datetime in its filename when placed (this limits files from being copied over).
  4. Optionally query Micronet via ODBC to check that the order products exists, and optionally have the correct pricing.
  5. Optionally create order files for Micronet's DataConnect service to read in. Mark orders in the connector as being imported.
  6. Optionally email out a report with details of the routine that just ran.
  7. Micronet's DataConnect grabs any order files in its directory and imports them into Micronet as picking slips.

Example Text File.txt

Text File with Order data
Example text file containing multiple orders and products for each.

Routine Setup: Creating a new Text File Order Import routine

  1. Open up the Accounting Connector application.
  2. In the Adaptors and Message tab, Under the Accounting System Adaptors section, click on the Settings button against a listed Micronet version 2.0-2.9 adaptors.
  3. Click on the Accounting Routines tab.
  4. In the Routine Type drop down, select the Text File Order Import option, then click the Create Routine button.
  5. In the Routine Code text box enter a name for the routine, then press the Save Routine button.

Routine Setup: Configure Data Fields

Once the routine has been created, each of the data fields in the text file need to be defined against the routine, based on the column names from the first line of the text file. You will be mapping the text file fields as "source fields" to the Accounting Connector's "destination fields" for orders.

  1. In the Data Fields tab, click on the Add Data Field button. A new data field row will get created.
  2. For the new row, set the Source Field to have a value the same as one of the header fields in the text file.
  3. In the Source Field Label set a meaning value that describes the source field.
  4. In the Destination Field, choose an ordering field for the data to be placed into from the source field of the text file.
  5. Continue steps 1 to 4 until all relevant columns from the text file have been listed.

Notes:

  • Ensure that the orderID, orderProductItemID, orderProductQuantity destination fields have been mapped to source fields. These fields are mandatory to create orders in Micronet.
  • The orderAccountID destination field must be assigned to a source field, but only if the routine's Order Account ID setting has a blank value. This ensures that an account ID is assigned against an order, otherwise the order won't import into Micronet.

Routine Setup: Configure Settings

  1. Click on the Settings tab of the routines.
  2. For each row in the settings table, click on the Setting Value field.
  3. Enter a setting value, then click to the next row. The setting will automatically save once the cursor exits the field.

Notes:

  • In the Routine Settings section below describes what each of the settings does. Use this to configure the routine.
  • If a red icon appears in the Setting Value cell, then the cell does not accept the given input. You will need to change the value in the cell to be valid.

Routine Settings

Below are the settings that can be configured for the routine.

Setting Label Allowed Values Description
Data Field Delimiter All Characters

Set the character(s) used to delimit field data in the order text file. These character(s) denote one data field value from another.
Set \t for tabs, \n for line-feeds, \r for carriage returns.

Data Row Delimiter All Characters Set the character(s) used to delimit row data in the text file. These character(s) denote one line from another.
Set \t for tabs, \n for line-feeds, \r for carriage returns.
Data Field Enclosing Character All Characters

Set the character(s) used to enclose around the field data for the order text file.
Set \t for tabs, \n for line-feeds, \r for carriage returns.

Data Field Escaping Character All Characters Character(s) used the escape enclosing characters within the field data for the order text file.
Set \t for tabs, \n for line-feeds, \r for carriage returns.
Date Data Field Format   Format for how dates are given in data fields. Use Microsoft.Net date format strings, as defined at MSDN Custom Date and Time Format Strings
Notification Email Address All Valid Email Characters Email address to send notifications to once the data import has been completed. Emails will only be sent to the address if the "Send Order Import Notification" is set to true.
Order Code Prefix All Characters Prefix to set infront of the order ID to make up the order code. The prefix text will be added at the front of the order ID field, and set as the order code.
Order File Extensions All Characters File extension(s) to match order files on.
Separate extensions using the pipe character eg. txt|csv
Ordering Account ID All Characters ID of the account in Micronet to apply the order against. This setting overrides the ordering account ID data field if it is left empty.
Send Order Import Notifcation Values: true or false Sends out an email notification containing results of the routine running.
If true, ensure that the "Notification Email Address" setting contains an email address. Also ensure that the connector's SMTP property has been configured.
Text File Folder Location Allowed Filename Characters Directory in the filesystem where order Text file are located and read from. Only files in the directory containing the extensions defined by the "Order File Extensions" setting will be read and imported.
Text File Success Folder Location Allowed Filename Characters Directory in the filesystem where order text files are moved to once they have been successfully processed. File moved to this directory will have date-time text added to the file name to ensure files do not get overwritten.
Validate Product Pricing Matches in Micronet Values: true or false If set to true, when an order is being imported into the connector, the routine checks each product in the order exists in Micronet's inventory, based on the product's item ID. If it doesn't exist then the order would not be allowed to import automatically into Micronet, but will still be imported into the connector and marked as not imported. Additionally a warning will be displayed in the connector's message log and routine email notification.
Validate Products Exist in Micronet Values: true or false If set to true, when an order is being imported into the connector, the routine checks that any pricing set for the product in the order matches the price in Micronet based on the assigned order account.
A product price only needs to be set for either the unit Price Ex, unit Price Inc Tax, total Price Ex Tax, or Total Price Inc tax.
If pricing doesn't match then the order would not be allowed to import automatically into Micronet, but will still be imported into the connector and marked as not imported.
Additionally a warning will be displayed in the connector's message log and routine email notification.

Text file Settings Explanation
Anatomy of a text file

Routine Data Fields

Listed below are each of the destination data fields that you can map the source fields of the text file to.

Destination Field Label Destination Field Description
Order created date orderCreatedDate Date when the order was created. Date can be both date and time.
Order Accounting Account ID orderAccountingAccountID Internal ID of the debtor account in Micronet. This is the same as the debtor number.
Order Code orderCode Code of the order. Typically contains the order ID and any prefix or suffix text.
ID of the product orderProductItemID Product Item ID in the order. This corresponds to the inventory item ID in Micronet.
Order Instructions orderInstructions Instructions to put against the order. Any extra information that is required to recognise or deliver the order.
Unit quantity ordered for the product orderProductQuantity Quantity of product units ordered. It can be a decimal or whole number.
Unit ordered for the product orderProductUnit Label of the unit that is assigned against the ordered product.
Description of the product orderProductDescription Description of the product in the order.
Product total including tax orderProductTotalIncTax Total price of all ordered product units inclusive of tax.
Currency pricing for the order is in orderCurrency Currency that pricing against the order is stored in. Use 3 digit ISO currency standards. http://en.wikipedia.org/wiki/ISO_4217
product units per pack orderProductUnitsPerPack Amount of units that occur when ordered in a pack for a product.
Order Account ID orderAccountID ID of the account to store against the order. This relates to Micronet's debtor account number.
Order ID orderID ID to assign against the order.
Purchase Order Number orderPONumber Number against the purchase order that the order relates to.
Purchase Order Product Number orderProductPurchaseOrderLineID Number against a product line in the purchase order that the order relates to.
ID of the purchasers product orderProductPurchaseOrderItemID ID against a product item in the purchase order that the order relates to.
Invoice ID invoiceID ID of the invoice related to the order.
Product unit price excluding tax orderProductUnitPriceExTax Total price of all ordered product units exclusive of tax.
Product total excluding tax orderProductTotalExTax Total price of all ordered product units exclusive of tax.
Product total tax orderProductTotalTax Total tax price of all ordered product units for a product line.
Product unit price including tax orderProductUnitPriceIncTax Unit price of an ordered product including tax.
Product unit price tax orderProductUnitPriceTax Tax price of an ordered product unit.

 

Routine Message Meanings

Listed below are a majority of the messages that are generated by the routine, either through the connector logs, or in the sent email notification.

Type

Message Meaning
Error The adaptor routine was not found or could not be run A error has occurred that has caused the routine not to run as it should. This error occurs when no other errors have been handled. Advised us if this occurs.
Error Unable to run "routine code" due to settings that cannot be found. Please notify us about this issue. The error occurs when the settings for the routine could not be found. This is most likely to do with a update issue when the connector was last changed. Advise us if you see this message.
Error Unable to run "routine code" due to not being able to find the mandatory data field "field name". The error occurs when one of the destination data fields has not been mapped to a source field, and this destination data field is mandatory to import orders. Look to modify the routine's data fields.
Error Unable to run "routine code" routine due to no data fields being configured. Please add data fields to the routine. The error occurs when no data fields have been created for the routine. Go into the routine and create data fields that map the text file fields to order fields.
Error Unable to run "routine code" routine due to the order text file directory not existing or not having permissions to be viewed. When the routine tried to find the directory where the order files are located, either it did not have permission to view the directory, or the directory does not exist. Look at the routine settings and/or the permissions that windows has for users on the directory.
Error Unable to run "routine code" routine routine due to the text file success directory not existing or not having permissions to be viewed. When the routine tried to find the directory where the order files are moved to once successfully processed, the routine either it did not have permission to view the directory, or the directory does not exist. Look at the routine settings and/or the permissions that windows has for users on the directory.
Error Row rowNumber could not be processed in file, aborted processing the rest of the file When the order text file was attempting to be parsed, it had to stop on the given row due to an error. View the error associated with the message to understand why it stopped at that row. Either the data it was expecting is not there, or it could not convert the data into a form needed by the connector, such as converting letters to numbers.
Error Unable to find an order or products from a row in the order text file. When the routine was reading through a text file's rows, for a given row it could not find the order ID or an account ID assigned to the order. Check the data within the order as well as the data field mappings to ensure that they are correct.
Error Order contains products that could not be found in "routine code" or have a pricing mismatch. If the "Validate Products Exist in Micronet" or "Validate Product Pricing Matches in Micronet" settings are set to true then this message occurs when either an order product in Micronet could not be found, or pricing set for the product does not match the price in Micronet, based on the account ID assigned to the order. You will need to update the item ID or pricing against each product for the order in the connector, an manually import it.
Error The order has been imported into connector and Micronet however it could not be marked as imported in the connector. An error has occurred we the order has been successfully created in the connector, a DataConnect order file has been created for Micronet, however the order import state in the connector could not have been updated. This most likely occurs if the Connector's data file was been locked or removed when the routine was running.
Error An order was successfully imported but its import status could not be updated. An error has occurred we the order has been successfully created in the connector, a DataConnect order file has been created for Micronet, however the order import state in the connector could not have been updated. This most likely occurs if the Connector's data file was been locked or removed when the routine was running.
Error The order could not be imported into Micronet. Please manually do this within the Accounting Connector. An order was successfully created in the connector, but a DataConnect order file was not generated. Locate the order in the connector, select it and click the import buttons to have the DataConnect file generated, and the order to be marked as imported.
Error The order been imported into connector but could not be imported into Micronet. An order was successfully created in the connector, but a DataConnect order file was not generated. Locate the order in the connector, select it and click the import buttons to have the DataConnect file generated, and the order to be marked as imported.
Error Imported Into Connector But Not Micronet. An order was successfully created in the connector, but a DataConnect order file was not generated. Locate the order in the connector, select it and click the import buttons to have the DataConnect file generated, and the order to be marked as imported.
Error The order could not be saved to the Accounting Connector, check the settings of the connector. The error can occur if the Accounting Connector's data file became locked or moved while the routine was running. Check the connector data file directory.
Error Unable to move the file to the success directory: "path". Please manually move this file.

After an order file was successfully process, the routine was unable to move the order file to the success directory, as configured in the setting "Text File Success Folder Location". You need to manually move the file into the configured routine, otherwise the order file will be processed again and duplicate orders will appear in the connector. The cause for this issue could be that the directory was deleted, or has permissions set so that the connector cannot move a file to it.

Error Unable to move the order text file: "path" to the success directory since not all orders inside the file could be processed. When the routine tries process all the orders within an order text file, if one line in the order cannot be processed, then this message will be raised. Until all files have been successfully processed then the order text file will be moved.
Error Unable to move the file to the success directory since not all orders inside the file were successfully processed. When the routine tries process all the orders within an order text file, if one line in the order cannot be processed, then this message will be raised. Until all files have been successfully processed then the order text file will be moved.
Error File could not be processed since its data could not be parsed correctly. When an order text file is being read by the connector, if it cannot correctly parse the data within the file then this error will occur. This typically happens of the routine data field mappings or routine delimiter settings do not match the content of the file.
Error File could not be processed since it does not contain a valid extension (file extension) When the routine looks for text files in the text file folder location. If a file contains an extension that is not listed in the routine "Order File Extensions" setting, then this error will occur. Look to keep only order files with valid extensions in the directory.
Error Text file order import routine could not be run. This error occurs when the routine cannot process any files, either because the settings and data fields are not configured correctly, or directories have not been correctly set.
Error An error occurred while trying to run "routine code" routine, the routine has been aborted. This is a generic error that appears when no other errors have been raised. View the details of the error message to find out the exact cause. If it is not understandable please contact us.
Error An error occurred while trying to send out an email notification for the "routine code" routine. After the routine has completed, if the "Send Order Import Notifcation" is set to true, the routine will try to send out an email notification with a report on how the routine ran. If the routine cannot send out the email this error will occur. It can be caused by the email connector settings not being configured correctly, or the SMTP server not able to send emails.
Info Imported Into Connector And Micronet Message occurs when an order in an order file was success saved to the accounting system and a DataConnect file generated for Micronet to import.
Info The order has been imported into connector and Micronet Message occurs when an order in an order file was success saved to the accounting system and a DataConnect file generated for Micronet to import.