If you have a whole bunch of customers to add and you don't want to manually enter them (after all, who would?), we have a couple of options.
Table of Contents
1. Download CSV template
- Go to More > Admin > Customers - Customer Import.
- Follow the onscreen instructions. We cover them in more detail here.
- Click Complete CSV to download the template file example-complete-customer-import.csv.
2. Populate the file with customer data
- Open the CSV file in Excel or a similar spreadsheet program.
- Make sure you do not modify the column headers or the import will likely fail. The second row has example data which is fine to delete.
- Fill in the spreadsheet with your customers. You need to have at least 2 out of these 5 columns populated in each row for a successful import.
- If you have custom fields not found in the existing columns that you want to add, you will need to do two things.
- Set up the customer custom fields in the app.
- Fill in the custom_fields column in the spreadsheet in the following format, where each "field:value" is separated by a semicolon:
field name:value;next field:value
- Tip: To make sure you have the format right (which is crucial to it importing correctly, we recommend you fill in all your custom fields in a test customer, export your customers, find that customer row, copy its cell in the custom_fields column, then paste it into the CSV spreadsheet you are preparing and modify as needed.
- Once you have populated the spreadsheet, export it in the CSV format.
- In a separate tab, go to More > Admin > Customers - Preferences.
- Click the Optionally, require these fields when creating a customer dropdown to see what fields are required, as these apply to customer imports. If you have customers on your CSV without an email and/or phone, either fill in those missing emails and phones, or turn off those settings as required fields before attempting the import. If, for example, email is required and a row in the spreadsheet has a blank email, then that customer will not get imported. Although you could uncheck everything (which does make the import go a little faster), that could result in importing garbage data.
- Make any needed adjustments and click Save.
4. Import completed file
- Back on the Customer Import screen, under step 4, click Choose File.
- Navigate to the file and double click it.
- On the Customer Import screen, click Import.
- The import can take awhile if the file is large. You will receive an email when the import is complete. You can also go to More > Admin > Reports, then the upper right, click Import Results to see the progress.
Both the QuickBooks integrations and the Xero integration have setup wizards and will prompt you to import your customers during the set up process. We strongly recommend importing your customers this way if they already exist on the QB or Xero side of things.
This makes it so that the customers are linked to their QB/Xero counterparts, ensuring that they will sync right away without additional steps. The below links will take you through the process.
The CSV format must be in UTF-8 to import correctly. If you get this error in your tests, upload the CSV to Google Drive, and then re-download. This should automatically convert it to UTF-8.
Generally, if you have a low percentage of failures, it's just some bad data in the import file. It the percentage is over 20% the file probably needs to be adjusted to fit our format.
The error summary is grouped by the fields that failed validation, so if it says: "email,phone,mobile invalid:" that means those records failed all of those fields at the same time. That could mean you have those fields marked as required in the settings, but they weren't found in the CSV.