Data cleansing using MS Access

Beginners

unread,

Jul 24, 2002, 10:40:38 AM7/24/02

to

Hi,

I am a beginner of MS Access and I would like if there's a function or sql script to do data cleansing by using MS Access.

For example,

MR David Chan, Siu Ming

Miss Tori, Amos

Mr Terri, Hall

Mr Tom, Chan

How can I split it into 3 different fields by using sql in Ms Access?

Thanks in advance!

Cheers

Beginners

Larry Linson

unread,

Jul 24, 2002, 12:40:45 PM7/24/02

to

Ah, "data cleansing". That's a good term.

If you can specify exactly how you want each form of the names handled, you
can write VBA code functions to split them, and use those functions in a
query/SQL to split them. That is often not an easy task, because not all
names are identical in format.

In your case, note that the first name has five parts, while the others have
three.

If, indeed, you _always_ want the first word (characters that precede the
first blank/space) in one field, all the characters that precede the "," in
the next field, and all the rest in a third field, it will be simple.
Usually, the data is not that "clean" when we receive it.

--
Larry Linson
http://www.ntpcug.org -- North Texas PC User Group
http://members.tripod.com/ntaccess -- Access SIG
http://members.tripod.com/accdevel -- Access Samples and Examples

Beginners <bief...@hotmail.com> wrote in message
news:ahme90$3q...@imsp212.netvigator.com...

Pieter Linden

unread,

Jul 24, 2002, 9:26:52 PM7/24/02

to

"Beginners" <bief...@hotmail.com> wrote in message news:<ahme90$3q...@imsp212.netvigator.com>...

> --

An instructor of mine wrote an app that did lots of data cleansing and
parsing. If it's fairly simple, you can use queries to parse it.
That's much better because you can work on entire columns at a time.
If your data is really irregular, you have to use the filescripting
library, which is a pain.

If all your data is like this...
Title: everything up to the first space (look up Mid, Left, Right,
InStr in Help).
Find the position of the first space, go one space to the right, take
everything from there until you get to one ot the left of the comma.
Last name - get everything from the comma to the end of the line. Use
right$ to get rid of the comma, then TRIM to clean up the extra
spaces.

HTH -- happy learning!
Pieter

Steve Jorgensen

unread,

Jul 25, 2002, 1:32:14 AM7/25/02

to

I can't add much to what's been said already except that Access is an
absolute joy as a data cleansing tool. It is not the fastest, for
that you would need something like DTS or some other ETL tool, but
it's got to be the easiest to use.

On Wed, 24 Jul 2002 22:40:38 +0800, "Beginners" <bief...@hotmail.com>
wrote:

>This is a multi-part message in MIME format.
>
>------=_NextPart_000_00C3_01C23363.221BC7A0
>Content-Type: text/plain;
> charset="big5"
>Content-Transfer-Encoding: quoted-printable
>
>Hi,
>
>I am a beginner of MS Access and I would like if there's a function or =
>sql script to do data cleansing by using MS Access.=20

>For example,
>MR David Chan, Siu Ming
>Miss Tori, Amos
>Mr Terri, Hall
>Mr Tom, Chan
>
>How can I split it into 3 different fields by using sql in Ms Access?
>
>Thanks in advance!
>
>Cheers
>Beginners

>------=_NextPart_000_00C3_01C23363.221BC7A0
>Content-Type: text/html;
> charset="big5"
>Content-Transfer-Encoding: quoted-printable
>
><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
><HTML><HEAD>
><META http-equiv=3DContent-Type content=3D"text/html; charset=3Dbig5">
><META content=3D"MSHTML 5.50.4916.2300" name=3DGENERATOR>
><STYLE></STYLE>
></HEAD>
><BODY bgColor=3D#ffffff>
><DIV><FONT face=3DMingLiu size=3D2>Hi,</FONT></DIV>
><DIV><FONT face=3D=B2=D3=A9=FA=C5=E9 size=3D2></FONT> </DIV>
><DIV><FONT face=3D=B2=D3=A9=FA=C5=E9 size=3D2>I am a beginner of MS =
>Access and I would like if=20
>there's a function or sql script to do data cleansing by using MS =
>Access.=20
></FONT></DIV>
><DIV><FONT face=3D=B2=D3=A9=FA=C5=E9 size=3D2>For example,</FONT></DIV>
><DIV><FONT face=3D=B2=D3=A9=FA=C5=E9 size=3D2>MR David Chan, Siu =
>Ming</FONT></DIV>
><DIV><FONT face=3D=B2=D3=A9=FA=C5=E9 size=3D2>Miss Tori, =
>Amos</FONT></DIV>
><DIV><FONT face=3D=B2=D3=A9=FA=C5=E9 size=3D2>Mr Terri, =
>Hall</FONT></DIV>
><DIV><FONT face=3D=B2=D3=A9=FA=C5=E9 size=3D2>Mr Tom, =
>Chan</FONT></DIV>
><DIV> </DIV>
><DIV><FONT face=3D=B2=D3=A9=FA=C5=E9 size=3D2>How can I split it =
>into 3 different fields=20
>by using sql in Ms Access?</FONT></DIV>
><DIV> </DIV>
><DIV><FONT face=3D=B2=D3=A9=FA=C5=E9 size=3D2>Thanks in =
>advance!</FONT></DIV>
><DIV> </DIV>
><DIV><FONT face=3D=B2=D3=A9=FA=C5=E9 size=3D2>Cheers</FONT></DIV>
><DIV><FONT face=3D=B2=D3=A9=FA=C5=E9 =
>size=3D2>Beginners</FONT> </DIV></BODY></HTML>
>
>------=_NextPart_000_00C3_01C23363.221BC7A0--
>

--
Steve Jorgensen
Database application developer - available
http://www.coho.net/~jorgens

kalpana...@gmail.com

unread,

Oct 5, 2012, 1:25:27 AM10/5/12

to

Data cleansing is the process of uncovering and correcting inconsistent records from a table, a set, or database. This is used mainly in databases to identify imperfect, incorrect, erroneous and irrelevant parts of the data and then modifying, replacing or deleting the incorrect data.

Informatics Outsourcing Provides the service Worldwide.