Re: Name Formats in Import Wizard
Well I was having huge problems with the import wizard being very fussy about what name formats it does accept and assuming that the data I receive will always have similar problems much of it being from MapMate [MapMap allows users to decide on name formats]. I decided to write a routine that would reformat names according to some rules that could be enforced each time I receive data from third parties. After all it couldn't be that difficult.
Well it is not that easy either. I realised that Regular Expressions [RegEx] are now available to windows users [something I've been sad enough to want for many years]. RegEx are a world in their own right with no absolute constants between languages. VBA is a late comer [it uses VBScript] and has many missing bits including split and replace. Both of which would have saved me a lot of time.
I have come up with a RegEx that others may find useful, may...
"^(\w[\']\w*|\w*)\,\s(\u004D\u0072\u0073\s?|\u004D\u0072\s?|\u004D\u0069\u0073\u0073\s?)?(\w\.\w\.\,?|\w\.\s\w\.\,?|\w*\.?\s?\w?\.?)?"
With this I can walk through a list of names in roughly this format:
Surname, A., Surname, A.B., Surname, A. B., Surname, Mrs A., Surname, Mr A.
Its then a simple case of reformatting the name to A. Surname, and putting the list back together. The routine only finds the first item, I process and remove it and send it back until all the items have been dealt with.
Add to this a Select Case statement to deal with variants that don't conform and therefore have to be adjusted by a specific case [I had 15 in 650] and I hope to test this when my new server arrives. The old one died, probably because it could no longer cope with my attempts to import data :)
I had to make use of some software http://www.regexbuddy.com/ written by Jan Goyvaerts co author of "Regular Expressions Cookbook". It would have been difficult without this help and other resources such as http://www.regular-expressions.info/ another of Jan's sites.
I hope someone finds it useful.
Tony
Somerset Environmental Records Centre