How to Import Customer Data to QuickBooks Online From Excel/CSV?

When running any organization, it does not matter whether it is small or big; you need the customer details. QuickBooks, […]

When running any organization, it does not matter whether it is small or big; you need the customer details. QuickBooks, like other trending software, permits the ease of importing and exporting data. We will discuss how to import customers to QuickBooks Online from Excel/CSV in the below blog.

When do you need to Import Customer Data?

Manually data entry related to each customer instantly might be very frustrating and time-consuming. Therefore, you need to make an intelligent decision to use the import feature to reduce delay and enhance accuracy. The importing option is mainly used in QuickBooks when there is a frequent need to analyze reports or when running sales tax. Each time manual uploading of the customer data might be complex, so in this scenario import, or import-export feature is beneficial.

Things you need to Consider Before you Import Contacts from Excel

  • Check the size of the file; it should be up to 2MB or 1000 rows
  • For a larger list, you need to split the list into small parts and perform multiple imports
  • Make sure you are not trying to import sub-accounts because QuickBooks online does not support sub-accounts. If there are sub-accounts, then it is suggested to change them into the parent accounts before importing. Once the import is complete, you can easily change them back into sub-accounts.
  • If mistakenly the wrong file is imported, or import is done incorrectly or requires re-importing, the customer data won’t be overwritten. It in turn creates duplicate entries. Therefore you need to delete the duplicate entries or edit them individually.
  • Make sure the name field entries are unique
  • You can add only a single email address while importing. However, you can add others later after the import
  • check the spreadsheet format as below:
    • It should not contain any blank rows, charts, or formulas
    • It should contain contacts
    • Check the first row should contain headers
    • Verify the spreadsheet does not contain any errors
    • Check the entries do not have any quotation marks or colons.
Helpful- Import Vendor List into QuickBooks Online

How to Import Customers to QuickBooks Online from Excel/CSV?

You can directly import customers to QuickBooks Online data from Excel/CSV; here are the steps.

Step 1: Prepare an Excel Spreadsheet for Import

To format the spreadsheet correctly is important and the initial phase is to import it into QuickBooks. Verify the field restrictions and descriptions while preparing a sheet. Follow the below instructions:

  • Firstly make a new spreadsheet in Excel.
  • Ensure that you are accessing sheet 1 of the spreadsheet and check the first row. It should be filled with a column key in capital letters. Check there should be no blank field
  • The import maps the address of the Excel sheet to the QuickBooks online
  • Save the spreadsheet with the correct format, such as XLS or an XLSX file.
  • In case you are accessing the Google Sheets, then the spreadsheet gets automatically saved.
  • Review the entered information and ensure that everything looks perfect.

Start Importing Customer Data

Once you are confirmed with the correct spreadsheet, you are now ready to import customer data into QuickBooks Online. Here are the steps:

  • They initially log in to QuickBooks Online
  • Click on the Settings option, then choose Import Data
  • Hit Customers
  • Now click on the Browse button to start uploading the file from the system
  • Select the file and then click Open
  • If you need to upload from the Google Sheets, then click on the Connect button to sign in to the Google account.
  • Hit on the file and then choose the Select option
  • Choose Next
  • Discover the column that says field, click on the small icon
  • Start matching the field corresponding to the field in the QuickBooks online
  • In case there is no matching column in the spreadsheet, click No Match
  • After this, confirm the contacts and then unselect if there is an irrelevant field you don’t want to save
  • Once you are done with these steps, click on the Import button.
Helpful- Import Customer or Vendor Contacts from Gmail to QuickBooks Online

How to Import Customer or Vendor Contacts from Excel to QuickBooks

Importing the customer or vendor contacts from Excel can be done just with a few simple steps but certain things need to be considered before you conduct an import from Excel to QuickBooks.

Contact fields that can be imported from Excel to QuickBooks

