Skip to main content
Avalara Help Center

Guidelines for using the import transactions template

Importing transactions to Managed Returns for Accountants involves filling out a template to create the import file. Keep these guidelines in mind when filling out the template:  

  • Use this article to determine which columns you need to fill out as well as how to fill them out.
  • Pay attention to the process code (Column A), which determines how the transaction import is processed and what other information is necessary. 
  • Don't exceed an entry's character limit.  
  • Before you import a large number of transactions, try importing a few transactions to get familiar with how it works. 
  • An import file is limited to 100,000 lines, and a single DocCode can't exceed 15,000 line items.

Quick guide

Refer to this table for a quick guide about the field requirements for each process guide. The next sections include more information about each field.

  • Y - Required
  • O - Optional
  • N - Not needed
     
    Void transaction Tax override
(new transaction without tax calculation)
Tax override (adjust existing transaction without tax calculation) New transaction Adjust existing transaction Accrued consumer use tax override Accrued consumer use tax override (adjust existing transaction)
Column Name ProcessCode 0 ProcessCode 1 ProcessCode 2 ProcessCode 3 ProcessCode 4 ProcessCode 5 ProcessCode 6
B DocCode Y Y Y Y Y Y Y
C DocType Y Y Y Y Y Y Y
D DocDate Y Y Y Y Y Y Y
E CompanyCode Y Y Y Y Y Y Y
F CustomerCode Y Y Y Y Y Y Y
H LineNo Y Y Y Y Y Y Y
I TaxCode Y Y Y Y Y Y Y
J TaxDate Y Y Y Y Y Y Y
K ItemCode Y Y Y Y Y Y Y
L Description Y Y Y Y Y Y Y
M Qty Y Y Y Y Y Y Y
N Amount Y Y Y Y Y N N
T DestAddress O O O O O O O
U DestCity O O O O O O O
V DestRegion Y Y Y Y Y Y Y
W DestPostalCode Y Y Y Y Y Y Y
X DestCountry Y Y Y Y Y Y Y
Y OrigAddress O O O O O O O
Z OrigCity O O O O O O O
AA OrigRegion Y Y Y Y Y Y Y
AB OrigPostalCode Y Y Y Y Y Y Y
AC OrigCountry Y Y Y Y Y Y Y
AD LocationCode O O O O O O O
AP TotalTax Y Y Y N N Y Y

Required fields for all transactions

Column Name What it means Possible values (character count) Notes
A ProcessCode The process to apply to a transaction document, represented by a one-digit code.
  • 0 = Void transaction
  • 1 = Tax override (new transaction without tax calculation)
  • 2 = Tax override (adjusted transaction without tax calculation)
  • 3 = New transaction
  • 4 = Adjust existing transaction
  • 5 = Accrued consumer use tax override (new transaction) 
  • 6 = Accrued consumer use tax override (adjusted transaction)
ProcessCode 0 voids the values in DocCode, DocType, and CompanyCode for the transaction. No other column values are required.

If you select ProcessCode 1, 2, 9, or 10, enter the tax amount in the TotalTax column (AP). ProcessCodes 2 and 9 create a new transaction if an existing transaction doesn't exist. These codes can’t be used to add consumer use tax to returns—use ProcessCode 5 or 6 instead.

For ProcessCodes 5 and 6, to override accrued consumer use tax, enter the accrued tax amount in the TotalTax column (AP) and enter 0 in the Amount column (N).

B DocCode The unique ID for the invoice, credit memo, or return An alphanumeric code (50) A DocCode must be unique within its DocType for all a company's transactions under this company. Document codes with the same DocType must be contiguous.

Overwrite previous transactions with the same DocCode by using a ProcessCode of 2, 4, or 6 (column A).
C DocType The type of document as represented by a one-digit code
  • 0 = Sales order
  • 1 = Sales invoice
  • 2 = Purchase order
  • 3 = Purchase invoice
  • 4 = Return order
  • 5 = Return invoice
  • 6 = Inventory transfer order
  • 7 = Inventory transfer invoice
Order documents (codes 0, 2, 4, and 6) aren't saved to Managed Returns for Accountants and don't post as committed transactions. 
D DocDate The invoice, credit memo, or return date A date in the format yyyy-mm-dd or mm/dd/yyyy. Use the date format that most resembles the one in your business application.
E CompanyCode The company code used by Managed Returns for Accountants

 

