Mark A Matte
markamatte at hotmail.com
Fri Jun 1 12:02:12 CDT 2007
Rocky,
Found it easier than I thought. Me!Text1 is a field on a form where I type
the original text file name. Me!Text3 is the file name I plan on outputting
to. Also, its not 1 mil...its 100mil for the buffer size.
Hope it Helps, Good Luck,
Mark A. Matte
**************Code Start*********************
On Error GoTo Err_Command0_Click
Dim f As Long
Dim g As Long
Dim intBufferSize As Long
Dim strtemp As String
Dim intSpaceRemaining As Long
Dim OutPutLength As Long
intBufferSize = 100000000
f = FreeFile
g = FreeFile
Dim C
C = -99999999
Dim PlaceHolder
Open Me!Text1 For Binary Access Read As f
intSpaceRemaining = LOF(f)
Close f
Do Until intSpaceRemaining = 0
C = C + intBufferSize
Open Me!Text1 For Binary Access Read As f
If intBufferSize > intSpaceRemaining Then intBufferSize =
intSpaceRemaining
strtemp = Space(intBufferSize)
Get f, C, strtemp
strtemp = Replace(strtemp, "\|", "")
intSpaceRemaining = intSpaceRemaining - intBufferSize
Close f
Open Me!Text3 For Binary Access Write As g
OutPutLength = LOF(g)
Put g, OutPutLength + 1, strtemp
Close g
Loop
MsgBox "done"
Exit_Command0_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
****************Code End*****************
>From: "Mark A Matte" <markamatte at hotmail.com>
>Reply-To: Access Developers discussion and problem
>solving<accessd at databaseadvisors.com>
>To: accessd at databaseadvisors.com
>Subject: Re: [AccessD] FW: Removing quotes
>Date: Fri, 01 Jun 2007 16:41:23 +0000
>
>Rocky,
>
>I have a similar situation converting data from DB2 to Informix to SQL.
>The
>text files are typically 1 to 2 gigs. I bring in the text file 1 mil char
>at a time...search and replace...and output to a new text file...even with
>the file size...it only takes a few minutes...
>
>If you need...I can dig out the code for you.
>
>Thanks,
>
>Mark A. Matte
>
>
> >From: "Rocky Smolin at Beach Access Software" <rockysmolin at bchacc.com>
> >Reply-To: Access Developers discussion and problem
> >solving<accessd at databaseadvisors.com>
> >To: "'Access Developers discussion and problem
> >solving'"<accessd at databaseadvisors.com>
> >Subject: Re: [AccessD] FW: Removing quotes
> >Date: Thu, 31 May 2007 16:38:21 -0700
> >
> >This is a test file of 18k records. The final file will be over a
>million.
> >And it's not a one shot. So I've got to find an automated solution.
> >
> >Rocky
> >
> >
> >
> >
> >
> >
> >-----Original Message-----
> >From: accessd-bounces at databaseadvisors.com
> >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
> >Sent: Thursday, May 31, 2007 3:52 PM
> >To: 'Access Developers discussion and problem solving'
> >Subject: Re: [AccessD] FW: Removing quotes
> >
> >Can't you delete the quotes in the original file? -- I'm trying to think
> >of
> >a Find Replace pattern that might work -- how about
> >
> >Space "
> >
> >And
> >
> >" space
> >
> >That would catch every " at the beginning of a word and after -- would
>help
> >I would think. Might not get everything, but might get enough that you
> >could
> >visually catch the rest.
> >
> >Susan H.
> >
> >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
> >
> >No virus found in this incoming message.
> >Checked by AVG Free Edition.
> >Version: 7.5.472 / Virus Database: 269.8.4/825 - Release Date: 5/30/2007
> >3:03 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.4/825 - Release Date: 5/30/2007
> >3:03 PM
> >
> >
> >--
> >AccessD mailing list
> >AccessD at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/accessd
> >Website: http://www.databaseadvisors.com
>
>_________________________________________________________________
>Need a break? Find your escape route with Live Search Maps.
>http://maps.live.com/default.aspx?ss=Restaurants~Hotels~Amusement%20Park&cp=33.832922~-117.915659&style=r&lvl=13&tilt=-90&dir=0&alt=-1000&scene=1118863&encType=1&FORM=MGAC01
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
_________________________________________________________________
PC Magazine’s 2007 editors’ choice for best Web mail—award-winning Windows
Live Hotmail.
http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migration_HM_mini_pcmag_0507