1-866-278-6243
Importing Data from Excel
Importing Data from Excel
There are times when you might not have the desire or ability to enter data directly into All Orders. Fortunately, the application makes it easy to import data into your main entity tables from a standard Microsoft Excel spreadsheet file. Why might you want to do this? Here are a couple of examples:
You have a colleague who wishes to enter records, but is currently in a remote location with no access to the All Orders database.
You have records stuck in an old database application. Since most data-intensive applications support export to Excel, this is a handy way of converting the old data without having to retype everything.
To import some data from Excel, choose File > Import > Excel, and then pick the data type you wish to import from the submenu that appears. You have five options:
Of course, in the file that you import, you'll need headers in place to let All Orders know which columns to import into which fields. Fortunately, you have a template .XLS document for each of the data types just waiting for you in the Import folder within your All Orders 4 install directory. Feel free to copy these documents and use them for adding your data.
Once you've chosen a data type, you'll be prompted to choose a file. By default, All Orders will take you to the aforementioned Import folder as well as auto-select the file based on the data type you chose. Click Open to import the file's contents.
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.
Notes:
You should not delete any columns or individual work sheets.
The columns in bold are required.
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. For example in the kits.
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:
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
More questions?
Call to speak with a NumberCruncher Solutions Consultant at:
