Input Files and Database Tables

The Import Vendors preprocessor allows you to import vendor information either from an ASCII input file produced from PRO-III system or a database table.

This help topic contains information on how to prepare an input file or database table that Costpoint can use to import vendor records.

Note: The input file layout may contain fields that are available only in Costpoint Advanced. If you are using Costpoint Essentials, these fields are excluded from the import process and may be left blank or null when you create the input file.

ASCII Input File

The input file used by the Import Vendors preprocessor must be a standard ASCII text file without null characters. Each record to be included must be on a separate line in the input file, separated with a combination of carriage return and line feed characters (ASCII decimal characters 13 and 10).

Each record must be in a separate line in the input file. There are separate formats for vendor and vendor address records. Vendor records are identified by the V record type and will be imported to the VEND table, while Vendor address records are identified by the A record type and will be imported to the VEND_ADDR table.

When starting a line in the input file, you must first enter the record type as the first field to identify the record as a vendor or vendor address. Costpoint then imports that record to the appropriate table.

See the Vendor Input File Format (VEND) and Vendor Address Input File Format (VEND_ADDR) tables for the input file layouts expected by Costpoint.

Database Tables

You can use database tables as the input source for importing vendor or vendor address records. You must create an AOPUTLVU_INP_VEND table for vendor records and an AOPUTLVU_INP_VENDA table for vendor address records. These tables use the same format as the input file but the first field uses status codes, instead of record type. Status codes for both input table types must be set to 'U' to indicate that a record is unprocessed or has not been imported yet into Costpoint.

Input File Excel Template

For Costpoint 2025.x and later releases, you can download the Excel template file specifically designed for this preprocessor from the Costpoint Preprocessor Guides page on the Deltek Learning Hub.

For Costpoint 8.1, the input file is available on the Developer Resources page in the Costpoint Information Center.

Excel templates for the other Costpoint preprocessors can be found in these same locations.

Vendor Input File Format (VEND)

Use this input file layout to enter a vendor record (record type 'V'). The following table lists the entries contained in each record of the vendor input file.

For Costpoint Essentials users, the following columns are processed by default, regardless of the values you entered in the input file:

Vendor Group Code (VEND_GRP_CD): The value of this column is ignored and not entered in the database.
NON-US EFT Active (NON_US_ACTIVE_FL): The value of this column is set to N by default.
NON-US Bank ID (NON_US_BANK_ID): The value of this column is ignored and not entered in the database.
Enable Supplier Portal: (SUPPLIER_PORTAL_FL): The value of this column is set to N by default.

