Accounts Receivable File Format Requirements for Importing Data

Appendix > Appendix > Format Requirements for the Import/Export Feature > Accounts Receivable File Format Requirements for Importing Data

Before you begin to import data, you need to make sure the text file is in the proper format and that all required fields have entries. If the external file is properly formatted, it will help eliminate the possibility of errors.

NOTE: If you are importing payers/payees that don’t exist in the system as customers, you should import them first. They must exist in the system as customers before you can attach them as payers/payees.

If the import file has blank payer/payee fields, the blanks will not overwrite any payers/payees attached to your customer records. If you want to clear payers/payees, you need to edit the relationship in Accounts Receivable.

Balance Forward Customers

Required fields are noted with an asterisk (*).

Field Order¹	Field Description	Maximum Length	Character Position (Fixed-Length Format)	Data Type
1	* Customer Number	50	1	Char-Caps
2	* AR Code	6	51	Char-Caps
3	Customer Type	6	57	Char-Caps
4	* Customer Name	50	63	Character
5	Billing Contact Name	35	113	Character
6	Billing Address Line 1	35	148	Character
7	Billing Address Line 2	35	183	Character
8	Billing City	35	218	Character
9	Billing State/Province	35	253	Char-Caps
10	Billing Postal Code	35	288	Character
11	Billing Country	35	323	Character
12	Billing Phone 1	20	358	Character
13	Billing Phone 1 Extension	5	378	Character
14	Billing Phone 2	20	383	Character
15	Billing Phone 2 Extension	5	403	Character
16	Billing Fax	20	408	Character
17	Email Contact	35	428	Character
18	Email Address	50	463	Character
19	Shipping Contact Name	35	513	Character
20	Shipping Address Line 1	35	548	Character
21	Shipping Address Line 2	35	583	Character
22	Shipping City	35	618	Character
23	Shipping State/Province	35	653	Char-Caps
24	Shipping Postal Code	35	688	Character
25	Shipping Country	35	723	Character
26	Salesperson Code	6	758	Char-Caps
27	Biller Code	6	764	Char-Caps
28	Tax Code	6	770	Char-Caps
29	Discount Code	6	776	Char-Caps
30	Ship Via Code	6	782	Char-Caps
31	Terms Code	6	788	Char-Caps
32	Collection Status Code	6	794	Char-Caps
33	Collection Comment	30	800	Character
34	Collection Date	10	830	Date
35	Date Created (Import date is used if field is blank)	10	840	Date
36	Credit Limit	16	850	Numeric (11.4)
37	Inactive Flag 0=Active 1=Inactive 2=Temp	1	866	Logical
38	EFT Customer Flag 1=Yes 0=No	1	867	Numeric
39	EFT Account Code	3	868	Char-Caps
40	EFT Account Description	20	871	Character
41	EFT Bank Account²	17	891	Character
42	EFT Routing Number2	9	908	Character
43	EFT Account Type 0=Checking 1=Savings	1	917	Numeric
44	Allow Checks Flag 1=Yes 0=No	1	918	Numeric
45	Allow Credit Cards Flag 1=Yes 0=No	1	919	Numeric
46	Allow AR Charges Flag 1=Yes 0=No	1	920	Numeric
47	Allow Cash Flag 1=Yes 0=No	1	921	Numeric
48	UDF 1	6	922	Character
49	UDF 2	6	928	Character
50	UDF 3	10	934	Character
51	UDF 4	10	944	Character
52	UDF 5	10	954	Date
53	UDF 6	10	964	Date
54	UDF 7	17	974	Numeric
55	UDF 8	17	991	Numeric
56	UDF 9	50	1008	Character
57	UDF 10	50	1058	Character
58	Amount Due³	18	1108	Numeric
59	Payer1⁴	50	1126	Char-Caps
60	Payer2	50	1176	Char-Caps
61	Payer3	50	1226	Char-Caps
62	Payer4	50	1276	Char-Caps
63	Payer5	50	1326	Char-Caps
64	Payee1	50	1376	Char-Caps
65	Payee2	50	1426	Char-Caps
66	Payee3	50	1476	Char-Caps
67	Payee4	50	1526	Char-Caps
68	Payee5	50	1576	Char-Caps
69	Additional Date	10	1626	Date

