Clean data with code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that I periodicly update by getting data off a web site. The
data is never exactly "clean" but gerenally has the same type issues to
change. Is there anyway to clean it up wth a query or code after it is the
proper table?

Changes usely are replacements, ie,

Replace;
"State" with "St";
"(" with "";
"." with"";
" " with " ";
etc.


David McKnight
 
hi David,

David said:
Is there anyway to clean it up wth a query or code after it is the
proper table?
Click to expand...
CurrentDb.Execute "UPDATE
SET " & _
"[Field1]=Replace([Field1], '(', ''), " & _
"[Field2]=Replace([Field2], '.', ''), " & _
"[Field3]=Replace([Field3], ' ', ' ')"


mfG
--> stefan <--
 
I used the follow code and got a syntax error. I was trying to have three
seperate fields "clean" by replacing any periods "."

Do I need any error handling on this?


CurrentDb.Execute "UPDATE [Scores] SET " & _
"[Home]=Replace([Home], '.', ''), " & _
"[Visitor]=Replace([Visitor], '.', ''), " & _
"[Location]=Replace([Location], '.', ''),"
--
David McKnight


stefan hoffmann said:
hi David,

David said:
Is there anyway to clean it up wth a query or code after it is the
proper table?
Click to expand...
CurrentDb.Execute "UPDATE
SET " & _
"[Field1]=Replace([Field1], '(', ''), " & _
"[Field2]=Replace([Field2], '.', ''), " & _
"[Field3]=Replace([Field3], ' ', ' ')"


mfG
--> stefan <--
Click to expand...
 
hi David,

David said:
I used the follow code and got a syntax error. I was trying to have three
seperate fields "clean" by replacing any periods "."
Do I need any error handling on this?
Click to expand...
No, you don't need error handling, if your fields are not nullable.
If they are, you have to call the UPDATE statement for each field:
UPDATE [Scores] SET [Home]=Replace() WHERE NOT IsNull([Home])
CurrentDb.Execute "UPDATE [Scores] SET " & _
"[Home]=Replace([Home], '.', ''), " & _
"[Visitor]=Replace([Visitor], '.', ''), " & _
"[Location]=Replace([Location], '.', ''),"
Click to expand...
The last comma is causing the error, drop it.

I forgot to mention, the Replace function is not available in Access
versions prior to Access 2000. In this case use
http://www.mvps.org/access/strings/str0004.htm

mfG
--> stefan <--
 
The above did the trick. One last question on this, if instead of replacing
blanks and other non text I want to replace text that match case specific,
how would I define that?

ie,
CurrentDb.Execute "UPDATE [Scores] SET " & _
"[Home]=Replace([Home], 'MI', ''), " & _
"[Visitor]=Replace([Visitor], 'MI, ''), " & _
"[Location]=Replace([Location], 'MI, '')"
Click to expand...
Click to expand...

Replace "MI" with blanks but leave "mi" alone.


--
David McKnight


stefan hoffmann said:
hi David,

David said:
I used the follow code and got a syntax error. I was trying to have three
seperate fields "clean" by replacing any periods "."
Do I need any error handling on this?
Click to expand...
No, you don't need error handling, if your fields are not nullable.
If they are, you have to call the UPDATE statement for each field:
UPDATE [Scores] SET [Home]=Replace() WHERE NOT IsNull([Home])
CurrentDb.Execute "UPDATE [Scores] SET " & _
"[Home]=Replace([Home], '.', ''), " & _
"[Visitor]=Replace([Visitor], '.', ''), " & _
"[Location]=Replace([Location], '.', ''),"
Click to expand...
The last comma is causing the error, drop it.

I forgot to mention, the Replace function is not available in Access
versions prior to Access 2000. In this case use
http://www.mvps.org/access/strings/str0004.htm

mfG
--> stefan <--
Click to expand...
 
One more question on the same line. How do replace only exact matches. So
Update "Citadel" to "The Citedal" does not become "The The Citadel" if it was
already "The Citadel"?

--
David McKnight


David McKnight said:
The above did the trick. One last question on this, if instead of replacing
blanks and other non text I want to replace text that match case specific,
how would I define that?

ie,
CurrentDb.Execute "UPDATE [Scores] SET " & _
"[Home]=Replace([Home], 'MI', ''), " & _
"[Visitor]=Replace([Visitor], 'MI, ''), " & _
"[Location]=Replace([Location], 'MI, '')"
Click to expand...
Click to expand...

Replace "MI" with blanks but leave "mi" alone.


--
David McKnight


stefan hoffmann said:
hi David,

David said:
I used the follow code and got a syntax error. I was trying to have three
seperate fields "clean" by replacing any periods "."
Do I need any error handling on this?
Click to expand...
No, you don't need error handling, if your fields are not nullable.
If they are, you have to call the UPDATE statement for each field:
UPDATE [Scores] SET [Home]=Replace() WHERE NOT IsNull([Home])
CurrentDb.Execute "UPDATE [Scores] SET " & _
"[Home]=Replace([Home], '.', ''), " & _
"[Visitor]=Replace([Visitor], '.', ''), " & _
"[Location]=Replace([Location], '.', ''),"
Click to expand...
The last comma is causing the error, drop it.

I forgot to mention, the Replace function is not available in Access
versions prior to Access 2000. In this case use
http://www.mvps.org/access/strings/str0004.htm

mfG
--> stefan <--
Click to expand...
Click to expand...
 
hi David,

David said:
One more question on the same line. How do replace only exact matches. So
Update "Citadel" to "The Citedal" does not become "The The Citadel" if it was
already "The Citadel"?
Click to expand...
Replace() is just a simple algorithm.

Maybe you can use RegExp, which is part of VBScript. Set a reference to
Microsoft VBScript Regular Expressions. Google: RegExp VBA will give you
a lot of examples.



mfG
--> stefan <--
 
Back
Top