Before conducting the import process, it is important to know which and all contact fields can be imported and any restrictions on which fields are optional and best practices before you get started.

  • Full Name: Import field
    The first, middle, and last name must appear in a single field or column before importing
  • Email: Import field
    Restrictions:-
    ● Phone: There is no restriction in formatting but a limit of 21 characters
    ● Mobile: There is no restriction in formatting but the limit of 21 characters
    ● Fax: There is no restriction in formatting but the limit of 21 characters
  • Company: Import field
    Restrictions:- 50 characters is limited
  • Street and city: Import field
    Street: 2000 characters is limited
    City: 255 characters is limited
  • Zip code or ZIP code
    Restrictions:- 30 characters is limited
  • Website: Import field
    Restrictions:-
    ● It should be in a valid URL format, for example; http://www.website.com.
    ● 1000 characters is limited
  • State/Territory/Province/Region: Import field
    Restrictions:- Limits of 255 characters
  • Country: Import field
    Default to your home country if it is left blank
  • Opening Balance: Import field
    It is optional (Balance of the account as of the date of opening balance, in the currency of the account).
  • Account Number: Import field
    The Customer or Vendor account number enables you to combine customers or vendors into a single account or allocate every customer or vendor to a separate account.
    Restrictions:- The account number must meet these rules: customers must start with 411 and vendors must start with 401. It must be between digits 6 to 15. It contains both numbers as well as letters.
  • Report Basis for vendor
    Restrictions:- Select the relevant report basis for the vendor (cash or accrual). In case, it is left blank then this field will default to cash.
  • Opening Balance Date: Import field
    Make sure that the opening balance date is valid
  • Resale number (customers) or Tax ID (Vendors): Import field
    Restrictions:-
    ● For customers: The resale number has a limit of 16 characters.
    ● For vendors: The tax ID has a limit of 20 characters.

Step 1- Create an Excel file for importing

  • The very first, create a new Excel spreadsheet
  • After that, ensure that the columns of the Excel spreadsheet must have the following headers on row 1. Along with that, make sure that no other headers or blank cells should be there at the top of the spreadsheet:
    • Full Name
    • Company
    • Email
    • Phone
    • Mobile
    • Fax
    • Website
    • Street
    • City
    • State
    • Postal Code
    • Country
  • Next, complete the information of contact in your spreadsheet.
  • Then save the Excel spreadsheet as a .xls or a .xlsx file and also make a note of the location of a particular Excel file.

Step 2- Importing Process

Once you’re done with the above steps then sign in to your QuickBooks Company and follow the import further steps involved.

  • Go to Customers and select the invoice
  • Next, choose the Import data under the Tools section
  • Now, select the Customers or Vendors depending on which you wish to import
  • After that, select the Browse option
  • Locate and choose the Excel file
  • Make a click on open and then hit the Next button to continue
  • Again hit the next after importing the data
  • At last, adjust the data if required and then click on the “Import” option.

How to Verify the Exported File

It’s important to review the details before you import your contacts and verify that everything looks right. When something goes wrong with the shipment, manually fixing or deleting your imported contacts may be a lengthy process.

  • Open and verify the earlier saved .csv file
  • Make sure the Excel spreadsheet columns in row 1 have the same headers as those in the capital letters below, in the same order. Please make sure that the top of the spreadsheet contains no blank cells or other headers. Also, be sure to use Sheet 1.
  • Review the table and fill in any missing information, or fix any errors. If an important problem arises, you may want to retry the export. Be aware of the address field in QuickBooks that helps you to map to the Billing address for that contact.
  • Save as a.xls or.xlsx file in the Excel spreadsheet; also remember where you have saved your file. Make sure the file doesn’t exceed the maximum of 2 MB or 1000 rows; otherwise, split it into several files for import.

Hopefully, the above information is helpful to you to proceed confidently to import customers to QuickBooks Online. Save more time with the use of the tool! Using intelligent techniques is a great savior. There are several importer tools available with many perks. For instance, the Dancing Numbers Tool is a productive tool that assists you in importing data in Batch and has many other benefits. Stay updated with the platform to learn more tips and tricks to import, export, and delete processes.


Frequently Asked Question

Can I Import Customer’s Data in Batch?

You can use the importer tool if you need to import data in bulk and save your precious time for other tasks.

Is the Importer Tool Compatible with all Versions of QuickBooks?

Yes, Dancing Numbers importer is compatible with all QuickBooks versions, including QuickBooks Online, QuickBooks Desktop, or Enterprise.

How can I Prevent Importing Errors in QuickBooks Online?

Make sure you are formatting the spreadsheet accurately and following the required protocols and restrictions.

What are the options available to Import Customers to QuickBooks Online?

Excel or using an importer tool

Where can you Import Data into QuickBooks Online?

You need to locate the import data menu in QuickBooks to import data.

close btn

Get Your Case Study

Call Now+1-800-596-0806
Top