Charlotte Foust
cfoust at infostatsystems.com
Fri Jun 1 09:57:05 CDT 2007
However, the .Net learning curve took months!! LOL
Charlotte
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, June 01, 2007 7:40 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] FW: Removing quotes
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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com