An alphanumeric code (25) This tells Managed Returns for Accountants which company you want the transaction to go to. This is important if you're importing transactions to multiple companies.
F CustomerCode The code your business application uses to identify a customer An alphanumeric code (50) In business application, a CustomerCode may be called a customer ID, customer number, or something similar.
H LineNo The transaction's line number Text (50) This value may be different than the line number in the transactions import template.

It's recommended you number lines sequentially; any lines with the same DocCode won't import unless they're in order.
I TaxCode The Avalara tax code associated with the item or SKU in the transaction An alphanumeric code (25) Use a tax code for any item with special taxability. Otherwise, the line item calculates as regularly taxable. 
J TaxDate Overrides the date used for sales tax calculation.

 

A date in the format yyyy-mm-dd or mm/dd/yyyy Managed Returns for Accountants normally uses DocDate (column D) for sales tax calculations.

The TaxDate override functionality is typically used on return invoice (credit memo) transactions requiring both a tax calculation date reflecting the original invoice date and a transaction date reflecting the current reporting period.
K ItemCode The item code or SKU that identifies the product, service, or charge An alphanumeric code (50) This is generally the item code from your business application.
L Description A description of the line item Text (255)  
M Qty The quantity of items included on the document line. A number

Fractional numbers to four decimal places are accepted for Qty (e.g. 2.5678). The default for Qty is 1.

Qty is not used as a multiplier for the amount field. Qty is used to calculate the per-item sales amount (unit price) for sales tax caps and thresholds.

N Amount The total sale for the line item A number

This field is required when you use the following ProcessCodes (column A):

  • 1 = Tax override (new transaction without tax calculation)
  • 2 = Tax override (adjusted transaction without tax calculation)
  • 3 = New transaction
  • 4 = Adjust existing transaction

For return invoices (DocType 5), use a negative number.

V DestRegion The destination or ship-to state or province's abbreviation A state or province abbreviation

 

W DestPostCode The destination or ship-to postal code
  • A ZIP code (5)
  • A ZIP + 4 code (9)
  • A Canadian postal code (6)
Excel removes leading zeros. If your transactions include postal codes that have them:
  • Add a ' before the code (for example, '08403).
  • Format this column as text and save the file as an Excel document instead of a .csv. 
AA OrigRegion The origin or ship-from state or province's abbreviation A state or province abbreviation (2)  
AB OrigPostCode The origin or ship-from state or province's postal code
  • ZIP code (5)
  • A ZIP + 4 code (9)
  • A Canadian postal code (6)
Excel removes leading zeros from postal codes. If your transactions include postal codes that have them:
  • Add a ' before the code (for example, '08403).
  • Format this column as text and save the file as an Excel document instead of a .csv. 
AP TotalTax The total tax calculated by Managed Returns for Accountants. A number

This field is required when you use the following ProcessCodes (column A):

  • 1 = Tax override (new transaction without tax calculation)
  • 2 = Tax override (adjusted transaction without tax calculation)
  • 5 = Accrued consumer use tax override (new transaction) 
  • 6 = Accrued consumer use tax override (adjusted transaction)

For return invoices (DocType 5), use a negative number.

To distribute a lump sum tax amount across multiple lines, specify TotalTax on the first line and leave TotalTax blank for the remaining lines. If specifying tax per line, you must use 0 to indicate no tax on a line rather than leaving TotalTax blank.

Optional fields

Column Name What it means Possible values (character count) Notes
G EntityUseCode The entity use code that triggers customer-level tax rules Text (25)
 
  • A = Federal government (United States)
  • B = State government (United States)
  • C = Tribe/status Indian/Indian band (both)
  • D = Foreign diplomat (both)
  • E = Charitable (both)
  • F = Religious
  • G = Resale (both)
  • H = Commercial agricultural production (both)
  • I = Industrial production / manufacturer (both)
  • J = Direct pay permit (United States)
  • K = Direct mail (United States)
  • L = Other (both)
  • M = Educational (both)
  • N = Local government (United States)
  • P = Commercial aquaculture (Canada)
  • Q = Commercial fishery (Canada)
  • R = Non-resident (Canada)
  • MED1 = US MDET with exempt sales tax
  • MED2 = US MDET with taxable sales tax
Entity use codes are commonly used for exempt customers.

Entity use codes for the United States and Canada are different, so the applicable country is shown beside the code.
O Discount

The trade discount allocated to the document line. Discounts decrease the taxable or non-taxable amount. This is a discount amount, not a percentage.

 

A number

The sign for the discount must match the line amount. If the line amount is positive, the discount must be a positive number. If the line amount is negative, the discount must be a negative number.

