Transferring Data out of pc/MRP

There are many reasons why end-users might want to extract data from pc/MRP. To do this follow the simple guide lines below and you will be successful every time. We will first cover how data is stored in pc/MRP. Then, we will cover how to extract data from pc/MRP. Finally, we will address a few rules to follow.

15.5.1 Existing Data Format

pc/MRP uses a very simple and sturdy file format to store information. This format is DBF or data base file. DBF files are accessible in almost every kind of spreadsheet and database software used today. Furthermore, since pc/MRP is made through using a Microsoft product (FoxPro), almost all other Microsoft products are compatible. This makes the transfer and utilization of data from pc/MRP in Excel and Access especially easy.

There are primarily two methods to extract information from pc/MRP, end-users can either access the entire raw DBF or run reports and rather than send the report to a printer send it to a file type.

15.5.2 Accessing the Raw DBF File. (This method is not recommended)

As mentioned above pc/MRP stores all data in DBF files. These files exist in the pcmrpw directory. Most of them are intuitively logical in what they are called; sales order DBF is sales.dbf. These files are accessible through most spreadsheets and databases that are made today. Accessing the DBF files directly however is very dangerous. To ensure that data is not lost while you are manipulating the data follow the simple steps below.

• Always make a copy of the desired DBF and access the copy made, not the DBF that resides in the pcmrpw folder.
• Place these copies in a temporary directory. Do not leave them or copy them back into the pcmrpw folder. You must remember that several tables in pc/MRP also use DBT or FPT files. (The sales.dbf, quote.dbf, purchase.dbf, and bom.dbf all have DBT or FPT files.) To completely transfer all of the data you must copy both the DBF and DBT or FPT files.
• If you are opening a table that has a DBT or FPT file, you must first open this file in MS Access.
• If you are opening a table that just has a DBF you can go directly to Excel.

15.5.3 Opening the Raw DBF File in Access.

Once again if you are opening a DBF file, and this DBF file has a DBT or FPT file, you must use Access to open the DBF file. Follow the instructions listed below to open the DBF and DBT in Access.

1. Start Microsoft Access.
2. Select Blank Database and press OK.
3. Whatever name Access assigns to this database is fine. Simply press Create.
4. Now select File, and Get External Data, and Import Data.
5. For Files of type: select FoxPro (.dbf). If you do not have the option to select Files of type FoxPro (.dbf) then you will have to get your Office 97 CD and install the drivers.
6. For Look in: select the location where you put your DBF and DBT.
7. Finally, once you have tracked down the location of both files double click on the DBF file and the importing will begin.
8. You can now close the importer.
9. You can now either save this file as an Excel spreadsheet or perform your tasks in Access.

15.5.4 Opening the Raw DBF File in Excel.

10. Start Microsoft Excel.
11. Select File Open.
12. Select for Files of Type: dBase III or DBF. (It is better to use dBase III) If you do not have the option to select Files of Type dBase III or DBF then you will have to get your Office 97 CD and install the drivers.
13. Now map the Look In to the location of your file and press Open.
14. You are now free to manipulate this file.

15.5.5 Sending Data to a File Type

In many places in pc/MRP when end-users run reports there are several output options. As the picture below depicts end-users can send reports to: the display, Printers, or the file types of, ASCII, XLS, and DBF.

The best option if you intent on utilizing the information in Excel or Access is to send the report to a DBF file. Once this has been selected and you press OK to run the report you will be prompted with the next screen to save this file.

Save this report to the desired location with an applicable name. Do not change the file extension. Write this name and location on a piece of paper. We now have a few decisions to make.

What type of report did you make?

• If you made a report from a DBF that also has a DBT (remember the sales.dbf, quote.dbf, purchase.dbf, and bom.dbf all have DBT files) you will have to open this report in Access before you can open it in Excel. To do this, follow the Access instructions below.
• If you made any other type of report from any other module you can go directly to Excel. To do this, follow the instruction listed below for Excel.

15.5.6 Opening Reports in Access

For BOM reports we must use Access to convert the file into a readable format for Excel.  (If you do not have MS Access you will not be able to finish this import) Follow the steps listed below.

1. Start Microsoft Access.
2. Select Blank Database and press OK.
3. Whatever name Access assigns to this database is fine. Simply press Create.
4. Now select File, and Get External Data, and Import Data.
5. For pc/MRP versions 7.01 and higher for Files of type: select Microsoft FoxPro (*.dbf). For versions 6.80 and lower for the standard DBF files select Files of type: DBase III(*.dbf). For DBF files that were created from a report select Files of type: select Microsoft FoxPro (*.dbf). If you do not have the option to select Files of type Microsoft FoxPro (*.dbf) then you will have to get your Office 97 CD and install the drivers.
6. For Look in: select the location where you put your report. (If you cannot find it look on the piece of paper you should have written the location on. If you cannot find the piece of paper you are beyond help.)
7. Finally, once you have tracked down your file double click on it and the importing will begin.
8. You can now close the importer.
9. You can now either save this file as an Excel spreadsheet or perform your tasks in Access.

15.5.7 Opening Reports in Excel

To open DBF files in Excel follow the steps listed below.

1. Start Microsoft Excel.
2. Select File Open.
3. Select for Files of Type: dBase III or DBF. (It is better to use dBase III) If you do not have the option to select Files of type dBase III or DBF then you will have to get your Office 97 CD and install the drivers.
4. Now map the Look In to the location of your file and press Open.
5. You are now free to manipulate this file.

15.5.8 The Rules

Follow the rules below:

• Never save files back into pc/MRP. Once you have ran a report and manipulated that data you can only enter that data back into pc/MRP manually or by importing.

Note: You cannot import just one field and have the rest of the data line up.

• Never access pc/MRP DBF files directly.
• Never build links from other programs to write into pc/MRP.
• Never save any files into the pcmrpw folder. The only files that should be there are already there.
• Always make backups of everything.