Import Vendor Data Version

Contents

Overview

The vendor data import feature provides a convenient way to add or change Materials Management and AP vendor information. The feature also allows you to synchronize vendor information in the system with an external (i.e., non-) AP application's vendor records. The data that you import are records that describe the vendor, one or more buy-from locations, and/or AP locations. Information might include; for example, address, discount terms, payment terms, default buy-from location, and so on. (If you wish to import vendor catalogs into the system, use the EDI 832 facility.)

You can add a new vendor record, change vendor information, logically delete a vendor, suspend vendors, reactivate vendors, and more. You can add, change, suspend, or delete AP locations and buy-from locations independently of the vendor that they belong to.

This topic explains how to add vendors to ERP using imports. You can also enter vendor data manually, which is covered in this topic: To add a vendor manually.

Note: A system setting, described below, lets you decide if you will enter vendor data manually, import vendor data, or do both. If your site is set up to import vendor data only, you will not be able to change or create vendor records manually.

Security

Display of vendor imports (Imports/Exports & Financials > Vendor Import) is controlled by the role object "Vendors." The minimal setting is View.

Users must have the proper authorization to update vendor information via an import file.
Authorization is set on the Users - Authorities tabbed panel, and through role objects "Vendors" and "VendorsMMandAP."
The flag on the Authorities panel is Allow Add/Update/Delete of Vendor Type "MM and AP." The flag is set "on" by default.

To access the Authorities panel:
- From the Work in Administration main Contents, select Administration > Users.
- The Users list appears. Locate the user of interest.
- Click Menu > Edit or click the edit icon .
- Click the Authorities tab. (It is the second to the last tab.)

Select the Method for Adding Vendors in System Values

Below are instructions on how to add vendors to ERP manually. You can also import vendor data, which is covered in this topic: Import Vendor Data.

Users want to add vendors to ERP with both the import method, or manually, as they choose. A feature field Vendor Maintenance Mode is available for this purpose (Figure 1).

Figure 1 - Vendor Maintenance Mode: Select Method of Adding New Vendors

Before you add a vendor to ERP, check this field and set it as you wish.

1. From the main Materials Contents, select Administration > System Values. System values appear.
2. Click Menu > Edit. A tabbed panel appears.
3. Select the Other tab.
4. Click the drop down arrow and select the desired option:
   - To always only enter new vendor records manually: select Manual Only.
   - To always use the vendor import only for new vendors: select Import Only.
   -To use either the import or manual entry, as you choose, at any time: select Manual and Import.
5. Click Submit.

Steps for Importing Vendor Data

Importing vendor information has these steps:

• Set the Vendor Maintenance Mode to Import Only or Manual and Import.
  To update vendor information through imports, you need to check a box in System Values to tell the system that you are using the update method. Details are in subsequent paragraphs.
• Create a .csv export file containing the vendor records. The file fields must be separated by commas.
• Edit the file to enter information in the correct fields for the type of import that you are doing. Types of Imports lists the imports that you can do.
  
• Import the file into the system.
• Debug import and update problems.

When you upload a vendor file, the system performs these steps:

1. Validates the file - the system checks the file for viruses, verifies that the file is a vendor update file, and determines if the file format is correct.

2. Reads the file - the system reads the file to determine the action(s) needed; for example, adding or changing a vendor record. You set the actions in the import file's ACTIONCODE field for each vendor record. See "How ACTIONCODES work."

3. Processes the file to update the system vendor information - the system updates its vendor records based on the information in the import file.

If the system cannot complete any step because of errors, it logs the errors. You can view the errors and correct them.

Note:
The field Help on the Authorities panel explains how the Allow Add/Update/Delete of Vendor... flag works, in concert with the relevant role objects.
- Click the Help button. Tabbed help information appears; click the Authorities tab.

Types of Vendor Imports

Several types of imports are available for vendors. The file format is the same for each type, except that data will be needed in different fields, depending on what you are importing. You can ...

• Add a vendor only (no buy-from location or AP location)
• Add an AP location only
• Add a buy-from location only
• Add an MM vendor and buy-from location at the same time
• Add an AP vendor and AP location at the same time
• Add an MM/AP vendor, AP location, and buy-from location at the same time
• Change vendor information
• Change buy-from location data
• Change AP location data
• Make a buy-from location the vendor default
• Make an AP location the vendor default
• Change the default invoicing location on a buy-from location
• Suspend/unsuspend a vendor
• Suspend/unsuspend a buy-from location
• Suspend/unsuspend an AP location
• Delete/undelete a vendor
• Delete/undelete a buy-from location
• Delete/undelete an AP location
• Add/change/delete hospital account numbers.

