Customer CSV Upload Format

Accounts Receivable > Accounts Receivable Upload Administration Functions > Customer CSV Upload

Customer CSV Upload Format

The default system provides a template—Customer Upload Template CSV—you may want to use to create the customer upload file. You must save your file in .csv format.

Whether you use the template or not, the system expects each record to contain data in the order shown below.

The CSV upload function requires a customerName entry in each record. If any entry is blank data, the system will exclude the record and complete the file.

Customer CSV Upload format

Column	Name	Type MaxSize	Reqd?	Special Formatting if applicable
A	customerNumber	Characters 9	Yes*	Please refer to section 3.1.3
B	customerNumberExternalID	Characters 9	Yes*	Please refer to section 3.1.3
C	customerSourceCode	Characters 4	Yes*	Please refer to section 3.1.3
D	customerName	Characters 40	Please refer to section 3.1.3
E	customerFirstName	Characters 20	No
F	customerLastName	Characters 20	No
G	customerAlias	Characters 45	No
H	customerAliasActiveIndicator	Characters 1	No	Y or N
I	customerParentCompanyNumber	Characters 9	No
J	customerTypeCode*	Characters 2	Yes	Has to be 2 digits, for example, "01" (cannot be just "1"). Please refer to “AR-Customer-BatchUpload-Valid-Values.xlsx” for all possible values
K	customerLastActivityDate	Date 10	No	YYYY-MM-DD
L	customerTaxTypeCode*	Characters 4	No	Please refer to “AR-Customer-BatchUpload-Valid-Values.xlsx” for all possible values
M	customerTaxNbr*	Numeric 9	No
N	customerActiveIndicator*	Characters 1	No	Y or N
O	customerPhoneNumber	Characters 20	No	###-###-#### or ###-###-#### Ext.###
P	customer800PhoneNumber	Characters 20	No	###-###-#### or ###-###-#### Ext.###
Q	customerContactName	Characters 40	No
R	customerContactPhoneNumber	Characters 40	No	###-###-#### or ###-###-#### Ext.###
S	customerFaxNumber	Characters 20	No	###-###-#### or ###-###-#### Ext.###
T	customerBirthDate	Date 10	No	YYYY-MM-DD
U	customerTaxExemptIndicator	Characters 1	No	Y or N
V	customerCreditLimitAmount	Decimal 19	No	Money format, 2 decimal places, no negative
W	customerCreditApprovedByName	Characters 40	No
X	customerEmailAddress	Characters 40	No
Y	customerAddressName*	Characters 40	Yes
Z	customerLine1StreetAddress	Characters 30	Yes
AA	customerLine2StreetAddress	Characters 30	No
AB	customerCityName	Characters 25	Yes
AC	customerStateCode	Characters 2	If country is US then Yes, otherwise No	Please refer to “AR-Customer-BatchUpload-Valid-Values.xlsx” for all possible values
AD	customerZipCode	Characters 20	If country is US then Yes, otherwise No	##### or #####-####. Please refer to “AR-Customer-BatchUpload-Valid-Values.xlsx” for all possible values
AE	customerAddressTypeCode	Characters 2	Yes	Alpha Numeric. Please refer to “AR-Customer-BatchUpload-Valid-Values.xlsx” for all possible values
AF	customerAddressInternationalProvinceName	Characters 45	No
AG	customerInternationalMailCode	Characters 20	No
AH	customerAddressEmail	Characters 60	No
AI	customerAddressTypeCode	Characters 2	Yes	A, P, T. Please refer to “AR-Customer-BatchUpload-Valid-Values.xlsx” for all possible values

*Indicates a required field.

Data dictionary rules

1. customerAddressTypeCode : Each customer must have one address type code of "P" (primary). The customer can also have other address type codes as long as there is one “P” address type code.

2. If the country is not US, then both customerAddressInternationalProvinceName and customerInternationalMailCode are required.

3. If the country is US, then both the customerStateCode and customerZipCode are required.

4. If the customerName field is present, then customerFirstName and customerLastName field must be left blank. Likewise, if the customerFirstName and customerLastName are present, then customerName must be blank. If customerFirstName is entered then customerLastName is required. If customerLastName is entered then customerFirstName is required.

5. customerAlias : A customer can have multiple aliases but the CSV file can only accept one alias at a time (in a row). customerAlias can be used to search for a customer in the customer search screen.

6. customerAliasActiveIndicator is not required. If it is not set and there is a value for customerAlias, then by default KFS will set the customerAliasActiveIndicator to N (i.e. it is inactive). If the customerAliasActiveIndicator is N and a user tries to use the corresponding customerAlias in the customer search screen in KFS, it will not return any results even if there is a matching alias.

7. customerNumber is a unique ID generated by KFS for a customer. It will take precedence by the customer upload batch job to be used to identify a customer. If the external department provides only customerNumberExternalID and customerSourceCode, but no customerNumber in the csv file, then KFS will use the customerNumberExternalID and customerSourceCode to identify the customer. If the external department provides customerNumber as well as the combination of customerNumberExternalID and customerSourceCode, then the customerNumber will be used as the identifier. If the customerNumber is provided in the csv file, but does not exist in KFS, then that particular record will error out. If the customerNumber exists and the customerNumberExternalID and customerSourceCode are also provided but the customerNumberExternalID does not exist, KFS will update the customerNumberExternalID for that customer whose customerNumber matches what the csv file provided.

8. customerNumberExternalID is a unique ID generated by the source system (e.g. SAP) for a customer and must be included in the CSV file. The first row encountered by KFS will be treated as a new customer. Subsequent rows with the same customerNumberExternalID will be treated as an edit to an existing customer.

9. customerSourceCode : Sample values for this are tentatively “CTRL” and “HRIT”.

10. To deactivate a Customer address, the Address End Date field must be set and must be a current or future date. There must be one primary address. If there are no other primary addresses for a Customer, the Address End Date may not be set to today.

11. For non-required fields, an empty value will not delete previously inserted values. In other words, the CSV upload cannot be used to delete values.

12. The customerName, or the combination of customerFirstName and customerLastName is automatically converted to all capital letters regardless of the case in the CSV file.

13. The CSV upload function does not require customerName (or the combination of customerFirstName and customerLastName). If customerName (or combination of customerFirstName and customerLastName) is empty, the system treats the record as an alternate address for the preceding Customer record. Note, however, if customerName (or the combination of customerFirstName and customerLastName) cells are blank in the CSV file, the Customer loading job (which is run after the CSV upload process) will fail because it cannot create a Customer record without a name.