Bulk update of customer data

Questions and Answers that relate to the [CS] Version of POS Pizza
Post Reply
mporreca
Member
Member
Posts: 17
Joined: Thu Nov 10, 2022 11:42 am

Bulk update of customer data

Post by mporreca »

I'm trying to clean up some of the data left by the prior owner, who used the postal code field in the customer table for notes rather than actual zip codes, which the PAX terminals don't like. I'm able to export the data to a CSV file with the data export tool, and can clean up the zip codes in the CSV file. I pared down the data in the file, so that it only included customers with bad zip codes.

When I attempt to import the data, I have the option of replacing customer data, adding to the customer data, and removing customer data.

  • Which option do I use to just replace the existing customer data with the contents of the CSV file, while not deleting the customerss in the database that are not in the CSV file?
    Do I need to specify the field order when I import, or just select all fields in the CSV file, then do the import?
User avatar
Scott
Site Admin
Site Admin
Posts: 2557
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Bulk update of customer data

Post by Scott »

* Replace will purge all existing customer data, then import the new customers.
* Add will add new customers that aren't already in your database.

Given this, neither will work unless you export all customers first. You can add your current "fixed" customers to the top of the full customer export file (after the header), and then import that as an option. There will be dupes, with bad zips, but they come later in the csv file, and will be ignored if that customer is already present from an earlier entry.

To clarify:
  1. Export ALL customers to a new .csv file.
  2. Edit that file and append your fixed list of customers at the beginning of the ALL customers file.
  3. Save the edited file, and import it to the POS.
The duplicate entries at the end will be ignored.

As always, do a database backup before importing in case something goes wrong!

Scott
mporreca
Member
Member
Posts: 17
Joined: Thu Nov 10, 2022 11:42 am

Re: Bulk update of customer data

Post by mporreca »

I tried this, and was able to get the data imported, but it looks like it didn't match the fields up correctly. When I did this:
-I backed up the database
-I exported all customers to a CSV file
-I edited the CSV file with Excel, sorted it by zip code, and cleared out the junk in the zip code field where there was something other than a zip code. The record was intact, with the exception of now having a blank in the zip code field.
-I saved that file.
-I ran the Customer Import, and selected the fields to import
-The customers were imported, but the fields were mis-matched, i.e., phone number was in the Customer ID field, customer ID was in the phone number field, house number was in the Street field, and so on.

I can provide screenshots and the CSV file if needed as an example.

I restored the database from the backup to get the customer data back as it originally was.

Can you tell me what I missed, or how to get the fields mapped correctly?
User avatar
Scott
Site Admin
Site Admin
Posts: 2557
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Bulk update of customer data

Post by Scott »

You have to align each field from top to bottom. In places where there is no specific field use the *Blank Placeholder* object. Once you have all of the fields aligned, click the mouse on each field in the center and verify that it aligns correctly with the list on the right. Fix any misaligned fields then import.

See the instructions on page 48 of the Manager's docs.


Scott
mporreca
Member
Member
Posts: 17
Joined: Thu Nov 10, 2022 11:42 am

Re: Bulk update of customer data

Post by mporreca »

Thanks, Scott. I was able to do this, and get the customer zip codes cleaned up. The customer list now looks good, but when I did the import, I seem to have lost the relationship between the customers and their order history. When I run a customer report for some customers, I don't see their historical orders, but if I query the orders_tbl, I can still see all of the details. It looks like the customer_id may have changed, and the one in the customers table no longer matches the one in the orders_tbl for this customer, but I might not be looking at that correctly.

Any guidance on how / if this can be corrected?
User avatar
Scott
Site Admin
Site Admin
Posts: 2557
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Bulk update of customer data

Post by Scott »

Yes, since there were changes, the customer records became *unlinked* from their respective past orders. There is no way to get those back, but any new orders made with those customers will be linked correctly.


Scott
Post Reply