Importing Data Files

 

15.11.1 Overview

In essence what we need to do is change your existing data into a readable format for pc/MRP. To do this we will have to use Excel and perhaps Access to manipulate your data. Your existing data will have to be exportable into Excel or other spreadsheet formats. If you feel more comfortable with Lotus or a different spreadsheet program than Excel you are welcome to use that instead. The instructions are however written specifically for manipulating Excel files and importing them into pc/MRP. To begin this process we will first address how your data is stored in pc/MRP. The lesson will continue with how to manipulate your existing data so pc/MRP will recognize it as an acceptable format. We will then address how to import this data into pc/MRP.

This chapter is specifically written for importing Inventory and Bills of Materials. Most of the steps and recommendations however are applicable for every module. If you follow the steps, study the data structure, manipulate your legacy file as needed, and import using option 40, you should be successful every time.

One last note: the data you are importing must also be Y2K. Dates should be imported with a four digit year. Dates imported without a four digit year will not work.

15.11.2 Inventory File Structure

The Inventory Module is simple to understand. Everything that deals with inventory data is stored here. The BOM module stores the recipe while the Inventory module stores the ingredients. For complete details about the Inventory Module see the chapter on the Inventory Module. When importing data into the inventory module there is really only one key field, the part number. However, a person should try to import as many fields as possible to save time. Listed below are a few of the fields in Inventory and their use. For more details see the Inventory Module and or the Import Utility.

PARTNO This is your part number field. KEY FIELD (Limited to 15 characters)

DESCRIPT This field is your description.

MANUFACTER Part manufacturer Name

MODELNO Part manufacturer number

PART_ASSY This field labels this line item as a Part or Assembly. KEY FIELD

The rest of the fields can be imported if the data exists but they are not essential fields.

15.11.3 BOM File Structure

pc/MRP uses a very simple system to organize the Bill of Materials. Take a moment to view the picture below. The picture below depicts a simple bike assembly and the fields necessary to import a BOM into pc/MRP. (The reference designator field, Refdesmemo, is optional but will receive some special attention later.)

As we can see pc/MRP uses just a few key fields. There are more fields in the BOM.DBF, but these fields are the essential fields for importing. The Bomno field contains the BOM number. The Bomdescri field contains the BOM description. The Partno field contains the partnumber for each part in the corresponding BOM. The Itemno contains the item number of each part within that level of the BOM. The Qty field is the quantity required of each part or assembly to finish the top assembly. The Part_assy field labels that entry as a part or assembly. The Refdesmemo field stores all of the reference designators. To learn more about the fields in the BOM module you can read the chapter on the Bills of Materials Module.

Another way of showing how pc/MRP organizes entries in the BOM module is to see a report. Take a moment to view the example report below. The report below is the exact information as pictured above simply presented in a different format.

Now in the process of comparing the two reports notice that BOMNO 000000001 contains one subassembly and six parts. Within BOMNO 000000001 BOM 0000000002 exists. This BOM contains 3 parts and one BOM. The final BOM, 000000012 contains two parts. We distinguish between parts and assemblies by using the Par_assy field by using an A for assembly and P for parts. As you can see, a BOM can call an assembly. This BOMNO calling an assembly and later the assembly calling additional assemblies allows pc/MRP to have infinite leveled and indented Bills of Materials. On a final note each part has a BOMNO and each part has a BOM description.

Stated in a different way, the BICYCLE TOP ASSEMBLY calls for several parts and one assembly. The assembly it calls for is 000000002. We know 000000002 is an assembly because it has an A in the Par_assy field. Further down on the BOMNO column 000000002 is called for. This BOMNO calls for a few more parts (000000009, 000000004, 000000013, we know that these are parts because they have a P in the Part_assy field) and assembly 000000012. This final assembly calls for two additional parts, 0000000008 and 000000010.

If your intention is to import BOM data with multiple levels, you will need to format your bills of materials and subassemblies in the format exactly as described above. If you plan on importing your BOM data into pc/MRP with only one level, pc/MRP can use the same BOMNO for each PARTNO.

