Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2012

    Unanswered: How to set a format on strings upon data import

    I have a database where I need to import data from an excel file.
    What my employer needs me to do is upon appending the data to a table which I named properties, under the field which I called owner, all the names should follow the correct format. Most of the inputted data does not follow the correct format.

    The correct format of the names should be:
    K. CURTIZ -> Where K is the initial of the First Name, it should have the '.' and a space before the Family Name

    Here's an example of a data that is inputted incorrectly:
    R OTTESON -> it should be R. OTTESON

    another example where there are 2 names of 2 different persons, it should be inputted this way:
    A. & J. PANAG

    Mostly inputted incorrectly this way:
    J&T BOTIFAN -> it should be J. & T. BOTIFAN

    I hope somebody can help me with this.

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    What your employer has asked you to do is not a simple thing. You would need to write a fairly complicated function (or set of functions) to test for each condition (does the input contain an ampersand, does the input contain full stops, does the input contain spaces, are all the various characters in the right places relative to each other, etc), and then rearrange the input according to the results of the tests. This is giving me a headache just thinking about it (although that could be the 'flu). For a start, look into the InStr and Split functions in VBA.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  3. #3
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Quote Originally Posted by weejas View Post
    What your employer has asked you to do is not a simple thing.
    To be brutally honest, that's got to be the understatement of the century! A number of years ago, one of the largest banking corporation in the country bought out a smaller, regional bank, and needed to move the data on these 'new' clients into their own system. The problem, of course, was making the names and addresses come into formatting alignment with their own system. They assigned a team of six experienced programmers, who worked on algorithms to automate this process for six months, and finally gave up! The problem was that there is an almost infinite number of possible combinations involved in something like this!

    Just think about it! You've mentioned names like R OTTESON andJ&T BOTIFAN, but what about Alan Hale, Jr, Thurston Howell, III, and Dr. Ben Casey? The possibilities go on and on and on, like the Energizer Bunny!

    You can try to automate the process, as weejas suggested, but in the end you're going to have to go through the records, one by one, and check them manually. The problem, of course, is that there's been no discipline on the Excel, input side, and that that app was designed poorly, cramming first and last names into one field.

    And in the end, you may spend more time and money trying to automate this than you would if you simply hired what they call 'production data input' people. That's what the bank above ended up doing.

    Linq ;0)>
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts