Hi,

So our family business sends out a marketing mail-out each Christmas to the customers who live in our home state. We have records going back at least as far as 1994. Each year we export mailing list address data and give it to a local marketing company. This company cross checks the addresses with the US Postal Service and gives us back a list of addresses that are no longer valid and a list of addresses that have changed (including what the new addresses are). Unfortunately, we have never updated our master database with any of these changes over the years, so the accumulated changes are pretty numerous.

My question: Is there a way to automatically update our database with these corrections (deleting certain records, updating others) using these external CSV files or do we have to change things by hand?

Thanks,
Mark

Yes, there are two different CSV files. One for the no-longer-valid addresses (for which a newer address could not be found) and one for the updated addresses.

When you ask about a unique field, do you mean something like a customer number that is unique to each customer? If so, the answer is no (unfortunately). Each entry in the database will have a unique invoice number because the information gets input at the point of sale, but any given customer could have shopped with us dozens of times and thus will be tied to dozens of invoice numbers. That question, alone, makes me think we might be up a creek...

-Mark

This may be too broad a question to ask without someone seeing all the details of our particular database setup, but is there a way to retroactively program in a feature such as a unique customer number? Or to include it from this point forward? Or would that be completely pointless without having had it from the inception of the database?

Well, perhaps. The ASCII file contains fields for name, address, city, state, and zip for both the new address (as updated by the USPS) and the old address (as it currently exists in our database). For example, here are all the fields listed in the file (the last three are sorting data from the USPS that are not pertinent to our database):

First Name(s); Last Name; Delivery Address; City; State; Zipcode; Delivery Address Before NCOA; City Before NCOA; State Before NCOA; ZIP Before NCOA; Return Code; DPV Confirmation; DPV Footnotes

Is there a way to use the "Before NCOA" data to reference the new, updated data to make the change in our system?

Thanks for you input with all this,
Mark

So if that were to work, what would be the actual procedure I would use to search, match, and replace entries between the ASCII file and the existing database? I'm not overly well versed in Sesame. Learning as I go...