The BOM module uses three different files: partmast.dbf (Inventory), BOM.DBF, and BOM.DBT. All parts used in BOM data exists in the inventory module. An inventory entry must exist for every part that is used in the BOM module. In inventory this part can be only listed once. In the BOM module this part can be listed as many times as needed. All inventory data is stored in the partmast.dbf. All BOM data or recipe data is stored in the BOM.DBF and BOM.DBT.

Below is a list of the field name and use for each file in the BOM DBF. Key fields are fields that must exist and have contents for a successful import.

BOMNO BOM Number KEY FIELD

BOMDESCRI BOM description KEY FIELD

PARTNO Part Number KEY FIELD

ITEMNO pc/MRP organizes everything in lists. This numerically orders each item in a given level of a BOM KEY FIELD

QTY The quantity of the item needed in that specific level to finish that specific level. KEY FIELD

PART_ASSY This field labels this line item as a Part or Assembly. KEY FIELD

REFDESMEMO This field uses the DBT file and stores the reference designators or locators.

Take a few more minutes to absorb this. Understanding how pc/MRP organizes Bills of Materials is critical to having success in importing BOM data. From here we will use a sample where we wish to import BOM data from an Excel spreadsheet.

15.11.4 Manipulating the Excel File

Once again, our primary objective is to bring data over from an existing program or spreadsheet into pc/MRP. There are literally thousands of things to take into consideration while attempting to do this. However, if you follow the steps below you should have success.

pc/MRP as described above requires your Excel spreadsheet to exist in a specific format before it can be imported.

Take a moment to view the example spreadsheet below.

We will first make the changes necessary for an import into the Inventory Module. After that we will make the changes for an import into the BOM Module.

1. Find the Excel file you wish to import.
2. Make a backup of this file. You should never change anything without having a backup to at least get us back where we started.
3. Open the one you wish to alter.
4. The top row must only contain the column headings. Write down what information you might need from the first few lines of the spreadsheet; assembly name, number, revision...
5. Delete all of the rows so that row #1 has all of the column headings.
6. If your spreadsheet does not have column headings insert a row and enter column headings.
7. Column headings must be 8 characters or less.
8. Column headings can only contain numbers or letters. They cannot contain any spaces, slashes, Number sings (#), periods (.), or any other symbol beyond a number or letter.
9. It also helps in the nomenclature of your spreadsheet columns to name then exactly the same or similar to pc/MRP. This eliminates any potential confusion.
10. Each entry can only exist on one row. Notice that CAPP_02_01 takes two rows. If we simply importing this into inventory we could delete the extra rows used for the extended reference field. The reference field or reference designator field is only used in the BOM module. However, since we are importing this both into Inventory and the BOM module, we will append the reference fields together.
11. In Excel you must append the reference field information in the correct order to one long line. This can be done by using the "&" to connect one row with the next. The reference designators are only used in the BOM module. We do however need to think in advance to save the reference designators before we consolidate each entry into one row.
12. After you have made one long line that consolidates all of the reference designators into one row and one column you must remove the extra rows. You must however copy the new reference designators and use the special paste under edit and select values so your expression does not change when you delete the extra rows. If you simply delete the extra rows with out the special paste you will loose the complete reference designator for each part.
13. We should also add one additional column and call it the "partassy" field. Put the letter "P" for each part. This column assigns each part as a Labor part, a Part part or an Assembly part. All of our parts in this example are simply parts. If we were importing assemblies and subassemblies we would have to assign some parts with an A for assembly.
14. Save your Excel file. (Simply save it as the current version of your Excel software.

Take a few minutes manipulate the spreadsheet as outlined above.

You should end up with something that looks like the picture below.

Certainly if this spreadsheet had vendor information, more manufacturing information, and a plethora of other information we could also manipulate that and import it into the Inventory Module. With future imports simply apply the rules as outline above when importing into the Inventory module and you should be successful. However since we are importing this spreadsheet into the BOM module too, we will continue.

Taking into consideration what is needed in the BOM.DBF we need to add several columns.

15. We need a column with the BOM number.
16. We need to give each part an item number.
17. We need a column that is the BOM description.
18. Existing already are our columns for the part number, quantity, and reference designators.

Take a few minutes to add the necessary columns. In our example, since we saved the data from the top four rows we know our BOM number to be ZZ-123456-A. The BOM description is ZZ Chip Board. In the item number column we can simply right an expression = (above row)+1 or simply assign each part an item number incrementing by one, starting at one.

Finally, save this file a different name than the above file. And once again save it as the file type of your current Excel program.

After performing your edits, your spreadsheet should look similar to the picture below.

Once again, we are assuming that this BOM has only one level. If this BOM had multiple levels, we would have to change the BOMNO representative of the multiple levels. Each subassembly would be listed in the PARTNO field. This subassembly would also be listed in the BOMNO field with the specific parts it utilizes in the PARTNO field. This is exemplified in the first picture of this chapter.

We have now manipulated our files and are ready to save the spreadsheet into a format that is acceptable to pc/MRP.

15.11.5 Saving Your File As a Readable Format

The following file types are readable using the pc/MRP Import Utility.

• ASCII
• DBase III
• FoxPro Versions 2.0 through 2.6
• Lotus 1-2-3 versions 1-A through 3.0
• Lotus 1-2-3 Symphony version 1.01 and 1.10
• Microsoft Excel versions 2.0 through 4.0

All of these other formats are readable, but your best chances for success are in using the DBF or Excel 4.0 file type.

There is however, one more thing we need to take into consideration. Most of these file types do not accept lengthy fields. If we were simply importing things into Inventory and or the BOM module without the reference designators, after saving our file as a DBF file, we could go directly to the next section. However since we are importing large reference designators into pc/MRP, we need to use Access to import this data.

I will repeat this. If you are not importing reference designators, you do not need to take the steps listed below. At this time you can simply save your spreadsheet as a DBF file and proceed to the next section, Importing Data Into pc/MRP.

However, in this example we are importing reference designators. To successfully import lengthy reference designators we must use MS Access. Do not save your file as any other format. Having saved your file above as a regular Excel file is fine for now.

15.11.6 Importing Large Reference Designators

Follow the steps below if you are importing large fields and or large reference designators.

1. Take your Excel spreadsheet, and having saved it above as a regular Excel file, exit from Excel. (Do not change the file type or do any special file type, simply save it as a regular Excel 97 or newer file and close down Excel.)
2. Make a backup of this Excel File. (You only need to make a mistake once to realize when you do not have backups hours of work can be lost.)
3. Start MS Access. If you do not have MS Access you will not be able to finish this import. Select Blank Database and press OK.
4. Whatever name Access assigns to this database is fine. Simply press Create
5. Now select File, and Get External Data, and Import Data.
6. Select Look In: and find the folder where your Excel spreadsheet resides.
7. Select in Files of Type: MS Excel.
8.  Double click on the Excel file.
9. Select Show worksheet and press Next
10. Select First Row Contains Column Headings and press Next
11. Select In a New Table and press Next
12. Select Next
13. Select No Primary Key and press Next
14. Import Table to: whatever you would like to call it and Press Finished
15. You will now see you have just created a Table in Access.
16. With this table highlighted (single left click.) Select Design
17. On the row that contains REFDES do a single click on the Data Type column.
18. Change the Data Type from Text to Memo
19. Press File and save.
20. Close the Design View
21. Select File, Save As, and To An External File or Database and press OK
22. Select the desired location to put this file.
23. Select Save As Type: FoxPro 2.6 or as a dBase III if FoxPro 2.6 is not available.
24. Select an appropriate name and Export
25. Close MS Access

We have now created the files necessary to begin importing data into pc/MRP.

Having followed all of the steps listed above you have now manipulated and created the necessary files to allow you to import data into pc/MRP. We will first import data into the Inventory module. We will then search for duplicate data in the Inventory module and then import data into the BOM module. From there we will audit the BOM module to look for errors.

15.11.7 Importing Into the Inventory Module

Follow the steps below to import data into the Inventory Module.

1. Get every one off of pc/MRP. You must have exclusive rights to all of pc/MRP to import anything.
2. Now make a back up of the partmast.dbf if data already exists in pc/MRP. This is done to ensure that we can always recover the data if the import fails.
3. After making a backup of the partmast.dbf open pc/MRP.
4. If you have data that already exists in pc/MRP you should run audit duplicate parts feature in the inventory module. You will have to clean up all of your duplicate parts before you import into pc/MRP.
5. After auditing duplicate parts in inventory go to Configuration, Settings and Utilities, Next and select option 40.
6. For the Inventory Module to receive data select Part Master.
7. For Import file type select the file type you used to saved your Excel spreadsheet.
8. Press locate to begin the process of tracking down where you put your manipulated spreadsheet. Your selections should look similar to the picture below.

9. Press Ok.
10. The next screen, seen below, is where you can start lining up the columns within pc/MRP with the columns of your spreadsheet. The left side is where the columns in pc/MRP are listed. The right side is where the columns from your spreadsheet is listed. Simply click once on the left for the desired field and double click on the right to match that field with the field selected on the left. If you make a mistake you can deselect a field by highlighting it on the left and double clicking none on the right.

11. Always to be sure to select Append records to database. If you have no data in pc/MRP you can select Replace. However, if you select replace and you have data that exists in the inventory module everything will be replaced with the import.
12. When you have selected the correct field matches and made sure you have selected the correct Import Method, select Begin.
13. Wait until the import is finished to do anything else on the computer.
14. Go into the inventory module and make sure everything was imported into the correct field within pc/MRP. If you have made a mistake recover from the backup and start over. If you are importing data into an empty Inventory module simply re-import. And select replace as the Import Method.
15. Once the import is finished cancel out of the Import Utility.
16. Go to Modules, Inventory and Run the Audit Duplicate Parts. Select No when pc/MRP asks you if you want to delete duplicate parts. This way you can research the duplicate parts. Delete all duplicate parts or rename existing parts to something else. You can only have one entry per part in the inventory module.
17. After auditing duplicate parts your import into the inventory module is complete.

 

15.11.8 Importing Into the BOM Module

Follow the steps below to import data into the BOM Module.

1. Be sure to have first imported everything into the Inventory Module. As explained above all BOM data must also exist in the Inventory Module.
2. Make a back up of the BOM.DBF and BOM.DBT
3. We should have already manipulated our spreadsheet into an acceptable format. Go ahead and enter the Import Utility.
4. This time however select: pc/MRP file to receive data = Bill of Materials.
5. Select the correct Import file type.
6. Select the correct file location.
7. Press OK
8. Once again match the fields one at a time.
9. After matching the fields select the correct Import Method.
10. Be sure to do nothing else on the computer until the import is complete.
11. Once the import is complete exit from the Import Utility
12. Select Modules BOMs and Edit and make sure that the data exists in pc/MRP.
13. Once you have confirmed that the data has been imported go to Modules, BOMs, and Audit BOMs. This will search the entire BOM database for errors.
14. Correct any errors that are found.
15. Finally, select Modules, BOMs, Print BOM..... and run an exploded BOM report on the imported BOM. Make sure everything is organized correctly. Make all necessary edits to make things correct.

15.11.9 Possible Problems With Importing Excel Files

• Your Excel columns may be right justified.
  • All Excel columns should be left justified. To left justify all Excel columns, press the control and A keys. Select Left Justify, in the Excel Toolbar located above the spreadsheet.
• Your Excel file contains hidden columns.
  • To display the hidden columns, press the control and A keys. Select File, click on New, select Edit, select Special Paste, and select Values. All of the hidden columns will now be displayed. The new columns can now be edited or deleted.