Creating and editing a vendor import file

1. Generate or create the import file.

• Your external AP application may have a utility that can save records as .csv files. If so, create a .csv export file containing your vendor information. Write the file to a directory that you can access with a text editor program and/or a spreadsheet (Microsoft Excel).
• Otherwise, if you have no utility to generate the file, create the .csv file manually. Open a new text file (or spreadsheet, but see the Important Note below) and populate it with the data that you will import into the system. Make sure that your program can save the file in .csv format. The file name must be unique. You will need to edit the file.

Important Note: For editing the file, a text editor is preferable to a spreadsheet program. Spreadsheet programs (e.g., Excel) add commas for blank columns. The system cannot process trailing commas. Also, the spreadsheet column formats must match the formats expected by the system. Simply saving your file from the spreadsheet does not guarantee that the column formats will be correct. For this reason, editing and saving a .csv file in a text editor is better.

2. Open the .csv file that you made in the previous step and edit it so that the fields and field values are consistent with the fields and values that the system expects. (See the Important Note above.)

3. Add Rows 1 and 2.

• Row 1 contains header information. The header contains three fields (Table 1):

Table 1 - Row 1 Fields for Vendor Imports

Field	Format	Enter:
ImportType	text	VendorExt
Customer	text	e.g., CHS (Carolina Hospital System)
Date	MMDDYYYY	e.g., 11092010


For example, your first row might look like this: VendorExt,CHS,04212011
• Row 2 contains the name of each field in the order that the system expects. Import file fields for data and examples are in the next section. Table 3 links to examples.
  
• Rows 3 and on are for your data.

Note: The data values that you enter in each column in the .csv file must match any parallel data values that you have defined in the system tables. For example, if your Discount Table contains the value: 2/10, you cannot enter 2%-10 as data in your .csv file. The import process will reject 2%-10.

• Edit your data as needed.
• Save your file (as type .csv) and close the spreadsheet program.

4. Reopen the file in MS Notepad (or another text editor) so that you can see the data as lines containing commas.
5. Add the following end-of-file marker after the last line of comma-separated data, on a line by itself:

<EOF>

Important: Your file must contain <EOF>, by itself, on the last line.

6. Save the file and close the text editor.

Import File Fields and Examples

Figure 2 displays the fields and their order on the comma-separated import file.

Figure 2 - Vendor Import File Fields - Extended Version

Table 2 provides details about fields that let you import different types of vendor data.

Difference between using the vendor edit panels on the application and importing vendor data with the template:

Several numeric fields that contain numeric data on the vendor edit panels automatically default to zero when you use those panels and leave the fields blank. This situation is not the same when you use the vendor import template. These fields, which you might normally leave blank (for "no") in the application panels must contain a value on the import template. Thus, the table below describes these fields as "required=Yes" when importing data.

Table 2 - Field Settings for Vendor Data Types - Extended Import

