How to Manipulate Data upon Import

[JM Noval]

Hi everyone,
I need help once more.

I am working on an access database where I need to import raw data from an excel file.
What I needed to do is to append these data into a table which I named properties.
Under this table, I have a field named owner.

Now under the field owner, some of the data inputs are wrongly done or does not follow the supposedly correct format. What I wanted to do is to manipulate the data upon appending it to the table properties.

The right format for the names should be as follows:

First name Initial must have '.' and a 'space' before the Surname
1. It should be in this format then: K. FAY
2. In some cases the data inputs does not follow the format and was entered this way: K FAY or K.FAY -> where '.' or space was forgotten.

Some have two names coming from 2 different persons related/non-related.
1. Example data: A. & B. BHADRASEN ; G. HERMANUS / S. RAMSDELL (these follows the correct format)
2. Example data that was inputted incorrectly: B&T RAWALA. 'B' and 'T' are the initials, '.' for every initials were forgotten, and as you've noticed there is no space between 'B&T'. It should be in this format, B. & T. RAWALA


To give you more clarity, here are some data I have taken from the excel and it's correct format:
K. FAY ---> Is in correct format already
F MEHARI ---> F. MEHARI (the '.' was forgotten
A. & B. BHADRASEN ---> Is in correct format already
G. HERMANUS / S. RAMSDELL ---> Is in correct format already
B&T RAWALA ---> B. & T. RAWALA (no '.' and space between initial and '&'
R & A MINGORANCE ---> R. & A. MINGORANCE (no '.' on initials)
A. & J. PANAG ---> Is in correct format already
D&T CHICK ---> D. & T. CHICK (no '.' and space between initial and '&'
S & L. FANSLAU ---> S. & L. FANSLAU (no '.' on one initial)

[NeoPa]

You'll need to develop your own code to handle this.

To start with you'll need to consider the exact logic that needs to be applied to it. Explaining what a human needs to understand is far from enough. The logic needs to be specific as it will be applied automatically without human intervention.

[JM Noval]

I tried some codes to somehow get the right format for the names. my only problem now is this one..

Names with two initials..
For example this data: D&T CHICK
By using replace function,
I found a way to have it in this format: D. & T CHICK
What lacks now is a '.' on the initial T to make it: D. & T. CHICK

Is there a way to make it?

Really need help.. Thanks.. XD

[TheSmileyCoder]

A very important issue that you do not mention in your question is whether this is a one time import, or if this is something that has to happen regularly.

Either way, I suggest you take a look at his article before proceeding.

http://bytes.com/topic/access/insights/585228-database-normalization-table-structures

The correct way to approach this is to use a seperate table for the persons, and then relate those two tables together (again see the article I linked for you). This can prevent 99% of the hazle that comes from incorrectly spelled/formatted values.

[NeoPa]

JM Noval:
I tried some codes to somehow get the right format for the names. my only problem now is this one.

Why? What basic logic are you working to?

Notice this was the first and most important step suggested. Without this done correctly first, anything else you do will just be spinning wheels (It may look like work but will just waste time).

Until you properly understand the logic, trying to implemet that logic will be a waste of your (and our) time.

Specific rules that cover every possible scenario must be laid out in order to process the data in accordance with those rules.