Update customers by importing fresh data

dpotts · ‎March 18, 2020

We want to update customer information in QuickBooks and have existing customers updated and new customers appended in our QuickBooks file/database. We are currently using QuickBooks desktop and would prefer to be able to update data in it instead of having to change to QuickBooks online.

I've read various threads related to updating and importing data:

https://quickbooks.intuit.com/learn-support/en-us/install/update-customer-information-by-importing/0...

https://quickbooks.intuit.com/learn-support/en-us/customer-lists/import-customer-or-vendor-contacts-...

https://quickbooks.intuit.com/learn-support/en-us/manage-lists/which-contact-fields-are-imported-fro...

https://quickbooks.intuit.com/learn-support/en-us/manage-lists/import-or-export-ms-excel-files/00/20...

Whether or not we can do what we want is unclear. We don't want to update one customer at a time through the QuickBooks UI, because we have thousands of customers and their data changes often. Instead, we want to export our customer data from the legal program we use (the "oracle" for our data) and import the data (either .xlsx or .csv is fine). If the import finds an existing customer, it should update that customer's data. If the import doesn't find a customer, it should append a new customer using that data.

I've seen a comment saying this is possible in QuickBooks desktop; but the person commenting then went on to discuss how to update one customer through the UI.

I chatted with a QuickBooks salesperson today; and they said this functionality is available but only in the online version of QuickBooks. However, one of the threads above says this isn't available in QuickBooks online (that thread is a year old, so maybe this has been added; if it has been added, it isn't clear that it has been when searching online for articles about it).

Is this functionality available in QuickBooks desktop? If not, is it available in QuickBooks online? If it is available in one or both of these, is there an article online that describes how to do this?

From looking at one of the articles about importing data from Excel, it seems like doing what we want would be problematic because there is no unique ID field other than the customer name field. The problem is that sometimes our customer names change. I would expect that in these situations QuickBooks would at best create a new customer record. This would orphan the existing customer record that has the customer's old name, and would require us to manually do some clean up so that we don't lose any historical information.

It seems that to work accurately, QuickBooks would need include a field to let us enter unique customer IDs that we create/manage and then use that field for deciding whether or not a customer exists or needs to be added. Having this ID, which at least in our legal program doesn't change for a client, would allow the customer name and other information to change as necessary and still let QuickBooks match up the data correctly.

Can we update data the way we want in QuickBooks?

Thanks in advance for your help?

Don

dpotts · ‎March 19, 2020

Thanks for your quick response!

It's more/different than just seeing the list of all the changes. Actually, we don't necessarily need to see what has changed. We want to be able to point QuickBooks to a .csv/.xlsx file, have it import the data and update any existing customers with the data from that file (without creating duplicate customers in QuickBooks) and append any new customers.

Thanks for passing this on to the product engineers! If they want to see a good implementation of this, they can look at the .CSV import block in Airtable. Airtable has two .csv import options: one that isn't very capable, and the .CSV import block which is wonderful and does exactly what we want to do in QuickBooks.

Thanks, again!

View solution in original post

MJoy_D · ‎March 18, 2020

Hello, @dpotts.

In QuickBooks Desktop (QBDT), you can use the Add/Edit Multiple List Entries window to add and edit multiple customers. You can also copy and paste list information from Excel directly into QuickBooks using this window.

Follow the steps below:

Go to your Lists menu and select Add/Edit Multiple List Entries.
Click the List drop-down arrow and select Customers.
Go to the Customize Columns button at the top to focus on the columns that you’d like to work with. You can also copy (Ctrl+C) data from Excel and paste (Ctrl+V) it into the Add/Edit Multiple List Entries window.
Once you’re done, click on Save Changes.

Check this article for more information: Add and edit multiple customers, vendors, and items.

In case you’ve encountered any errors while doing this, you can go to the Help menu and type Fix errors in the Add/Edit Multiple List Entries window to help you in resolving it.