VENDOREXT	CustomerID	Time Stamp
Column Name	Max Character Length	Max Numeric Length	Decimal Positions	Required	Notes	Used With
RecordType	1			Yes	1=Vendor, 2=AP Loc 3=Buy From, 4=Vendor/BFL, 5=Vendor/APL, 6=Vendor/BFL/APL Note: Types 4,5,6 for Action Code 'A' only 7 - Asset Loc Hospital Account 8 - Department Hospital Account	All
ActionCode	1			Yes		All
VendorNumber	15			Yes
ExtAPVendorNo	15			Yes		All
VendorType	1			No	1=MM, 2=AP, 3=MM/AP Used for Vendor Adds only	1,4,5,6
VendorName	50			No		1,4,5,6
VendorID	15			No		1,4,5,6
BuyFromLoc	15			No		3,4,6,7,8
APLocation	15			No		2,5,6
AddressName	50			No		2,3,4,5,6
AddressLine1	50			No		2,3,4,5,6
AddressLine2	50			No		2,3,4,5,6
AddressLine3	50			No		2,3,4,5,6
City	30			No		2,3,4,5,6
StateCountry	2			No		2,3,4,5,6
Country	30			No		2,3,4,5,6
ZipCode	10			No		2,3,4,5,6
TaxID	15			No		1,4,5,6
Code1099	10			No		1,4,5,6
CustSvcContact	50			No		2,3,4,5,6
ContactTelNo	30			No		2,3,4,5,6
FaxNumber	30			No		2,3,4,5,6
ContactEmailAddress	30			No		3,4,6
DefaultBFLYN		1	0	Yes	1=Yes, 0=No Sets BuyFromLoc as Vendor default (MM, MM/AP)	1,4,5,6,8
DftInvoicingExtAPVendor	15			No		1,4,5,6
DftInvoicingAPLoc	15			No		1,4,5,6
PaymentTerms	10			No		1,4,5,6
PaymentMethod	10	1		Yes		2,5,6,8
Bank	15			No		2,5,6
BankAccount	15			No		2,5,6
BankAccountRouting	15					2,5,6
DiscountTerms	10					1,4,5,6
DefaultAPLYN		1	0	Yes	1=Yes, 0=No Sets AP Loc as Vendor default (AP only)	1,4,5,6,8
DeleteSuspend	1			No		1,4,5,6
VendorGroup	15			No		1,4,5,6
PaymentPriority	15			Yes	Value may be 1 through 9	1,4,5,6,8
POTransmitType	15	1		Yes		3,4,6,8
MinOrderAmt		19	4	No		3,4,6
DftFreightType	15			No		3,4,6
VendorOrderCycleCode	10			No		3,4,6
SeparatePOByDeptYN		1	0	Yes	1=Yes, 0=No	3,4,6,8
SepPOByItemTypeYN		1	0	Yes	1=Yes, 0=No	3,4,6,8
AutoAuthEDIPOYN		1	0	Yes	1=Yes, 0=No	3,4,6,8
AutoAuthFaxPOYN		1	0	Yes	1=Yes, 0=No	3,4,6,8
AutoAuthEmailPOYN		1	0	Yes	1=Yes, 0=No	3,4,6,8
FillKillYN		1	0	Yes	1=Yes, 0=No	3,4,6,8
AutoProcessASNYN		1	0	Yes	1=Yes, 0=No	3,4,6,8
Org	15			Yes		7,8
AssetLoc	15			Yes		7,8
Dept	15			Yes		8
DeliverToLoc	15			Yes		8
HospitalAcctNo	15			Yes		7,8
DftPayingAPLoc	15			No		2,5,6
DftPayingVendor	15			No		2,5,6
EmailAddress	30			No		2,5,6
TaxGroup	10			No		2,5,6
TaxType	10			Yes		2,5,6,8
DirDlvYN		1	0	Yes		3,4,6,8
FreightLimit		19	4	Yes	Decimal value for the approximate amount allowed as a freight charge. The Freight Limit is transferred to a PO header when the PO is created, but can be changed on the PO.	3,4,6,8
RGARequiredYN		1	0	Yes	1=Yes, 0=No	3,4,6,8
TaxIDType	10			No		1,4,5,6
LegalNameFor1099	50			No		1,4,5,6
AddressLine1For1099	50			No		1,4,5,6
AddressLine2For1099	50			No		1,4,5,6
AddressLine3For1099	50			No		1,4,5,6
APLocationFor1099	30			No		1,4,5,6
CityFor1099	2			No		1,4,5,6
StateCountryFor1099	30			No		1,4,5,6
ZipCodeFor1099	10			No		1,4,5,6
CountryFor1099	30			No		1,4,5,6
AutoMatchEDIYN		1	0	Yes	1=Yes, 0=No	1,4,5,6,8
DftDistProfile	15			No		1,4,5,6
FinRptFld1	10			No		1,4,5,6
FinRptFld2	10			No		1,4,5,6
FinRptFld3	10			No		1,4,5,6
ProjectCode	10			No		1,4,5,6
FundCode	10			No		1,4,5,6
GLAcctDftDistP1	15			No		1,4,5,6
GLAcctDftDistP2	10			No		1,4,5,6
GLAcctDftDistP3	10			No		1,4,5,6
SepPmtPerInvYN		1	0	Yes	1=Yes, 0=No	1,4,5,6,8
InComplianceYN		1	0	Yes	1=Yes, 0=No	1,4,5,6,8
SmallBusinessYN		1	0	Yes	1=Yes, 0=No	1,4,5,6,8
FemaleOwnedYN		1	0	Yes	1=Yes, 0=No	1,4,5,6,8
LocallyOwnedYN		1	0	Yes	1=Yes, 0=No	1,4,5,6,8
MinCertifiedFromDate	10			No		1,4,5,6
MinCertifiedNo	15			No		1,4,5,6
MinCertifiedToDate	10			No		1,4,5,6
MinorityCertifiedYN		1	0	Yes	1=Yes, 0=No	1,4,5,6,8
MinorityVendorYN		1	0	Yes	1=Yes, 0=No	1,4,5,6,8
PreferredStatusYN		1	0	Yes	1=Yes, 0=No	1,4,5,6,8
RestrictedVendorYN		1	0	Yes	1=Yes, 0=No	1,4,5,6,8
BTGLNCode		13	0	Yes		1,4,5,6,8
STGLNCode		13	0	Yes		1,4,5,6,8
AsianIndianYN		1	0	Yes	1=Yes, 0=No Set to zero (0) if not entered	1,4,5,6,8
AsianPacificYN		1	0	Yes	1=Yes, 0=No Set to zero (0) if not entered	1,4,5,6,8
BlackYN		1	0	Yes	1=Yes, 0=No Set to zero (0) if not entered	1,4,5,6,8
HispanicYN		1	0	Yes	1=Yes, 0=No Set to zero (0) if not entered	1,4,5,6,8
NativeAmericanYN		1	0	Yes	1=Yes, 0=No Set to zero (0) if not entered	1,4,5,6,8
VeteranYN		1	0	Yes	1=Yes, 0=No Set to zero (0) if not entered	1,4,5,6,8
VeteranServiceDisabledYN		1	0	Yes	1=Yes, 0=No Set to zero (0) if not entered	1,4,5,6,8
LGBTQYN		1	0	Yes	1=Yes, 0=No Set to zero (0) if not entered	1,4,5,6,8

