Guidelines for using the import transactions template
This article applies to:Avalara Returns for Accountants
Importing transactions to 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 40,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
| 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. |
|
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 |
|
Order documents (codes 0, 2, 4, and 6) aren't saved to AvaTax or 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 Returns for Accountants
|
An alphanumeric code (25) |
This tells Returns for Accountants which company you want the transaction to go to. This is important if you're importing transactions to multiple companies. To find the company code:
|
| 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 | 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):
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 |
|
Excel removes leading zeros. If your transactions include postal codes that have them:
|
| 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 |
|
Excel removes leading zeros from postal codes. If your transactions include postal codes that have them:
|
| AP * | TotalTax | The total tax calculated by Returns for Accountants. | A number |
This field is required when you use the following ProcessCodes (column A):
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. |
* Column AP isn't required if you're using process code 3 or 4.
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)
|
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. |
Returns for Accountants handles discounts at the document header level, but it imports transaction discounts at the line level. While importing transactions, 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 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 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. |
|
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 address for the buyer | Text | The city tax authority may use this email to send the buyer the invoice confirmation directly. |