Articles

Importing Vendors

Districts buying Escape generally have existing vendor records on some computerized format. This write-up discusses plans for converting those records for import into Escape. Source files for this conversion are ASCII files which we will convert to the Escape format. The rest of this document outlines the rules and format of the record layouts. This document describes the procedures for importing vendor information into Escape prior to system startup.

WARNING: THIS PROGRAM WILL REMOVE THE EXISTING VENDORS IN YOUR ESCAPE SYSTEM! If you are trying to update or load new vendors into your Escape system, then you should use the import vendor function from within Escape AP Module.

Vendor Import Rules

The file must be in ASCII, with fixed field format. NO packed or binary information! No delimiters please! You may utilize any or all of the fields specified below - when not using a field it is to be padded with blanks (ASCII 32). If the field is filled with blanks, the system will fill the field with blanks.

Date Fields

This field must be in YYYYMMDD format, eight characters in length. For example, December 1st, 2003 would be 20031201.

Time Fields

This field must be in HHMM format, four characters in length. For example, 6:00am would be 0600 and 3:30pm would be 1530.

Decimal fields

These fields accept numbers only. The fields must be "right justified" and "zero filled." No "punctuation" is allowed and the decimal point is implied. For example, if the Shipping Percentage field has been defined like this:

SHIPPING_CPT   STRING(4)   DDdd

Then a value of 8.5% would be formatted like this: 0850

Integer fields

These fields accept numbers only. The fields must be "right justified" and "zero filled." No "punctuation" is allowed. For example, if the Ship Days has been defined like this:

SHIP_DAYS  STRING(3)   DDD

Then the Ship Days of 45 would be formatted like this: 045

Another example. If the Zip Code has been defined like this:

ZIP_CODE     STRING (10)     DDDDDDDDDD

Then the zip code 95827 0978 would be formatted like this: 95827-0978 OR 95827 0978

Each record must end with ASCII Carriage Return (CR) and Line Feed (LF) characters. A number of decisions may need to be made regarding formatting the district's existing data to match our format. We will assist each district in deciding which fields to convert, etc.

Conversion Considerations

Required Fields

If you are a Purchasing stand-alone customer (not Finance), then there are only 2 required fields - CODE and COMPANY_NAME1. If you are an Escape Finance customer, then there are 3 required fields - CODE and COMPANY_NAME1 and NO_1099_FORM.

NOTE re NO_1099_FORM

The import file will set the NO 1099 flag to Y if there is no value for this field in the import file AND if there is no TAX ID number. If there is a TAX ID number, the import will not set the flag. When the user edits the vendor record in Escape, the user will be prompted to fill the 1099 field.

Status

If a vendor is active, this field should be blank! If you are importing inactive vendors, you may put a value in the field to indicate that. Most customers do not import inactive vendors.

Sort Code

Fill this with the first 10 characters of the Vendor Name.

Department

This is not a required field and is not used for selection or filtering of vendor records. It is an information-only field.

Shipping and Terms fields.

Ask your Purchasing Director for default values which you can prefill into the Terms, Ship Days, Ship Pct, Ship Via and FOB fields of all the Vendor records. By doing this, only exceptions will need to be modified.

District-Specific Codes

There are four district-specific fields to be used at the discretion of each district. Two of these fields are 2 bytes in length and 2 are 1 byte in length. An example of how a district used one of these fields is that a "1" designated the vendor as a minority-owned business.

Import File Layout

The Escape Import record layout contains the following fields which are listed below in the order required.

For your convenience, we have a column designating the position number in the ASCII file and a column heading if you are creating or editing this file in Microsoft Excel.

Running the Import Program - IMPRTVND.EXE dated 01/20/00

WARNING: Remember this program is for loading Vendors into the Escape system on start-up. THIS PROGRAM WILL REMOVE THE EXISTING VENDORS IN YOUR ESCAPE SYSTEM! If you are trying to update or load new vendors into your Escape system, then you should use the import vendor function from within Escape AP Module.

Before running the program you should back up the following groups of files found in the \ESCAPE\AP directory:

VENDORS.*
VND*.*

To run the Vendor Import Program you should be in the directory where your VENDORS.TXT import file is found. Usually this would be the \ESCAPE\IMPORT directory.

The IMPRTVND.EXE should be found in your \ESCAPE\UTIL directory. So to run the program, go to your \escape\import directory and type in the following:

K:\ESCAPE\IMPORT> \ESCAPE\UTIL\IMPRTVND.EXE

The program will automatically start to import and the results should look something like the example shown below:

Load vendors using "VENDORS.TXT" (Note: all vendor data will be deleted first)
Total converted: 33

Once complete, you should go back into Escape as normal and review the vendors file to verify the import file was loaded correctly. If the import failed, then you can restore the files you backed up before running the import program.

Verifying the Vendor Name and Addresses - VNDUTIL.EXE dated 03/23/04

Users must be out of the system to run this utility.

This utility reads the vendor file checking for names and addresses. It generates a log file with the vendor code, address seq, name and clues of invalid address records. It uses the following logic to determine if a name and address for the vendor is valid:

• The payee name is at least 2 characters in length.
• The payee street is at least 6 characters in length.
• The payee city is at least 2 characters in length
• The payee state or country must be at least 2 characters long
• The payee zip code must be at least 5 characters.
• The payee address sequence is unique.

To run the Vendor validation program you must be on the drive where Escape is located. You can be in any directory.

The VNDUTIL.EXE should be found in your \ESCAPE\UTIL directory. Type in the following:

\ESCAPE\UTIL\VNDUTIL.EXE

The program will automatically start to read the vendor records. The results should look something like the example shown below:

         Utility to report invalid Vendor Address Records
         Log produced is named \escape\ap\vndraddr.log

         Checking address sequence records for Address Elements
         Records Read: 1157

Total Read: 2100 Total Name Only Records: 116
Total Read: 2100 Total Errors: 105 

Once complete, two log files are generated:

• \ESCAPE\AP\VNDRADDR.LOG - There are two sections to the log, the first lists the vendor address records with Name only. The second section, is missing address elements.

  When there is an invalid address, the vendor number, addr seq, missing elements, and name are entered in the log. The vendor number and name are not repeated for multiple address records reported for the same vendor.

• \ESCAPE\AP\ESC_VNDR.LOG - If a vendor has a bad address, the log will report if they have a good address. A total vendor record count and error count is entered as the last entry of each section of each log.

The logs are appended to with each run of the utility. Delete it before running the utility subsequent times if you only want the errors for the current run.

• Print this page
• Email a link