Overview
Often, there will be large amounts of data in an existing database (or Excel spreadsheet) that needs to be imported into ERPlite.
Examples:
Customer Names, ship to addresses etc.
Vendor Names, fax numbers etc.
Parts lists
Bills of Materials
Others
Retyping this data is both frustrating and error prone. Much work has probably gone into verifying the accuracy of this data. Thus, it is very desirable to automate the import of this data.
Fortunately, most data systems provide means for exporting the data as a delimited text file, or Excel spreadsheet.
ERPlite Data Import Utilities
ERPlite has automated import utilities that can import most item and bill of materials data.
ERPlite Switchboard > Batch Links
If the automated utilities are insufficient, importing either formats into a Microsoft Access database is easy, and done with a very similar process. Thus, we will use text file importing to demonstrate the process.
Also, we will demonstrate importing the parts list; other imports are done in a similar way.
Parts List Import
Following is typical parts list file exported from a customers existing database. It happens to also include the current inventory count for each part.
Table 1: Parts List
Note that the file is formatted in columns. More often, in order to minimize the size of the file, the fields will be separated by a delimiter (such as , or tab). Microsoft Access easily imports any of these formats.
Importing Process Overview
Importing the data is accomplished using the following steps:
1. Export the data from the current system to a text file.
2. Import the text file to ERPlite into a new table.
3. Use an Append Query to copy the data into the Products table.
4. Visually verify the data, looking for invalid data such as blanks where text or numbers are required.
5. Fix the invalid data.
Microsoft Access Importing Tools
The "typical" installation of Microsoft Access includes only a few importing tools, and does not include the text importing tools. These text importing tools (along with several others for proprietary databases) can be installed from the Office ValuPack.
Import the Text File
1. Load the DbM_be.mdb into Microsoft Access.
2. Click on: File > Get External Data >Import.
3. On the Import Screen, select .txt in the Files of Type box.
4. Navigate to find and select the text file, click Import.
4. Since this is a fixed width text file, click Next.
5. Since the data looks Ok, click Next.
6. Then, Select "In New table", click Next.
7. Replace the default field names (in Field Options) with the desired field names (SKU, Total, Description). Verify that Access picked the correct data types, and change if necessary. Click Next.
8. Let Access choose the primary key. Click Next.
9. Import to a table named "SKU". Click Next.
10. Click Finish
** A new table (SKU) will have been created and populated in Access. If any data could not be imported properly, an error table will be generated. Inspect the error table and take appropriate action.
Append data to Products Table
Many ERPlite applications do not work unless certain key data exists. For basic functionality, this key data is highlighted on data input forms, like the ItemMaster, in orange. However, for more advanced functionality, some additional data is needed. Following is the list of fields that we recommend be completed in Itemmaster and the corresponding fields in the underlying Products Table.
Recommended default values are shown in brackets in the ItemMaster column if you have no better information available at the beginning. The following is a partial view of the needed append query to insert the three columns from the table just created into the Products table:
This query appends the data from the SKU table to the Products table along with the default data needed.
The full SQL statement is (for those who speak SQL):
INSERT INTO Products ( ProductID, PhysicalCount, ProductName )
SELECT SKU.SKU, SKU.Total, SKU.Description
FROM SKU;
FROM SKU;
11. Run the Append query.
12. Inspect the result in the Products table.
13. Open the Check Data Quality form from the Administration menu on the Switchboard and click on the Run Data Quality Check button. The following report should appear, with many errors for the new products that were just created, since they are missing essential information needed for ERPlite functionality.
14. Close the report, which will bring you back to the Check Data Quality form. Click on the Insert Default Values For Nullvalues button, which will intuitively fill in default values for the products that you just imported. Run the data quality check again to ensure that the new products are now complete with the necessary values (the error report should be empty). If other errors show up, diagnose and follow the recommended action given on the report until all data quality issues are resolved. Furthermore, each new product has default values for fields such as Standard Unit Price, and can be given its proper value in the ItemMaster/QVL form.
Import BOMs
Bills of Material (BOMs) are easier to import than the parts list, but require the parts list to already be in ERPlite. First, you need to add the parent assembly and the quantity of the child item in your BOM list, resulting in a table which resembles the one shown below which was taken from the sample data in ERPlite.
Using the Access importing tools described previously in this tutorial, import the BOM list to create a table that resembles the sample below, the important fields being the productID, the parentID, and the quantity of the productID in the parentID. Notice that the table does not contain the "ASY0008" or "ASY0008A" items. This is because in our example they are root parents, the BOM does not go any higher, thus they do not need to be included since they have no parents to define.
Then you create and Microsoft Access Append Query to append the BOM data to the ERPlite Where_Used Table:
The SQL statement for the above query is:
INSERT INTO Where_Used ( ProductID, ParentID, QtyInParent )
SELECT zzz.ProductID, zzz.ParentID, zzz.BOMQuantity
FROM zzz;
Run this query, and you now have BOMs in ERPlite. Since invalid data can be inserted into ERPlite via this query, run the Check Data Quality Report again, and follow the instructions to resolve any issues that are found. When it comes to the errors in the Where_Used table, look for products that are not defined in the Products table, or products without quantities for their respective BOM.