Importing Data from Excel

If your file is linked to QuickBooks then existing items, vendors and customers will already have been imported.   If you are converting from another systems or you want to import orders from a web site then you can use our Excel import feature.

All Orders provides Excel templates that can be used to import data from other sources.   The import templates are located in C:\Program Files\NumberCruncher\All Orders\Import.   The import templates contain multiple tabs or sheets representing the table structure of All Orders.    The sheets are linked together with using a primary reference.  For example, in the case of ItemInventory.xls template which is used to import items, the 'ItemFullName' column is used in all the tabs to link the Item tab with say the Vendor tab.    Another example is the AdjustInventory.xls which is used to adjust inventory; it uses the ControlRef (any alpha-numeric value) is used to link the tabs.

You will note that certain columns are bolded which means they are required.    Importing Lists can be used either to add or modify records. Only fields that need to be modified need to be in completed in the Excel template. For example, if you want to change all the prices, complete the FullName and Price Columns of the Excel template. If you wish to delete the contents of a field without replacing it put d/ in the cell.

If you refer to another record in an import that record must already be in All Orders.  For example, if you import preferred vendors using ItemInventory.xls then the vendor must already be on the vendor list in All Orders.

To import some data from Excel you first need to populate the relevant template and then choose File > Import > Excel, and then pick the data type you wish to import from the submenu that appears.

 

The following examples are provides.

 

Importing Lists

The sheets in Excel templates provided mirror table structure of All Orders.   For example, you will notice that the ItemInventory.xls (used to import all types of items) has several sheets.  The main sheet Item is the starting point.  You must enter values in this sheet first before any other sheet will be considered.   For example, if you want to add 2 vendors for a particular item,  you first enter the Item's 'FullName' and Group on the Item sheet.

Then on the Vendor sheet enter the Item twice plus each vendor once.   Note that the Vendors listed must already be in All Orders.

Importing bill of materials and/or kits is done in the same way.   

To create a kit you start with the Item worksheet as above, then populate the Kit Components worksheet repeating the item full name for each kit component.  

 

Finally populate the Kit Selections work sheet as follows:

 

Importing Transaction

Transaction Excel files also mirror the All Orders table structure.   Take for example the InvAdjustment.xls which will create a Qty Adjustment in All Orders.  It has 3 worksheets.   

InventoryAdjustmentHeader:  Used for the top portion of the Qty Adjustment.  

InventoryAdjustmentItem: Used for each item on to be included in the Qty Adjustment

LotSerialNumbers:  Used to import lot or serial #s if applicable.

 

ControlRef in the InventoryAdjustmentHeader represent 2 adjustment.  The ControlRef is repeated for each item that will be included in the adjustment in InventoryAdjustmentItem.   Notice also that for ControlRef 1, Item YY45-15 is repeated twice because I wanted to make the adjustment to 2 separate bins.

If an item has lot or serial #s, you will need to complete LotSerialNumbers.  In this case, the LotControlRef corresponds to each Lot or Serial # entry.  LotControlRef is used also to link toLotSerialNumbers to  InventoryAdjustmentItem

 

Note:

All Orders also offers its program interface (API).  The API can be programmed to import data from other databases.