¹ This number is for reference purposes only and will not appear in the actual text file.

² If you exported this file from Denali, this field will only contain information if the Show bank information on exports check box is selected in Module Preferences. If this field is blank when you import the file, it will not overwrite existing account information in the database.

³ This field is only used if balance forward amounts exist.

⁴ All payer/payee fields are optional.

Non-Balance Forward Customers

Required fields are noted with an asterisk (*).

Field Order¹	Field Description	Maximum Length	Character Position (Fixed-Length Format)	Data Type
1	* Customer Number	50	1	Char-Caps
2	* AR Code	6	51	Char-Caps
3	Customer Type	6	57	Char-Caps
4	* Customer Name	50	63	Character
5	Billing Contact Name	35	113	Character
6	Billing Address Line 1	35	148	Character
7	Billing Address Line 2	35	183	Character
8	Billing City	35	218	Character
9	Billing State/Province	35	253	Char-Caps
10	Billing Postal Code	35	288	Character
11	Billing Country	35	323	Character
12	Billing Phone 1	20	358	Character
13	Billing Phone 1 Extension	5	378	Character
14	Billing Phone 2	20	383	Character
15	Billing Phone 2 Extension	5	403	Character
16	Billing Fax	20	408	Character
17	Email Contact	35	428	Character
18	Email Address	50	463	Character
19	Shipping Contact Name	35	513	Character
20	Shipping Address Line 1	35	548	Character
21	Shipping Address Line 2	35	583	Character
22	Shipping City	35	618	Character
23	Shipping State/Province	35	653	Char-Caps
24	Shipping Postal Code	35	688	Character
25	Shipping Country	35	723	Character
26	Salesperson Code	6	758	Char-Caps
27	Biller Code	6	764	Char-Caps
28	Tax Code	6	770	Char-Caps
29	Discount Code	6	776	Char-Caps
30	Ship Via Code	6	782	Char-Caps
31	Terms Code	6	788	Char-Caps
32	Collection Status Code	6	794	Char-Caps
33	Collection Comment	30	800	Character
34	Collection Date	10	830	Date
35	Date Created (Import date is used if field is blank)	10	840	Date
36	Credit Limit	16	850	Numeric (11.4)
37	Inactive Flag 0=Active 1=Inactive 2=Temp	1	866	Logical
38	EFT Customer Flag 1=Yes 0=No	1	867	Numeric
39	EFT Account Code	3	868	Char-Caps
40	EFT Account Description	20	871	Character
41	EFT Bank Account²	17	891	Character
42	EFT Routing Number2	9	908	Character
43	EFT Account Type 0=Checking 1=Savings	1	917	Numeric
44	Allow Checks Flag 1=Yes 0=No	1	918	Numeric
45	Allow Credit Cards Flag 1=Yes 0=No	1	919	Numeric
46	Allow AR Charges Flag 1=Yes 0=No	1	920	Numeric
47	Allow Cash Flag 1=Yes 0=No	1	921	Numeric
48	UDF 1	6	922	Character
49	UDF 2	6	928	Character
50	UDF 3	10	934	Character
51	UDF 4	10	944	Character
52	UDF 5	10	954	Date
53	UDF 6	10	964	Date
54	UDF 7	17	974	Numeric
55	UDF 8	17	991	Numeric
56	UDF 9	50	1008	Character
57	UDF 10	50	1058	Character
58	Payer1³	50	1108	Char-Caps
59	Payer2	50	1158	Char-Caps
60	Payer3	50	1208	Char-Caps
61	Payer4	50	1258	Char-Caps
62	Payer5	50	1308	Char-Caps
63	Payee1	50	1358	Char-Caps
64	Payee2	50	1408	Char-Caps
65	Payee3	50	1458	Char-Caps
66	Payee4	50	1508	Char-Caps
67	Payee5	50	1558	Char-Caps
68	Additional Date	10	1608	Date

¹ This number is for reference purposes only and will not appear in the actual text file.

² This field will only import or export information if the Import and Export Bank Information check box is selected in Module Preferences.

³ All payer/payee fields are optional.

Published date: 12/03/2021