Seq. No.	Column Name	Costpoint Table/Column	Type	Length	Required or Optional	Format
1	Record Type/ Status Code	N/A	Character	1	Required	Must be Vfor Vendor.
2	Vendor ID	VEND_ID	Alphanumeric	12	Required
3	Vendor Approval Code	VEND_APPRVL_FL	Character	1	Optional	Enter Y (Yes) or N (No).
4	Vendor Name	VEND_NAME	Alphanumeric	25	Required
5	Vendor Long Name	VEND_LONG_NAME	Alphanumeric	40	Optional
6	Vendor Terms	TERMS_DC	Alphanumeric	15	Optional
7	Location	VEND_NAME_EXT	Alphanumeric	6	Optional
8	Hold Payment Flag	HOLD_PMT_FL	Character	1	Optional	Enter Y (Yes) or N (No).
9	1099 Tax ID	AP_1099_TAX_ID	Alphanumeric	20	Optional
10	1099 Type Code	S_AP_1099_TYPE_CD	Alphanumeric	6	Optional
11	Print 1099	PRNT_1099_FL	Character	1	Optional	Enter Y (Yes) or N (No).
12	Allow Auto Voucher	AUTO_VCHR_FL	Character	1	Optional	Enter Y (Yes) or N (No).
13	A/P Account Description	AP_ACCTS_KEY	Alphanumeric	30	Optional
14	Cash Account Description	CASH_ACCTS_KEY	Alphanumeric	30	Optional
15	Vendor Notes	VEND_NOTES	Alphanumeric	254	Optional
16	Vendor Group Code	VEND_GRP_CD	Alphanumeic	6	Optional
17	Pay Vendor	AP_CHK_VEND_ID	Alphanumeric	12	Optional
18	Allow Edits to Pay Vendor	ED_VCH_PAY_VEND_FL	Character	1	Optional	Enter Y (Yes) or N (No).
19	Pay When Paid	PAY_WHEN_PAID_FL	Character	1	Optional	Enter Y (Yes) or N (No).
20	Separate Check	SEP_CHK_FL	Character	1	Optional	Enter Y (Yes) or N (No).
21	Vendor Status for PO	S_VEND_PO_CNTL_CD	Character	1	Optional
22	Business Size Class Code	S_CL_SM_BUS_CD	Character	1	Optional	Enter S (Small) or L (Large).
23	Woman Owned	CL_WOM_OWN_FL	Character	1	Optional	Enter Y (Yes) or N (No).
24	Disadvantaged	CL_DISADV_FL	Character	1	Optional	Enter Y (Yes) or N (No).
25	HUB Zone	CL_LAB_SRPL_FL	Character	1	Optional	Enter Y (Yes) or N (No).
26	Historic Black College	CL_HIST_BL_CLG_FL	Character	1	Optional	Enter Y (Yes) or N (No).
27	Veteran Owned	CL_VET_FL	Character	1	Optional	Enter Y (Yes) or N (No).
28	Service Disabled Veteran Owned	CL_SD_VET_FL	Character	1	Optional	Enter Y (Yes) or N (No).
29	Blank Laser Check Memo	CHK_MEMO_S	Alphanumeric	25	Optional
30	Customer Account	CUST_ACCT_FLD	Alphanumeric	20	Optional
31	Employee ID	EMPL_ID	Alphanumeric	12	Optional
32	ANC and Indian Tribes	CL_ANC_IT_FL	Character	1	Optional	Enter Y (Yes) or N (No).
33	Certification Date	VEND_CERT_DT	Date	10	Optional
34	Certification Number		Number	20	Optional
35	eProcurement Vendor	EPROCURE_FL	Character	1	Optional	Enter Y (Yes) or N (No).
36	FOB	FOB_FLD	Alphanumeric	15	Optional
37	Ship Via	SHIP_VIA_FLD	Alphanumeric	15	Optional
38	Payroll Vendor	PR_VEND_FL	Character	1	Optional	Enter Y (Yes) or N (No).
39	Time Collection Expense Class		Alphanumeric	20	Optional
40	Vendor 1099 Name	VEND_1099_NAME	Alphanumeric	40	Optional
41	SM Subcontractor Flag	SM_SUBCTR_FL	Character	1	Optional	Y (Yes) or N (No). For use if licensed for Subcontractor Management. If null, this will be set to N.
42	Vendor Employee Approval Group Code	VE_APPRVL_GRP_CD	Character	6	Optional	For use if: SM license is ON or available and SM Subcontractor Flag = Yand Vendor Employee Requires Approval = Yin AP_SETTINGS table and Use Vendor Employee Approval Groups = Yin AP_SETTINGS table This is set to NULL if: Subcontractor Management (SM) license is OFF or SM license is ON and SM Subcontractor Flag = Nor SM license is ON and SM Subcontractor Flag = Yand Vendor Employee Requires Approval = Nin AP_SETTINGS table or SM license is ON and SM Subcontractor Flag = Yand Vendor Employee Requires Approval = Yin AP_SETTINGS table and Use Vendor Employee Approval Groups = Nin AP_SETTINGS table.
43	DUNS Number	DUNS_NO	Alphanumeric	15	Optional
44	CAGE Code	CAGE_CD	Alphanumeric	15	Optional
45	8(a) Certified	CL_8A_FL	Character	1	Optional	Enter Y (Yes) or N (No).
46	AbilityOne Non-Profit Agency	CL_ABIL_ONE_FL	Character	1	Optional	Enter Y (Yes) or N (No).
47	GovWin IQ Company ID	GOVWIN_COMP_ID	Alphanumeric	15	Optional
48	Vendor Web Site	VEND_WEB_SITE	Alphanumeric	1024	Optional
49	UEI Number	UEI_NO	Alphanumeric	12	Optional
50	Enable Digital Signature	DIGITAL_SIG_FL	Character	1	Optional	Enter Y (Yes) or N (No).
51	Enable Supplier Portal	SUPPLIER_PORTAL_FL	Character	1	Optional	Enter Y (Yes) or N (No).
52	Intracompany Vendor	IC_VEND_FL	Character	1	Optional	Enter Y (Yes) or N (No).
53	Performing Company	PERF_COMPANY_ID	Alphanumeric	10	Required if you enter Yin the Intracompany column. Otherwise, optional.
54	LGBTQ+ Owned	CL_LGBTQ_FL	Character	1	Optional	Enter Y (Yes) or N (No).
55	1099 State	AP_1099_STATE_CD	Alphanumeric	2	Optional	The value you enter in this field must match an existing state code in Costpoint. Refer to the States and Codes special topic to view valid state codes.
56	Recipient TIN Type	S_1099_RECPT_TIN_TYPE	Alphanumeric	10	Optional	E: EIN Q: QI-EIN S: SSN I: ITIN A: ATIN U: UND
57	Recipient First Name	S_1099_RECPT_FNAME	Alphanumeric	20	Required if Recipient TIN Type is S, I, or A.
58	Recipient Middle Name	S_1099_RECPT_MNAME	Alphanumeric	20	Optional
59	Recipient Last Name	S_1099_RECPT_LNAME	Alphanumeric	20	Required if Recipient TIN Type is S, I, or A.
60	Recipient Suffix	S_1099_RECPT_SUFFIX	Alphanumeric	20	Optional