Table 3 explains field settings that implement different import types, and provides examples.

Table 3 - Using Fields for Different Types of Vendor Imports

How ActionCodes work

The add (A) and delete (D) ActionCodes add and delete vendor records, including buy-from locations and AP locations. The C (change) code changes vendor information, suspends, and reactivates vendors. The C code works as follows:

When you enter C in the ACTIONCODE field, and a date in the SUSPENDEDDATE field, the system checks to see if the vendor is already suspended, and if not, suspends the vendor.

When you enter C in the ACTIONCODE field, and leave the SUSPENDEDDATE field empty, the system checks whether the vendor is already suspended, and if so, reactivates the vendor.

When you enter a C in the ACTIONCODE field, and the changes you wish to make in other appropriate fields, the system first checks to determine if you intend to suspend or reactivate a vendor. If there is no date in the SUSPENDEDDATE field, and if the system's record for the vendor shows that the vendor as active, the system assumes that you wish to make other changes.

Note about suspended vendors: When you "unsuspend" a vendor using an import, the system unsuspends all AP and Buy-From locations for the vendor. You can suspend and unsuspend AP locations and buy-from locations separately.

Note about adding vendors: When you add a new vendor, the system creates a new vendor number for the vendor, and associates the new vendor number internally with the ExtAPVendorNo value from the input file.

To import vendor data into the system

1. From the Materials Management main Contents, select Imports/Exports & Financials > Vendor Import.
   Or, from the Accounts Payable Contents, select Imports/Exports > Vendor Import.
   The Import List for Import Type Vendor appears (Figure 3). The list contains vendor files that have been uploaded.

Figure 3 - List of Imported Vendor Files

2. Click New. The Vendor Import panel appears.
3. Enter the file that you wish to import to the system in the File to Upload field.

• Click Browse (on Microsoft IE or Edge) or Choose File (on Chrome) to locate the file.
• Click the file name to select it.

4. At this step, you have a choice. You can work through the import process in two stages or in one stage. For new users of this feature, we recommend the two-stage approach.

• The two-stage approach first loads the .csv file and validates it. Once the file has successfully loaded, you can import the information into the system.

- Select the box labeled Load and Verify, No Import.

- Click Submit. The system attempts to upload your file and validate it. If successful, the system sends you a message. If the system cannot upload your file and validate it, error messages appear. Read and close the error messages. Make changes as needed in your .csv file and try the upload again. Click here to read debugging information.

The Import List for Import Type Vendor appears.

- Click Refresh to see your file listed.

• The one-stage approach loads and immediately updates the system's data using the file records. This method is efficient if you are confident that your .csv file will not contain many errors.

- Click Submit. The system attempts to upload your file and validate it. The Import List for Import Type Vendor appears.

