Rocky Smolin at Beach Access Software
rockysmolin at bchacc.com
Fri Jun 1 09:23:02 CDT 2007
Dear list:
In case anyone might find it useful, here are the three routines I wrote to
clean the quotes from the db.
The first routine strips the quotes from the field names which came in quote
delimited from the import wizard.
Private Sub cmdStripQuotes_Click() strips the leading and trailing quotes
from any field that has them.
Finally, Private Sub cmdReplaceQuoteWithIn_Click() replaces all occurrences
of quotes with 'in.' in the description field.
Any suggestions for refinements welcome. The routines work but could
probably be optimized. The table had 18,000+ records for a test, but the
real data will have hundreds of thousands so quicker is better.
BTW the table contains 104 fields.
Thanks and happy Friday,
Rocky
Option Compare Database
Option Explicit
Dim db As DAO.Database
Dim rsIn As DAO.Recordset
Dim fld As DAO.Field
Dim tdf As DAO.TableDef
Dim obj As Object
Dim intI As Integer
Dim lngRecNum As Long
Private Sub cmdChangeFieldNames_Click()
Set db = CurrentDb
Set tdf = db.TableDefs("tblPOLINEAPR2007")
For Each obj In tdf.Fields
If Left(obj.Name, 1) = """" Then
'MsgBox obj.Name & " - *" & Mid(obj.Name, 2, Len(obj.Name) - 2) &
"*"
obj.Name = Mid(obj.Name, 2, Len(obj.Name) - 2)
Else
'MsgBox obj.Name & " No quotes "
End If
Next obj
db.Close
Set tdf = Nothing
Set db = Nothing
End Sub
Private Sub cmdReplaceQuoteWithIn_Click()
Set db = CurrentDb
Set rsIn = db.OpenRecordset("Select * FROM tblPOLINEAPR2007")
rsIn.MoveLast
Me.txtNumRecs = rsIn.RecordCount
rsIn.MoveFirst
lngRecNum = 0
Do While Not rsIn.EOF
lngRecNum = lngRecNum + 1
If lngRecNum / 1000 = Int(lngRecNum / 1000) Then
Me.txtRecNum = lngRecNum
Me.Repaint
End If
If InStr(1, rsIn!Description, """") <> 0 Then
rsIn.Edit
rsIn!Description = rsIn!Description & vbCrLf &
Replace(rsIn!Description, """", " in. ")
rsIn.Update
End If
rsIn.MoveNext
Loop
MsgBox "Done"
rsIn.Close
Set rsIn = Nothing
db.Close
Set db = Nothing
End Sub
Private Sub cmdStripQuotes_Click()
Set db = CurrentDb
Set rsIn = db.OpenRecordset("Select * FROM tblPOLINEAPR2007")
rsIn.MoveLast
Me.txtNumRecs = rsIn.RecordCount
rsIn.MoveFirst
lngRecNum = 0
Do While Not rsIn.EOF
lngRecNum = lngRecNum + 1
If lngRecNum / 100 = Int(lngRecNum / 100) Then
Me.txtRecNum = lngRecNum
Me.Repaint
End If
For intI = 0 To rsIn.Fields.Count - 1
'MsgBox intI & " *" & rsIn(intI) & "*"
If Left(rsIn(intI), 1) = """" And Right(rsIn(intI), 1) = """" Then
rsIn.Edit
rsIn(intI) = Trim(Mid(rsIn(intI), 2, Len(rsIn(intI)) - 2))
rsIn.Update
End If
Next intI
rsIn.MoveNext
Loop
rsIn.Close
Set rsIn = Nothing
db.Close
Set db = Nothing
MsgBox "Done"
End Sub
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin at
Beach Access Software
Sent: Friday, June 01, 2007 6:10 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] FW: Removing quotes
Erwin:
Thanks. I'll take a look.
Regards,
Rocky
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Erwin Craps - IT
Helps
Sent: Friday, June 01, 2007 5:05 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] FW: Removing quotes
I have written a long time ago some little vb app to remove some strings
from files in a folder.
At that day someone needed to strip HTML code from a lot htm files.
It only removes charachters not replace them.
www.ithelps.eu/tools
I noticed that the tool is regulary downloaded so is upose it has his use
:-)
Erwin
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin at
Beach Access Software
Sent: Friday, June 01, 2007 12:36 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] FW: Removing quotes
Dear List:
A client has a large tab delimited file which I'm trying to import into an
access table using the wizard. The text fields in the tab delimited file
have quotes around them. Unfortunately the Description field has embedded
quotes for descriptions with lengths - i.e. "Suture 3 1/2" ".
So if I import with the quote as text qualifier any record with a quote in
the description field is unparseable because there's an uneven number of
quotes and all of the subsequence fields after the Description are dropped.
If I import with no character as the text qualifier I get all the fields but
all the text fields are surrounded by quotes. So I have to remove them.
I'm thinking to do it with a couple update queries but there are about 140
fields so it would be awkward and a bit time consuming to structure and test
the update queries.
I'm thinking of doing it with a bit of code - cycle through the fields for
each record and strip the quotes (also replace the quote that represents
inches with 'in.')
I think it would take an hour to do it in code. But is there any faster,
easier, slicker way I'm overlooking?
MTIA
Rocky
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.472 / Virus Database: 269.8.5/826 - Release Date: 5/31/2007
4:51 PM
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.472 / Virus Database: 269.8.5/826 - Release Date: 5/31/2007
4:51 PM