How to import Invoices into QuickBooks Online with Business Importer
After reading this guide you will be able to import your Invoices into QuickBooksⓇ Online using Business Importer.
Overview:
How to import Invoices into QuickBooks Online
To import Invoices into QuickBooks Online, please follow these simple steps:
1. Sign in to Business Importer and connect it to QuickBooks.
2. Go to Upload file tab and select Invoice entity. Click on the Next button.
IMPORTANT: Multi-Lined Invoices – You can easily import multilined Invoices (with several products) into QuickBooks Online, using Business Importer. Just set the one Invoice Number for each multi-lined Invoice.
3. Press Browse file to select file from computer or you can also put the link into “Insert the link field” if you want to upload file from Online Sheets. Press Next.
4. Select sheet with invoices in your file (only in case you have several sheets) and click Next.
5. Map Provider Labels fields (1) with Your Labels from your file (2). To map fields properly you can use “Help me with mapping” button (3).
6. After mapping go to the bottom of the page and click on Next button to see the preview. Then press Start import button.
Congratulations, you have imported your Invoices successfully!
There are 5 types of import results.
The most common errors and ways how to solve them
There are 7 the most common errors made by our clients when they import Invoices into QuickBooks Online. To prevent this happening, we have reviewed all of them and given solutions below.
| Problem | Error occurs | Solution | Correct example |
|---|---|---|---|
| Wrong date format is used. | WARNING Error parsing date 01-14-2015. : Expected format currently MM/dd/yyyy, but you can change it on your profile page. | Change date format to MM/dd/yyyy. | 01/14/2015 |
| No product in the Invoice. | ERROR Invoice 41: Required parameter Line.SalesItemLineDetail is missing in the request. | Add product name to the Line: Product name | New product |
| E-mail is incorrect. | Email Address does not conform to the syntax rules of RFC 822. Supplied value:http://www.intuit.com | Verify the e-mail and change it to the correct one. | beryy@intuit.com |
| Wrong Amount | Amount is not equal to UnitPrice * Qty. Supplied value:8. | The given Quantity & Rate is not equal to given Amount. Please verify those fields. | Quantity * Rate = Amount |
| Tax name is incorrect | TaxCode with Name = “tax” is not found in your QuickBooks Online account (New Company). | Provide with correct tax name | For US: TAX or NON
For non-US: ‘S 20%’ or other real name of your Tax Code |
| Warning: Product(Service) is not found in your QuickBooks Online account. | Product(Service) with Name = “Design” is not found in your QuickBooks Online account (New Company). | Create a new product (service) in your QuickBooks Online account or use auto creation of products – read how to set it. | Design |
| Invalid Currency code | ERROR Invoice 41: Invalid or Unsupported currency type supplied. Supplied value:US. | Specify Currency code (CurrencyRef) . Choose letters according to ISO 4217. For example, USD, EUR, CAD, GBP. | USD |
Useful tips on how to work with your transactions with Business Importer
1. You can delete transactions from QuickBooks with the help of Business Importer. Check this detailed guide on the matter.
2. Another useful option of Business Importer is that you can schedule your imports to be done automatically without you even entering the app.
3. Line: Description Type – If specified line will be processed with following rules: DescriptionOnly – show only text line, Subtotal – Subtotal calculated and displayed.
4. DocNumber (Invoice number) – There are 2 options: 1. You can write your DocNumbers in your Excel file, or 2. Use our Number AutoGeneration function. In case both of the settings in Business Importer will be disabled, the app will allow QuickBooks to generate sequential number to imported invoices.
5. Products Autocreation function – Enable this function in your Business Importer account. Read how to autocreate products in your QuickBooks Online account.
6. Line: Tax code name – should include values “TAX” or “NON”. Read more about the taxes applied for US and non-US residents in corresponding guides.
Supported fields and their description in QuickBooks Online
Here is a small guide that will help you to prepare your Excel file and create correct mapping:
| QuickBooks Field | Business Importer Name | Description | Example |
|---|---|---|---|
| DocNumber | Invoice number | Reference number for the transaction. | INV-001 |
| CustomerRef | Customer name | Name of customer. Please, provide its Display name, as soon as if it is different from the one you meant, invoice will be associated with another customer. | Sonnenschein Family Store |
| AllowOnlineCreditCardPayment | Allow Online CreditCard payment | true or false, default = false | true |
| AllowOnlineACHPayment | Allow Online Bank transfer | true or false, default = false | true |
| ApplyTaxAfterDiscount | Apply tax after discount | If false or null, calculate the sales tax first, and then apply the discount. If true, subtract the discount first and then calculate the sales tax. US versions of QuickBooks only. |
true |
| LinkedTxn.TxnId | Linked Txn: Id | Id or document number of the related transaction. | 23345 |
| LinkedTxn.TxnType | Linked Txn: Type | Transaction type. Ex: Payment, Check, CashPurchase etc. | Payment |
| BillAddr (ShipAddr) | Billing address: Line 1 (Shipping address: Line 1) | First line of the address. Maximum of 500 chars. |
12 Ocean Dr. |
| Billing address: Line 2 (Shipping address: Line 2) | Second line of the address. Maximum of 500 chars. |
||
| Billing address: Line 3 (Shipping address: Line 3) | Third line of the address. Maximum of 500 chars. |
||
| Billing address: City (Shipping address: City) | City name. Maximum of 255 chars. |
Half Moon Bay | |
| Billing address: Postal code (Shipping address: Postal code) | Postal code (zip code for USA and Canada). Maximum of 31 chars. |
94213 | |
| Billing address: Country (Shipping address: Country) | Country name. Maximum of 255 chars. |
US | |
| Billing address: Region (Shipping address: Region) | Region within a country (state name for USA, province name for Canada). Maximum of 255 chars. |
CA | |
| BillEmail | Billing e-mail | Identifies the e-mail address where the invoice is sent. If EmailStatus is NeedToSend, Billing e-mail is required. |
payments@intuit.com |
| ClassRef | Class name | Reference to the Class associated with the overall transaction. Please, provide only its name. | Payments |
| CurrencyRef | Currency | Values: USD, AUD, EUR, CAD, etc. | EUR |
| ExchangeRate | Exchange Rate | Specify it in case Transfer is done not in your home currency. Decimal like 1.23 | 1.7 |
| CustomerMemo | Customer memo | User-entered message to the customer; this message is visible to end user on their transactions. | Thank you for your business and have a great day! |
| Custom Field # | Custom Field # (Custom Field name) | Custom field based on order in your QuickBooks config. | |
| GlobalTaxCalculation | Global tax calculation | Method in which tax is applied. Allowed values are: TaxExcluded, TaxInclusive, and NotApplicable | TaxExcluded |
| Deposit | Deposit | The deposit made towards this invoice. | 0 |
| DueDate | Due date | Date when the payment of the transaction is due. Default date format is dd-MM-yyyy. You can change it on Settings page. | 09/20/2015 |
| DepartmentRef | Location | Location of the transaction, as defined using location tracking in QuickBooks Online. Please, provide only its name. | Marketing Department |
| Update Invoice | If true app will find Invoice with same Invoice number and will update. | true | |
| Line | Line: Description Type | If specified line will be processed with following rules: DescriptionOnly – show only text line, Subtotal – Subtotal calculated and displayed. | Subtotal |
| Line: Amount | The amount of the line item. | 10 | |
| Line: Description | Free form text description of the line item that appears in the printed record. | Rock Fountain | |
| Line: Class name | Reference to the Class for the line item. Please, provide only its name. | Fountain | |
| Line: Product name | Reference to an Item object. Please, provide only its name. | Rock Fountain | |
| Line: Quantity | Number of items for the line. | 2 | |
| Line: Service date | Date when the service is performed. Default date format is dd-MM-yyyy. You can change it on Settings page. | 09/20/2015 | |
| Line: Tax code name | Reference to the TaxCodefor this item. Please, provide only its name. | NON | |
| Line: Unit price | If used for unit price, the monetary value of the service or product, as expressed in the home currency. You can override the unit price of the subject item by supplying a new value. | 14.99 | |
| Discount: Amount | Amount by which the amount due is reduced. | 8.5 | |
| Discount: Class name | Reference to the Class for the discount item. Please, provide only its name. | Fountain | |
| PrivateNote | Private note | User entered, organization-private note about the transaction. This note does not appear on the invoice to the customer. | Summary for sample invoice |
| SalesTermRef | Sales term name | Reference to the Term associated with the transaction. Please, provide only its name. | Term One |
| ShipDate | Shipping date | Date for delivery of goods or services. Default date format is dd-MM-yyyy. You can change it on Settings page. | 09/20/2015 |
| ShipMethodRef | Shipping method name | Reference to the ShipMethod associated with the transaction. Please, provide only its name. | UPS |
| ShippingAmount | Shipping amount | How much is your shipping cost. ‘Shipping’ functionality should be enabled for your QuickBooks account. | 150.25 |
| ShippingTaxCode | Shipping tax code name | Tax applied to your ‘Shipping amount’. Please, provide only its name. | HST ON |
| TrackingNum | Tracking number | Shipping provider’s tracking number for the delivery of the goods associated with the transaction. | 1Z9999999999999999 |
| TxnDate | Invoice date | The date entered by the user when this transaction occurred. For posting transactions, this is the posting date that affects the financial statements. If the date is not supplied, the current date on the server is used. Default date format is dd-MM-yyyy. You can change it on Settings page. |
09/20/2015 |
| TxnTaxDetail | Total tax | Total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list. | 100 |
| Txn tax code name | Reference to the transaction tax code. | ||
| TransactionLocationType | Transaction Location Type | For France only. The account location. Valid values include:WithinFrance(default) FranceOverseas OutsideFranceWithEU OutsideEU | FranceOverseas |
If I am importing multi-line invoices into QBO, is there a way to indicate the beginning of a new invoice without having to number all the lines manually? With the old IIF importers, there was a column where you entered “new” to indicate the start of the next transaction. Please advise. Not all of my invoices have the same number of lines. This will be very tedious. Thank you!
Hi Christian! Thank you for your question. You can use the same reference number to indicate that multiple lines belong to the same invoice.
Please, take a look at our guide on multilined transactions: https://cloudbusinesshq.com/2016/02/how-to-import-multilined-transactions-into-quickbooks-online-through-the-example-of-multilined-purchase-order-import/
Ho w do I import an Invoice with multiple line items with different unit amounts and quantity?
Hello Christina! Thank you for asking your question here.
To import invoice like you described, you would need to use the same invoice numbers for those lines of your excel sheet you would like to join to one transaction. Within the lines you can specify the item names and the quantities/unit amounts needed.
Please take a look at this guide for more details: https://cloudbusinesshq.com/2016/02/how-to-import-multilined-transactions-into-quickbooks-online-through-the-example-of-multilined-purchase-order-import/
Hi! Our current invoices in QBO use the “Terms” field (i.e., 15 days, 30 days) as one of the column headers. This data is stored in the client information section under “payment and billing”. I don’t see “Terms” as one of the fields on the invoice import template. Is there something I can use to import that field?
Hello Tim! Thank you for your question.
We have “Sales term name” field for “Terms” on QBO side. At mapping step this field is located between “Shipping address: Line3” and “Deposit”. At our template it is an “AO” column, located between “Private note” and “Shipping address: City”. Let me know, if you have any further questions.
Hi Anton! Thank you, I found the “Sales term name” and was able to use it. Thanks for your help!
I imported Invoice file with Deposit column = 50 -> This deposit appeared in the QBO Invoice. (GOOD)
Now, when I update the Invoice (Update Invoice = TRUE), I want to ignore value in the Deposit column, not to override deposit in QBO Invoice. How do I ignore Deposit column value during Update? I tried not mapping Deposit column during import but the deposit is wiped in QBO Invoice anyway. Thank you in advance for your help.
Hello Steve, thank you for contacting us.
When you need invoice update, you need to include there both correct existing data and new edited data. Please check our useful guide regarding this topic: https://cloudbusinesshq.com/2019/07/bulk-update-of-transactions-in-quickbooks-online-using-business-importer/
By the way, you can check your email, I have already sent there instructions regarding this case.
How do I create multiple invoices using data from Excel?
Hello TJ, thank you for your question.
You will be able to import Invoices in bulk using Business Importer. If you have different Invoice numbers in your spreadsheet the app will process them as separate Invoices. The rows with the same Invoice number will be grouped into one multi-lined transaction.
Please let me know if you have any further questions.