Updating Multiple Fields Independently
-
I suspect I won't like the answer to this, but here's what I've got...
I have a moderate sized set of data I received from an external source. The data needs "cleansing." It was sent to me in tab-delimited text files, so the data originator had to use a placeholder to indicate missing values. They happened to use underscores ("_"). I started by importing the data into Access as text fields. So part of my job in this deal is that I have to remove all the underscores from every text field across the 50 or 60 fields in 6 to 8 tables.
So my question, simply, is how do I do this with plain, vanilla SQL, without hurting my typing fingers? Unfortunately, the data must remain in Access for now so please assume no special SQL Server features are available. I know, I know, it isn't strictly an SQL Server question, but it's related, and this is something I would like to know how to do in Server as well.
Thanks!
-
this is untested but i think should work...
update tblMyTable
set col1 = replace(col1, '_', '')
where (PATINDEX('_', col1) > 1)
oops - just realized you said ACCESS - maybe you could use ascii values to replace...?! and you could find a substitute for PATINDEX() hopefully ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
I think Sushila meant :
Update TblName
set col1 = IIF(col1 = '_', NULL, col1)
I'll see if I can come up with a way to generate the statements automatically.
-
Excellent! Either of your solutions seems to work but I do think the IIF will be a bit faster. I tried the REPLACE() solution and it does work, although it whines a lot when it encounters nulls.
I was approaching it from a WHERE clause standpoint, which, of course, was completely wrong. Thanks both for your help!
And Remi, I'll keep an eye on this to see if you think of a way to generate this automatically. I could probably write some VBA to write the SQL string, but at "only" 60 fields or so I tend to doubt it's worth the trouble. If you come up with something better, let me know.
Thanks again.
-
1 - Don't use the replace function, it will update good data too eg : Some_name will become somename.
This should solve your problem in case you need to redo this again sometime :
Public Sub RepairData()
Dim Tables() As String
Dim sRepairList As String
Dim i As Integer
Dim MaxI As Integer
Dim MyRs As ADODB.Recordset
Dim MyField As ADODB.Field
Dim SQLQ As String
Set MyRs = New ADODB.Recordset
'list of tables to process
sRepairList = "Test;Test2;Test3"
Tables = Split(sRepairList, ";")
MaxI = UBound(Tables)
For i = 0 To MaxI
MyRs.Open "Select * from " & Tables(i) & " where 1 = 0", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
SQLQ = "UPDATE " & Tables(i) & " SET "
For Each MyField In MyRs.Fields
'checks varchars only
If MyField.Type = 202 Then
SQLQ = SQLQ & MyField.Name & " = IIF(" & MyField.Name & " = '_', NULL, " & MyField.Name & "), "
End If
Next
SQLQ = Left(SQLQ, Abs(Len(SQLQ) - 2))
'Debug.Print SQLQ
MyRs.Close
CurrentProject.Connection.Execute SQLQ
Next
End Sub
-
Nice!
Might there be a way so that function could be called by an Access query (so any dufus could just double-click qry_Remove_Underscores and it runs)?
-
This is a function... just call it on a click of a button. You might also make a listbox that loads all the table names and create the "to clean list" from that.
-
I could do that but this doesn't have a front-end. I'm just trying to organize all my data integration and cleanup and such into step-by-step queries (eventually the whole database could be integrated and cleaned with a single macro I guess). I was just hoping I could execute the function in the Access Database window similarly to executing a query by double-clicking.
-
You can create a macro that will call that function and call it cleaning for dufus
. -
I know nothing about Access but could you not run a macro to do this for you?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
Remi - WHAT is your typing speed ?! And man - you're ALL OVER THE PLACE - like some sort of ubiquitous spook....
cleaning for dufus - clever that!!!!
**ASCII stupid question, get a stupid ANSI !!!** - Thanks, will do.
-
NP.
Sushila I'm not sure you can comprehend that number. It's almost impossible to measure.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply