Import transactions template guidelines
This article applies to:AvaTax
Importing transactions to AvaTax 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.
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. SST sellers should use only codes 3, 4, or 0. |
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) 9 = Out of harbor tax override (new transaction without tax calculation) 10 = Out of harbor tax override (adjusted transaction without tax calculation) |
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 AvaTax 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. |
| 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. |
| N | Amount | The total sale for the line item | A number | For return invoices (DocType 5), use a negative number. |
Required fields for U.S. and Canadian transactions
| Column | Name | What it means | Possible values (character count) | Notes |
|---|---|---|---|---|
| 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:
|
Required fields for international transactions
| Column | Name | What it means | Possible values (character count) | Notes |
|---|---|---|---|---|
| X | DestCountry | The destination or ship-to country's abbreviation | A country abbreviation (2) | |
| AC | OrigCountry | The origin or ship-from country's abbreviation | A country abbreviation (2) |
Required fields for Streamlined Sales Tax (SST)
If your company participates in the SST program, a transaction must have these five additional values.
| Even if you don't participate in SST, it may be useful to include these values in your transactions. |
| Column | Name | What it means | Possible values (character count) | Notes |
|---|---|---|---|---|
| I | TaxCode | The AvaTax tax code or customer 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. Enter the SST certified tax code in this field unless it's mapped to the item code in AvaTax. Otherwise, the transaction line defaults to tax. |
| K | ItemCode | The item code or SKU that identifies the product, service, or charge | An alphanumeric code (50) | If an ItemCode is mapped to an AvaTax tax code or custom tax code, any custom tax rules are triggered automatically. |
| L | Description | A description of the line item | Text (255) | |
| 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 AvaTax. 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. |
| 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:
|
| X | DestCountry | The destination or ship-to country's abbreviation | A country abbreviation (2) | Required outside the U.S. or Canada. |
Optional fields
| Column | Name | What it means | Possible values (character count) | Notes |
|---|---|---|---|---|
| E | CompanyCode | The company code used by AvaTax
|
An alphanumeric code (25) | This tells AvaTax which company you want the transaction to go to. This is important if you're importing transactions to multiple companies. If CompanyCode isn't specified, AvaTax applies the company code for the company you're in when doing the import. |
| G | EntityUseCode | The entity use code that triggers customer-level tax rules | Text (25)
|
Entity use codes are commonly used for exempt customers. Custom codes are supported, as well as the following AvaTax standard codes. Entity use codes for the United States and Canada are different, so the applicable country is shown beside the code. SST sellers: You must use Exemptions or CertCapture to capture exemption certificate information and exempt a transaction.You can't use these codes. Ensure the exemption certificate information is in AvaTax before you import this document. |
| J | TaxDate | Overrides the date used for sales tax calculation.
|
A date in the format yyyy-mm-dd or mm/dd/yyyy | AvaTax 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. |
| 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. |
| 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. |
AvaTax handles discounts at the document header level, but it imports transaction discounts at the line level. While importing transactions, AvaTax 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. The ExemptionNo is not required unless:
You must enter exemption certificate data into AvaTax before uploading this file for accurate tax calculation. |
| S | RevAcct | The revenue account for this transaction. | Text (50) | RevAcct is a user-defined reference that identifies a general ledger account. |
| 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 AvaTax. 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. |
| 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) | Can be used to match against single-use exemption certificate entries created in Exemptions. |
| 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 |
AvaTax 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. |
| AP | TotalTax | The total tax calculated by AvaTax. | A number |
For ProcessCode (column A) 1 or 2, use the tax amount desired. For ProcessCode (column A) 0, 3, or 4, the value is Not used. (Required for SST state calculations.) 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. |
| 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 | Possible values include individual, private business, and government entity. |
| CA | BRBuyer_IsExemptOrCannotWH_IRRF |
Whether the buyer is exempt from withholding of IRRF (Imposto de Renda na Fonte) |
True or False | If true, AvaTax won't calculate IRRF withholding on this transaction. |
| CB | BRBuyer_IsExemptOrCannotWH_PISRF |
Whether the buyer is exempt from withholding of PIS. |
True or False | If true, AvaTax won't calculate PISRF withholding on this transaction. |
| CC | BRBuyer_IsExemptOrCannotWH_COFINSRF |
Whether the buyer is exempt from withholding of COFINS |
True or False | If true, AvaTax won't calculate COFINSRF withholding on this transaction. |
| CD | BRBuyer_IsExemptOrCannotWH_CSLLRF |
Whether the buyer is exempt from withholding of CSLL |
True or False | If true, AvaTax won't calculate CSLLRF withholding on this transaction. |
| CE | BRBuyer_IsExempt_PIS |
Whether the buyer is exempt from taxation and withholding of PIS |
True or False | If true, AvaTax won't accrue PIS payable and won't calculate PIS withholding on this transaction. |
| CF | BRBuyer_IsExempt_COFINS |
Whether the buyer is exempt from taxation and withholding of COFINS |
True or False | If true, AvaTax won't accrue COFINS payable and won't calculate COFINS withholding on this transaction |
| CG | BRBuyer_IsExempt_CSLL |
Whether the buyer is exempt from taxation and withholding of CSLL |
True or False | If true, AvaTax won't accrue CSLL payable and won't calculate CSLL withholding on this transaction. |
| 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) | |
| CI | Email address for the buyer | Text | The city tax authority may use this email to send the buyer the invoice confirmation directly. |