[AccessD] FW: Removing quotes

jwcolby jwcolby at colbyconsulting.com
Fri Jun 1 09:40:27 CDT 2007


Rocky,

If speed is a real concern, switch to vb.net.  You will get about a 10X
improvement in speed.  I am dealing with 10s of millions of records with >
100 fields and the switch dropped my processing from days to hours. 


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----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 10:23 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] FW: Removing quotes

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
 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list