- Click Refresh to see your file listed.

5. Review the Import List... entry for your file. The following fields provide useful status data:

Import Status Desc - The successful or unsuccessful status of the upload.

Errors: The .csv file could not be read or validated.

Ready for Processing: If you have used a two-stage upload, the file is ready for the second (update) step.

Complete: Update of system data from the import file is complete and successful.

Incomplete: The file could not be brought into the system. (You may have the wrong file name or path.)

Processed - The number of data records processed out of the total uploaded. This number shows how many of the import file records successfully updated the system vendor records.

n of X: X is the number of data records uploaded and n is the number of records processed.

For a one-stage upload, this field indicates whether the system was able to update its vendor data using the imported file's records, and, if so, how many updates were successful. 0 of X means that the system was not able to update any of its vendor data using the imported file's records. Click Menu > View Import Details. The Vendor Import Details list appears, and you can review your data for errors.

For a two-stage upload, 0 of X is normal.

To complete processing of your import file:

If you are using the one-stage approach, the system has run the import step for you. You can review any errors, and begin fixing any problems.

If you are using the two-stage approach, you need to process the vendor file that you uploaded so that the the system records are updated. Do the following:

1. From the main Contents, select Vendors > Vendor Import. The vendor Imports table appears.
2. Locate the file that you wish to process.
3. Click Menu > Reprocess. the system asks you to verify that you wish to reprocess the file.
4. Click Submit.

• The system tells you how many vendor records were successfully processed, and how many contained errors.
• If you have no processing errors, then you are finished with the update, and the Import Status Desc is Complete.
• If you have errors, Import Status Desc contains the value Errors. See To debug your import file to correct the errors and click Menu > Reprocess again. You may need to make changes in your .csv file and re-import the file.

To debug your import file:

You can encounter errors at any or all of the stages of vendor record import and update. Error messages provide information about problems with the .csv file. In most cases, you will wish to alter your .csv file. Sometimes, you may wish to change values in the system tables, such as establishing new Discount Terms to accommodate a particular vendor's data.

A. At the validation and reading stages:

1. Check that the file name and path are correct.
2. Make sure that the import file is a .csv file.
3. Verify that your file has the header line.
4. Check that you have included a line containing the correct column names, in the correct order, and that none are missing.
5. Check that the correct number of fields are in the import file for each record.
6. Review the ACTIONCODE values to make sure they are correct.
7. Check that the file contains <EOF> on the last line.

B. At the update stage:

When you select Menu > Reprocess, the system attempts to update its vendor records using the imported records. The number of records successfully updated appears on the Import... list, and errors in processing each record are logged. To see the errors:

1. Click Menu > View Import Details. The imported records appear (Figure 4).

Figure 4 - Vendor Import Details

2. Review the Processed field for each record to determine which have errors. Yes indicates that the record successfully updated the system. If the field is empty, the record has a problem, and could not update the system.
3. Click View Errors to get a list of the problems with the record. In most cases, you will want to go back to the .csv file, correct the data, and re-import the file.

Import Additional Buy-From and AP Locations

On a vendor import, you can add multiple buy-from and AP Locations for a vendor.

To add multiple buy-from or AP locations:

1. On the import spreadsheet, create a record that adds a vendor with the initial buy-from location and AP location.
   The settings (from Table 3) are:
   
   

   To do this:	Use Field Settings:
   Add an MM/AP vendor, AP location, and buy-from location at the same time	RecordType 1, VendorType 3, DefaultBFLN 1(if applicable), DefaultAPLYN 1 (if applicable).

   
   Use the "A" action code (Figure 5).
2. Create additional records for the same vendor that contain each buy-from and AP location being added (Figure 5).
   Use the settings for fields (below). These settings are outlined in Table 3.
   
   

   To do this:	Use Field Settings:
   Add an AP location only	RecordType 2, VendorType blank, DefaultAPLYN 1 (if applicable).
   Add a buy-from location only	RecordType 3, VendorType blank, DefaultBFLYN 1 (if applicable), DftInvoicingExtAPVendor, DftInvoicingAPLoc.

   
   
3. Upload the spreadsheet as normal. Figure 5 is an example. You can also view a sample file for adding a vendor and multiple buy-from and AP locations.

Figure 5- Adding Buy-From and AP Locations on a Vendor Import Spreadsheet

Copyright © 2023 by Premier Inc. All rights reserved.