Bulk edit of customer accounts possible?

[Mariner]

We recently changed PSP, moving from Worldpay to Sage pay (now catchily-renamed 'Opayo').

However, many moons ago when we started using Actinic, we kept all customer names in a single field rather than splitting into First Name and Last Name. This was a good choice at the time as we have a lot of overseas customers, many with unusual names and it was just easier for us to work in this manner. However, after moving to Sage pay, we discovered that they required separate First and Last names instead of a single name field which had been accepted by Worldpay so had to immediately make the necessary changes to the layout in Sellerdeck.

We quickly encountered issues with certain customers who had set up accounts (accounts are optional), as they were still somehow able to make payments via Worldpay, even though we had deleted the option in our payment settings! Going through hundreds of accounts and manually editing the names took quite a while but it was the only option we could find at the time. However, we have now discovered that certain other customers are encountering errors on the site which makes them unable to reach the payment stage. This is because, when we split the customer names manually in the "Edit Customer Details" window, we only changed the Accounts tab and not also in the Buyers tab.

Are we really going to have to waste many hours going through hundreds of customer accounts and editing these manually? Exporting the data into a spreadsheet and reimporting after editing would be a heck of a lot easier, but it doesn't seem that this is possible. I tried looking at the Access file, but this was rather complex and I'm a bit concerned about editing the database directly outside of Sellerdeck.

We've been working flat out in recent weeks due to a combination of a busy period for us alongside staff absences as family members need to first wait for Covid tests and then wait for the results. With no end in sight and limited time as Christmas rapidly approaches, there must surely be an easier way than spending many hours editing each customer account manually by painstakingly editing each one within Sellerdeck?

Any advice would be greatly received!

[Mike Hughes]

I like doing this kind of thing as an excel macro. It’s a relatively simple task to read the name records from the sellerdeck database, split them into first and last name if required and then write them back to the Database.

the same kind of thing can be done in access.

I won’t be at my computer for a few days but I’ll take a look at it when I get some time.

Mike

[Mariner]

Thanks Mike.

I only have basic experience of the use of Excel and Access but I'm relatively tech-savvy so I'm capable of following basic instructions/pointers where necessary. Exporting our 1,000+ customer accounts as a CSV, I'm rather baffled by the sheer plethora of information displayed when viewing the file in Excel. I can see which bits need to be changed, but not how the changes need to be made and, as mentioned, I've never used Excel in conjunction with Access. Any assistance would be gratefully received if you find the time!

Michael

[Mike Hughes]

Hi Michael,

I've been doing a little thinking about this and as always the difficult bit is specifying exactly what the routine should do. I don't have access to your data so there are a few questions you can help me with:

1. Are all your names 'First Name' <space> 'Lastname'? e.g. are there any titles (Mr, Mrs, Ms, Dr, Rev, etc) or other bits of superfluous information ( e.g. "Mr Formal Customer BSc, Phd, MBA" etc) in the name field and if so what would you like done with them?

2. Are any names entered as 'Lastname' <comma> 'First name' or similar that might not be correctly split if we use the order in which they appear to split them?

3. If there are more than two names should we just move the last one to the 'Last Name' tab?

4. Can you think of any other difficulties you came across when you did it manually that would make it a less than simple task?

As you're happy with exporting your customer accounts to a CSV I assume you'll be happy enough if we can correctly split 98% or so of the names and you'll be able to look at the results in a CSV to quickly spot any that might need to be manually edited. Especially as we won't be touching the sName field. Just using it to populate the sFirstName and sLastName fields.

Mike

[Mariner]

Hi Mike,

Sorry for the delay in getting back to you. I've been so busy over the past couple of weeks that I've barely had time to think and haven't even logged in here since 6th. All catch-up dealing with the yearbooks which were delayed due to pandemic and lockdown. I've now hopefully got the chance to breathe before it gets too busy before Christmas though, that said, I've got two magazine adverts to produce today and a 16 page sales leaflet to send to the printers next week! One of the joys of wearing lots of hats in a small family business.

The names are all split by spaces, not commas so exporting to CSV ought to be fine. One slight issue may be that some western customers have more than one first name or at least like to be called by their first and middle names. That shouldn't be too much of a problem if we're just separating out the last name. Also, we've got a few "von der", "von", "de" and so forth which will need to be included in the family name. Added to that, many overseas customers, mostly of hispanic origin have more complex compound names and some customers have always listed their family names first (mostly French customers, in fact). In general, if customers have two family names, we'll need to transfer both across. Ultimately, I fully expect to have to manually go through and check and edit the CSV after any automated changes but, if it was possible to transfer the majority of the names automatically, this would certainly save untold hours of work.

We've always had a separate salutation field so there shouldn't really be issues with Mr/Miss/Dr etc. We have some Jnr/Junior suffixes and possibly the odd OBE/MBE but, again, this is something I can edit in the CSV if required.

I don't think there are any other issues which need to be mentioned. I'll try to check back here every couple of days for responses, providing you find the time to send one.

Michael

[Mike Hughes]

Hi Michael,

I'll take a detailed look at this next week. In the meantime I've sent you a contact email via your website. For some reason I can't seem to send Private Messages from here anymore.

Mike