Vendor Address Input File Format (VEND_ADDR)

Use this input file layout to enter a vendor address record (record type 'A'). The following table lists the entries contained in each record of the vendor address input file.

Seq. No.	Column Name	Costpoint Table/Column	Type	Length	Required or Optional	Format
1	Record Type/ Status Code	N/A	Character	1	Required	Must be Afor Vendor Address.
2	Vendor ID	VEND_ID	Alphanumeric	12	Required
3	Address Code	ADDR_DC	Alphanumeric	10	Required
4	US EFT Active	ACTIVE_FL	Character	1	Optional	Enter Y (Yes) or N (No).
5	Order Address Code	S_ORD_ADDR_CD	Character	1	Optional
6	Payment Address Code	S_PMT_ADDR_CD	Character	1	Required
7	Address Line 1	LN_1_ADR	Alphanumeric	40	Optional
8	Address Line 2	LN_2_ADR	Alphanumeric	40	Optional
9	Address Line 3	LN_3_ADR	Alphanumeric	40	Optional
10	City Name	CITY_NAME	Alphanumeric	25	Optional
11	Payment State	MAIL_STATE_DC	Alphanumeric	15	Optional
12	Payment Country	COUNTRY_CD	Alphanumeric	8	Optional
13	Postal Code	POSTAL_CD	Alphanumeric	10	Optional
14	Phone ID	PHONE_ID	Number	25	Optional
15	Other Phone ID	OTH_PHONE_ID	Number	25	Optional
16	Fax No.	FAX_ID	Number	25	Required
17	Email	EMAIL_ID	Alphanumeric	100	Required
18	EFT Payment	EFT_PMT_CD	Character	6	Optional
19	Bank ID. (ABA No.)	BANK_ABA_NO	Alphanumeric	9	Required if you enter Yin the US EFT Active column. Otherwise, optional.
20	Bank Account	BANK_ACCT_ID_S	Number	17	Required if you enter Yin the US EFT Active column and the Non-US Bank Account column is blank. Otherwise, optional.
21	ACH Code	S_ACH_TRN_CD	Alphanumeric	2	Optional
22	Non-US Bank Account	NON_US_BANK_ACCT_ID	Alphanumeric	25	Optional
23	Print EFT Advice	PRINT_EFT_FL	Character	1	Optional	Enter Y (Yes) or N (No).
24	Originator ID Code	EDI_N104_CD	Alphanumeric	80	Optional
25	Bank Reference	BANK_ACCT_REF_CD	Alphanumeric	18	Optional
26	IBAN Code	IBAN_CD	Alphanumeric	34	Optional
27	Ship ID	SHIP_ID	Alphanumeric	20	Optional
28	Sales Tax Code	SALES_TAX_CD	Alphanumeric	6	Optional
29	Intermediary Bank ID	IB_BANK_ID	Alphanumeric	15	Optional
30	SWIFT Code	SWIFT_CD	Alphanumeric	11	Optional
31	NON-US EFT Active?	NON_US_ACTIVE_FL	Character	1	Optional
32	NON-US Bank ID	NON_US_BANK_ID	Alphanumeric	34	Optional
33	Email EFT Advice	EMAIL_EFT_FL	Character	1	Optional	Enter Y (Yes) or N (No)
34	UEI Number	UEI_NO	Alphanumeric	12	Optional