Managed Returns for Accountants handles discounts at the document header level, but it imports transaction discounts at the line level. While importing transactions, Managed Returns for Accountants adds together all line item discounts to create a total discount for the transaction, and then splits the total discount between all discounted line items. Tax is calculated based on the new amount for each discounted line item.
P Ref1 A user-defined reference. Text (50)  
Q Ref2 A user-defined reference. Text (50)  
R ExemptionNo The exemption certificate number, if applicable. Text (25)

ExemptionNo generates a sales tax exemption for the document line item to which the exemption certificate is applied.

S RevAcct The revenue account for this transaction. Text (50) RevAcct is a user-defined reference that identifies a general ledger account.
T DestAddress The destination or ship-to street address Text or a LocationCode (50) Entering a LocationCode in this column identifies an address associated with a location in Managed Returns for Accountants.

If you do this, leave columns U, V, W, and X blank.
U DestCity The destination or ship-to city A city name (50) If you enter something in LocationCode (column AD), leave DestCity blank. If you don't, use this field to ensure the most accurate tax. 
X DestCountry The destination or ship-to country's abbreviation A country abbreviation (2)  
Y OrigAddress The origin or ship-from street address. Text or a location code (50) Enter a LocationCode in this column to identify an address set up in locations in Managed Returns for Accountants. When importing LocationCode, leave OrigCity, OrigRegion, OrigPostalCode, and OrigCountry blank.
Z OrigCity The origin or ship-from city Text (35) This value is only used when PostalCode isn't included.
AC OrigCountry The origin or ship-from country's abbreviation A country abbreviation (2)  
AD LocationCode

The sales location (outlet) for the document line.

Text (50) If you use an online marketplace like Amazon, Etsy, or Walmart, enter the location code you set up to keep track of these sales. This code is most likely the marketplace name.
AE SalesPersonCode The salesperson for the document line Text (25)  
AF PurchaseOrderNo The purchase order for the document line Text (50)  
AG CurrencyCode The ISO currency code. Text (30) Defaults to USD.
AH ExchangeRate The conversion rate from CurrencyCode to the company base currency A number For reference only. The default value is 1.0.
AI ExchangeRateEffDate The effective date of the exchange rate. A date in the format yyyy-mm-dd or mm/dd/yyyy The default value for ExchangeRateEffDate is the DocDate (column D) value.
AJ PaymentDate Reserved for future use. A date in the format yyyy-mm-dd or mm/dd/yyyy  
AK TaxIncluded Whether tax is included in the amount in column N.
  • 0 or blank = Tax not included in Amount
  • 1 = Tax included in Amount
Managed Returns for Accountants calculates the taxable (column AN) and total tax (column AP) amounts from the TaxIncluded value.
AL DestTaxRegion Either the four-character signature code or up to a 10 character TaxRegionId value A number (10)  
AM OrigTaxRegion Either the four-character signature code or a TaxRegionId value A number (10) Reserved for future use.
BW ReferenceCode Used to track merchant code for mobility reporting or for user defined use. Text (50)  
BY IsSellerImporterOfRecord

Whether the seller is importer of record 

True or False On a Mac, the import template may tell you to type 0 or 1. Enter True or False instead.
BZ BRBuyerType

The type of buyer.

Text Leave this field blank.
CA BRBuyer_IsExemptOrCannotWH_IRRF

Whether the buyer is exempt from withholding of IRRF (Imposto de Renda na Fonte) 

True or False Leave this field blank.
CB BRBuyer_IsExemptOrCannotWH_PISRF

Whether the buyer is exempt from withholding of PIS.

True or False Leave this field blank.
CC BRBuyer_IsExemptOrCannotWH_COFINSRF

Whether the buyer is exempt from withholding of COFINS

True or False Leave this field blank.
CD BRBuyer_IsExemptOrCannotWH_CSLLRF

Whether the buyer is exempt from withholding of CSLL

True or False Leave this field blank.
CE BRBuyer_IsExempt_PIS

Whether the buyer is exempt from taxation and withholding of PIS

True or False Leave this field blank.
CF BRBuyer_IsExempt_COFINS

Whether the buyer is exempt from taxation and withholding of COFINS

True or False Leave this field blank.
CG BRBuyer_IsExempt_CSLL

Whether the buyer is exempt from taxation and withholding of CSLL

True or False Leave this field blank.
CH Header_Description The sale description that will be displayed in the Service Invoice for tax in Brazil. (Discriminação do Serviço) Text (5) Leave this field blank.
CI Email Email address for the buyer Text The city tax authority may use this email to send the buyer the invoice confirmation directly.
  • Was this article helpful?