Contents - Index - Previous - Next

Importing Master Files  
The Data Exchange module allows you to import master file data from your existing accounting system, thus greatly reducing the need for re-keying information by hand.  You can import data into the following master files: customers, vendors, inventory, bills of material, routings, and GL chart of accounts.
You must be able to export your accounting master files into an ASCII text format, either comma delimited or fixed length.  Most accounting systems have a data export capability that can do this.
The data import design prevents bad data from getting into the permanent files.  Data is first imported into temporary holding files where they can be edited for errors.  A transfer program then moves the data from the temporary files to the permanent files.  Only valid records are transferred.     
The data import is a three step process.  In step one you import your data into the temporary files.  In step two you edit the data for any changes or additions that might be needed.  In step three you transfer the data to the permanent files. 

Step 1 - IMPORT DATA INTO TEMPORARY FILES
Export Accounting Data
First, you must export your accounting data for any of the following files: inventory, bills of material, routings, customers, vendors, GL chart of accounts.  You must export the data to ASCII text, and it can either be in a comma delimited or fixed length format.  Most accounting packages have data export programs that will output your data to these formats.  If not, you may have to get a programmer to create the ASCII files for you.   A spreadsheet program such as MS Excel can also be used to develop the data to be imported and then saved as either CSV (Comma Delimited) or PRN (Space Delimited or Fixed Length).
Comma delimited is slightly easier to work with, but if you have quotation marks within any of your data fields, those fields will have to be hand edited from your file before the import can be run.  Otherwise, the import program gets confused because it looks for quotation marks to identify alphanumeric fields.  Quotation marks are commonly found within inventory description fields because they are used to indicate inches.  The easiest way to edit the data is to load it into a spreadsheet (such as MS Excel), make any necessary changes and then save as either CSV (Comma Delimited) or PRN (Space Delimited or Fixed Length).  If you have massive incidence of quotation marks, you should go with a fixed length format.  You may combine methods.  Use a comma delimited file for all the fields except the ones containing text, then create a second fixed length file containing only item number and the description fields including the quotations.
Run Import Programs
The import programs (DE-B thru DE-G) all function identically.  You are first asked to enter the name of the ASCII file to import, including its data path.  You should keep the file names 8 characters or less and 3 character extensions with no spaces in either the file name or path.  If the file is saved to the DBAMFG directory, no path is needed.   For example, if your ASCII file was named INVENT.CSV and was located in a subdirectory named ACCT on drive F, you would enter the following:
F:\ACCT\INVENT.CSV
Next, you are asked if you want to Skip or Replace existing item numbers.  If you have already imported some records and have already edited them, you can choose the Skip option so that those records are bypassed and won't be accidentally replaced during another import.
The Replace option can be very useful.  Inventory data, for example, often comes from more than one file.  Master data might be in one file, costs in another, and on-hand balances in another.  If you select the Replace option, the program will replace just the field values for each field you select.  Therefore, in the example of inventory, you could make three separate imports, each bringing in its own set of field values.    
NOTE: Be aware that the Skip and Replace functions only apply to records that are still in the Data Import module and have not yet been transferred to the permanent files.  These functions do not work with the permanent data files.  When you actually transfer the data to the permanent files, the transfer program will always replace existing records for all master files except for bills of material, which, because the system allows duplicate bill of material components, always appends records rather than replaces them.    
Next, indicate whether your ASCII file is in comma delimited or fixed length format.  
The rest of the screen consists of a listing of all the field names within the master file, with two entry fields to the left of each field name.
Comma Delimited Files
If your ASCII file is comma delimited, you are only allowed entry of one field next to each field name.  In a comma delimited ASCII file, each field is separated on either side by a comma.  Alphanumeric fields may be further enclosed by quotation marks.  A comma or commas between two quotation marks do not count as field separators.  Simply identify your ASCII file field position numbers consecutively from left to right, then enter those numbers next to the appropriate field.
Fixed Length Files
If your ASCII file is fixed length, you must enter two values to the left of each field  name.  The first entry is the character position of the first character in the field, and the second entry is the number of characters in the field.  It is easy to identify each field's starting character position and length by pulling up the file in a text editor (such as DOS Edit) that indicates the character location of the cursor.  Simply move the cursor to each field position and write down the location, then count the length of the field.  Then enter the starting position and length next to the appropriate field.
If any of your fields are larger than the destination data fields, you can crop your field length by specifying a starting position and length that reduces the size of the field being brought in.  For example, you might have a unit of  measure field of four characters, while the system unit of measure is three characters.  Simply reduce the length of the field from four to three and the program will cut off the fourth character when importing the field data.  On a right justified field, such as many numeric fields, specify a starting position to the right of the actual starting position and shorten the length, thus cropping the field. 
Date Fields
All the date fields are required to be ISO YYYYMMDD format in the import ASCII file.  If you are creating the file using Excel, you can create such a date format by clicking Format ?Cells ?Custom.  Then click on the blank Type field, enter YYYYMMDD and click OK and you have created a custom date format.  Apply this format to any date fields and they will now be in the correct ISO standard format for import.
Time Fields
In the routings file there are a couple of time fields.  The time fields are in a HH:MM:SS format, meaning hours, minutes, and seconds.  If your time fields are different, you may have to edit them after they are imported so they conform to the required format.
1000 Character Limit
Be aware that the import programs only accept ASCII records of 1000 characters or less.  Most accounting packages' data export programs allow you to export selected fields rather than the entire record, so if your ASCII record exceeds 1000 characters, export it again and leave out any unnecessary fields to get it under the 1000 character limit.  
Import Processing
When all the field positions are entered, start the import by pressing F10.  If the import stops and you get an error message indicating a field specification error, there is a problem with the ASCII record indicated on the screen.  This usually is caused by a comma delimited record that has a quotation mark between two quotation marks, such as with an inventory description using a quotation mark to stand for inches.  You will have to pull up your ASCII file in a text editor (such as DOS edit) or spreadsheet and fix the bad field.  Run the import again until it completes its run without any errors.
Each time you run an import, the field positions stay on the screen in the  event you need to rerun the import again. 
Erase Files Program
If for any reason any of your imports goes so badly that you want to start over, you can erase any of the temporary files and start over again by running DE-L, Erase Files.  Simply enter Y next to each file to be erased.  After entering Y or N next to each file, you are asked  if you want to begin the program.  If you answer yes, the appropriate temporary files will be erased.
STEP 2 - EDIT THE DATA
Print Error Reports
Now that your data has been imported  into the temporary master files, you should run error reports to check for missing or invalid fields needed by the master files.  Run the appropriate error reports (DE-I -A thru DE-I-E) as required.  
The error reports will list all records that need to be edited, as well as any missing or invalid values needed.
Edit Programs
You can edit individual records one-by-one through the edit programs (DE-J-A thru DE-J-G).  These screens are identical to the master file entry screens within the system, except that they access the temporary data rather than permanent records.
For details on field explanations and general program operation, study the following:  
IN-B  Enter Inventory 
BM-A  Enter Bills of Material  
RO-A  Enter Routings 
AR-A  Enter Customers  
AP-A  Enter Vendors 
AM-C  Enter General Ledger Accounts  
At a minimum you must edit the missing or invalid fields listed on the error reports.  You can also add or change any other fields at this point, or you can wait until the temporary files are transferred and make further changes within the permanent files.
Global Field Change (DE-K)
This program can be very useful for entering or changing field values globally across many records.  For example, in your accounting system a purchased item might be coded with an inventory type ?.? In this system, the inventory type code for purchased items is an ?.? Rather than have to individually change every inventory record, you can use the global field change program to change all records with a type ??to a type ?.? The program only changes critical fields listed on the error reports.
First you are presented with a choice of four files with which the program can be used.  Highlight the file you want to work with and press <Enter>.
Next you are presented with a  window listing the critical fields within that file that can be globally changed.  Highlight the field you want to work with and press <Enter>.  
If you want to superimpose a field value on all records in the file, answer Y to Replace all values?  For example, you might want all items to have a selling unit of measure of ?A.? The cursor then moves to the Value to replace with? field.  You would enter "EA" and press <Enter>.  The program will then superimpose the value "EA" in the selling unit of measure field for all records within the file.   
If you want to replace a particular value only within specific records, answer N.  You are then asked for the value to search for.  In our inventory Type example, you would enter a ?.? Enter the value to replace with, which in  our example would be an ?.? The program will then automatically replace all ??values in the inventory field with an ?.
Erase Files Program
If for any reason any of your imports do not initially go well on the first try, you can erase any of the temporary files and start over again by running DE-L, Erase Files.  Simply enter Y next to each file to be erased.  After entering Y or N next to each file, you are asked if you want to begin the program.  If you indicate yes, the appropriate temporary files will be erased.
STEP 3 - TRANSFER DATA TO PERMANENT FILES
The final step is to transfer the temporary data to the permanent master files through DE-M, Transfer Files to DBA.  Only records without missing or invalid fields will be transferred by the program.  Run the appropriate error reports before running this program to make sure all records to be transferred are without errors.
Enter a Y next to each file you wish to transfer.  If you want an error report which lists any records that did not transfer, enter Y to Print Errors Report?  The program will then begin the transfer.
The transfer completes the import process and insures that only good records get into the permanent files.

NOTE: When you are importing data from multiple files into one file, do not run this program until all your data has been imported and gathered together.  You cannot transfer data in segments into the permanent data files.