Let me know if there’s anything else that you need with your account. I’m always here to assist. Have a wonderful day!

dpotts · ‎March 18, 2020

Hi! Thanks for your quick response and help!

Add/Edit Multiple List Entries might help if we knew where the changes in our legal data occurred and could find a meaningful way to filter down to the subset that actually changed. However, with 10,000 plus records that can change at any time, we don't know where all the changes are unless we export the customer data from QuickBook and compare it to what we export from our legal program and then use Add/Edit Multiple List Entries. But, even then, because changes could occur in various fields, there doesn't seem to really be a way to filter the QuickBook data to exactly match the list of records that need to change.

We also use Airtable for status tracking. Because our legal program assigns each customer a unique ID number and we include that in a table in Airtable, we can re-import the data we export from our legal program and, when it finds a record in Airtable with a matching ID (which never changes), it will update the fields that have changed. Any new records, whose IDs aren't found, are appended to the table.

We were hoping that we could update data in QuickBooks just like we do in Airtable, letting QuickBooks figure out what needs to be updated and/or added. It looks like there is no way to do this, though. It seems that the only way to update the data is through manual editing, whether we use Add/Edit Multiple List Entries or other UI. If we do figure out which customers in our legal program are new, though, it does seem that Add/Edit Multiple List Entries can speed up our adding new clients to QuickBooks.

Is there something about the functionality of Add/Edit Multiple List Entries that I'm missing?

Thanks!

JessT · ‎March 18, 2020

I appreciate that you're explaining everything in detail, dpotts.

With the number of records you need to change, it would help you if you'll just see the list of names that need an update. While this isn't a feature right now, I'll bring this idea to our product engineers. That way, they can consider adding this option in future updates.

In the meantime, you can try finding a third-party app that can tell you what needs to be edited or added.

We're just around if you have additional or other questions about QuickBooks Desktop.

Fiat Lux - ASIA · ‎March 18, 2020

@dpotts

Utilize one of these 3rd party tools to import (or update) your customer list in bulk. You may set some filters to avoid any duplication in your list.

https://transactionpro.grsm.io/qbd

https://partners.saasant.com/affiliate/idevaffiliate.php?id=5051_2

Hope it helps.

dpotts · ‎March 19, 2020

Thanks for your quick response!

It's more/different than just seeing the list of all the changes. Actually, we don't necessarily need to see what has changed. We want to be able to point QuickBooks to a .csv/.xlsx file, have it import the data and update any existing customers with the data from that file (without creating duplicate customers in QuickBooks) and append any new customers.

Thanks for passing this on to the product engineers! If they want to see a good implementation of this, they can look at the .CSV import block in Airtable. Airtable has two .csv import options: one that isn't very capable, and the .CSV import block which is wonderful and does exactly what we want to do in QuickBooks.

Thanks, again!

dpotts · ‎March 19, 2020

Thanks, I'll look at these.

APQuinn · ‎June 20, 2021

Did you ever figure out how to update multiple customers information (such as email addresses) for customers that already exist? When I try to add/edit multiple list entries, it gets mad when I try to add a row with a different column than what the existing customer has.

Fiat Lux - ASIA · ‎June 20, 2021

@APQuinn

Utilize the trial version of the apps as I mentioned earlier. Export your data out to XLS, modify it, and import it back.

OnlineAccount23 · ‎September 08, 2023

It's been years and this feature still hasn't been addressed. This isn't complicated. QBO has some form of internal primary key identifier for each client, and lining those identifiers up with information from a CSV so that client information can be mass updated is a basic feature.

OnlineAccount23 · ‎September 08, 2023

It's been almost 3 1/2 years and this still hasn't been addressed. QBO clearly has some form of primary key identifier, and using that identifier to line up data in a CSV is very simple. This is not complicated and is another great example of the many limitations of QBO.

Topics

Training

Community

Resources

Update customers by importing fresh data