MS Access Forum / Modules / DAO / VBA / September 2005
replacing text in textfile
|
|
Thread rating:  |
Jesper - 01 Sep 2005 17:05 GMT I have a text file that's 20 lines long. I specifically need to replace the word "no" to "yes" on line 10 of the file. Line 10 reads: "Locked=No" and needs to be "Locked=Yes"
Is that possible? Can it be done with VBA I/O or do I need the Scripting Runtime? Thanks for any input.
Jesper, Denmark
Dirk Goldgar - 01 Sep 2005 17:34 GMT > I have a text file that's 20 lines long. > I specifically need to replace the word "no" to "yes" on line 10 of [quoted text clipped - 6 lines] > > Jesper, Denmark You can do it with simple VBA I/O. Quick, untested "air code" might look like this:
Dim intFile As Integer Dim strFileText As String Dim astrLines() As String
Const conFilePath As String = "C:\Temp\SomeFile.txt"
intFile = FreeFile
' Since it's a small file, we'll read it all at a gulp. Open conFilePath For Input As #intFile strFileText = Input(LOF(intFile), intFile) Close #intFile
' Split the file text into lines. astrLines = Split(strFileText, vbCrLf)
' Replace "Locked=Yes" on the tenth line. astrLines(9) = Replace(astrLines(9), "Locked=Yes", "Locked=No")
' Join the lines back together with carriage return+line feed ' characters between them. strFileText = Join(astrLines, vbCrLf)
' Write the text back out. ' NOTE: I'm writing this to the same file. ' You may not want to do that. Open conFilePath For Output As #intFile Print #intFile, strFileText; Close #intFile
 Signature Dirk Goldgar, MS Access MVP www.datagnostics.com
(please reply to the newsgroup)
Tim Ferguson - 01 Sep 2005 18:08 GMT > I have a text file that's 20 lines long. > I specifically need to replace the word "no" to "yes" on line 10 of [quoted text clipped - 3 lines] > Is that possible? Can it be done with VBA I/O or do I need the > Scripting Runtime? open "myoldfile.txt" for input as #InFile open "mynewfile.txt" for output as #outfile
for j = 1 to 20 input line #infile, strTempLine if j = 10 then strTempLine = Replace(strTempLine, "Locked=No", "Locked=Yes") end if
print #outfile, strTempline next j
close #outfile close #infile
Kill "myoldfile.txt" ' can't remember the syntax for this Rename "myNewFile.txt", "myoldfile.txt"
Isn't it easier to use TextPad or something?
Hope that helps
Tim F
Jesper - 01 Sep 2005 20:11 GMT > open "myoldfile.txt" for input as #InFile > open "mynewfile.txt" for output as #outfile [quoted text clipped - 15 lines] > ' can't remember the syntax for this > Rename "myNewFile.txt", "myoldfile.txt" Great, thanks very much to both of you.
Jesper, Denmark
Douglas J. Steele - 01 Sep 2005 23:07 GMT <picky> Line Input, not Input Line </picky>
 Signature Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!)
>> I have a text file that's 20 lines long. >> I specifically need to replace the word "no" to "yes" on line 10 of [quoted text clipped - 29 lines] > > Tim F Jesper - 02 Sep 2005 11:52 GMT > <picky> > Line Input, not Input Line > </picky> I actually had some trouble with so thanks.
Jesper F, Denmark
Tim Ferguson - 02 Sep 2005 17:34 GMT > <picky> > Line Input, not Input Line > </picky> <moment class="senior"> Thank you Matron -- here come my tablets. </moment>
All the best
Tim F
Jack - 13 Sep 2005 13:37 GMT Tim, I am looking for a very similar procedure. What I am wanting to do is use this procedure to replace text in a html file. I created a 'temp' txt file that has all of the sections (3) cthat need to be replaced with ##replace1##,replace2##,##replace3##. I am hoping that I can have the replace strings stored in a table. The ##replace## are throughout the text file - no particular line. I need to do a replace all with the ##replace## string. I am then needing to have the save as name stored in table. I would like to push a command button on form and have access create 300+ html files on the fly. I believe I can adapt paqrt of your code, however I am confused as to how set it to replace anywhere it finds ##replace?## in text file, much less how to instruct it to do same thing for next ##replace2## etc. Any help would be greatly appreciated. Thanks, Jack
> > I have a text file that's 20 lines long. > > I specifically need to replace the word "no" to "yes" on line 10 of [quoted text clipped - 32 lines] > > Tim Ferguson - 13 Sep 2005 20:36 GMT > I am looking for a very similar procedure. I don't think it is very similar: the OP, as I remember it, had incredibly tight requirements that made it easy to offer a simple, but hopelessly importable, solution.
My first reaction to any kind of generic text manipulation, especially when it comes to html tags and the like, is to get the PERL book out. If you really want to use VB and its cousins, the built in Scripting.Regex is nearly as flexible, but be prepared for a steep learning curve.
If you have really fixed targets, you can use Instr() and Replace() to find them. The other problem with html type text is that you cannot expect to find nice line-endings in appropriate places, and you have to work very hard to make sure you don't miss having more than one target in the same line.
Best of luck
Tim F
Jack - 14 Sep 2005 14:51 GMT Tim, Thank you for the reply. These are TRULY fixed targets. Generally the ##replace1## field within the txt file needs to replaced with one - three words MAX. If this was a onesy twosey (did i spell that right:) )I wouldnt have a problem. But 100+ pages need to be updated with every file having different data. I created a template, went through and made the targets ##replace1##, ##replace2## and ##replace3##. I then saved this as a text file.
During my endless attempts to figure out how to do this. I have taken the following steps. I just need assistance putting it together.
I created a table named tbl_replace - 3 text fields named replace1,2,etc I added a fourth field as to what I will need the output filename to be. I created a form with a command button and inserted the following code.
Sub ReplaceFile(Tempfile As String, ReplaceFile As String, Offset As Integer) On Error GoTo Err_ReplaceFile
Dim db As DAO.Database Dim Directory As String Dim MyString As String Dim NewString As String Dim MyChar As String Dim TagFound As Boolean Dim i As Integer
Set db = CurrentDb Directory = Mid(db.Name, 1, Len(db.Name) - Len(Dir(db.Name)))
Open Directory & "\" & Tempfile For Input As #1 !This works fine Open Directory & "\" & ReplaceFile For Output As #2! I havent figured out how to get access to loop through all of my records. Maybe a d lookup? TagFound = False
'loop through text file Do While Not EOF(1) NewString = "" 'read one line Line Input #1, MyString Do Until instr(MyString) = 0 'walk down the line !This is part I am confused about something like: replace(mystring,##replace1##,[field from tbl_replace]) replace(mystring,##replace2##,[field from tbl_replace]) replace(mystring,##replace3##,[field from tbl_replace]) Loop 'Output the line Record Print #2, NewString ! is this best way to do this? Loop
MsgBox "Done"
Exit_ReplaceFile: Close #2 Close #1 Set db = Nothing Exit Sub
Err_ReplaceFile: If Err.Number = 62 Then Resume Exit_ReplaceFile Else MsgBox Err.Number MsgBox Err.Description Resume Exit_ReplaceFile End If
End Sub
Any assistance would be greatly appreciated. Thanks again, Jack
> > I am looking for a very similar procedure. > [quoted text clipped - 16 lines] > > Tim F Jack - 14 Sep 2005 16:13 GMT Ok... I believe I am getting somewhere. I scoured the net and found the following code. I created a sample form with 3 text boxes (Text1,Text2,Text3) I created 2 buttons(replace text and replacefile) Replacetext give me and error 2185 focus error ReplaceFile does indeed creat the replacement file - but nothing is in it. Can someone Please take a look at the code and give me a clue as to what may be going on. I am really trying to learn and am not asking to have code rewritten.
Thanks, Again Jack Code Follows:
This is a simple text replacement example. It will also work on Binary files since I included the description on how to make it reusable from a form with 3 text boxes and how to read in a full binary or ascii file as a single string. Usage:
Private Sub cmdReplaceText_Click() 'replaces all occurances of Text2 in Text1 with Text3 Call ReplaceTextInTextBox(Text1, Text2, Text3) End Sub Private Sub cmdReplaceFile_Click() 'replaces all occurances of "hello" with "goodbye" in c:\test.txt, and saves the new file as c:\test_replace.txt Call ReplaceInFile("c:\test.txt", "c:\test_replace.txt", "hello","goodbye") End Sub
Place the code below in a module
Public Function ReplaceTextInTextBox(MyTextBox As Object,_ TextOld As Object, TextNew As Object) 'The object reference is made form the form 'you use to call this module. 'Assumed you have a text1, text2 and text3 text box on your form 'where 1 is used for the text you wish to replace text in '2 is the one containing text you are searching for '3 is the new text you wish to see entered in its place. 'The replacement function being called
Call ReplaceText(MyTextBox.Text, TextOld.Text, TextNew.Text)
'Replace textview and refresh your form
MyTextBox.Text = ReturnValue MyTextBox.Refresh
End Function
Public Function ReplaceInFile(InputFile As String, _ OutputFile As String, OldT As String, NewT As String) As String Dim Fnum As Integer ' get a FreeFile number Dim FileLength As Long 'Just in case its really big Dim TheString As String
'I open the file as binary to avoid some 'complications with reading in a full file. 'This just so I can skip some trouble shooting with 'some special characters in certain exe files. 'It might be that character you wish to replace.
Fnum = FreeFile Open InputFile For Binary As #Fnum ' Open file. FileLength = LOF(Fnum) ' Get length of file.
TheString = Input(FileLength, #Fnum)
Close #Fnum ' Close file.
'OldT = Whatever text you send form a form or function to search for 'NewT = Whatever text you wish to replace OldT with
'Call the function to replace text
Call ReplaceText(TheString, OldT, NewT)
'Now print the result to a new file 'so you dont overwrite your original
Fnum = FreeFile Open OutputFile For Output As #Fnum ' Open file. Print #Fnum, ReturnValue ' the ReturnValue from the replacement Close #Fnum ' Close file.
End Function
Public Function ReplaceText(CleanThis As String, _ OldText As String, _ NewText As String)
'To get the len of the string Dim StrLn As Long
'To split the string Dim PartA As String, PartB As String
'If search string is found, get its start position Dim FoundP As Long Dim OldLn As Long On Error GoTo ErrHandle
'Set a value for the len of the old text to be replaced OldLn = Len(OldText) StrLn = Len(CleanThis)
'Loop through the string until all occurences are eliminated
Do While InStr(1, CleanThis, OldText) <> 0 FoundP = InStr(1, CleanThis, OldText)
'Get PartA of the string (before found occurance) PartA = Left(CleanThis, FoundP - 1)
'Get PartB of the string (after found occurance) PartB = Right(CleanThis, StrLn - FoundP - OldLn + 1)
'*NOTE ON THE ADDITION AND SUBTRACTION** '****************************************************** '+ 1 to avoid a skip in adding found len and old len 'The previous line could also be written ' 'PartB = Right(CleanThis, StrLn - (FoundP + OldLn - 1)) '****************************************************** 'REBUILD THE STRING BEFORE NEXT LOOP 'This adds a space before and after NewText 'and trims out unneccessary spaces too! CleanThis = Trim(PartA) & " " & _ Trim(NewText) & " " & _ Trim(PartB) 'GET NEW LEN BEFORE LOOPING StrLn = Len(CleanThis) Loop
'Set result of the function ReplaceInString = CleanThis
'Avoid error handling Exit Function 'Add your own error code hereafter ErrHandle: Select Case Err.Number Case Err.Number MsgBox "Your function executed with an error " & Err.Number _ & vbCrLf & vbCrLf & Err.Description, vbExclamation, _ "Error " & Err.Number Err.Clear End Select Resume Next End Function
> Tim, > Thank you for the reply. These are TRULY fixed targets. Generally the [quoted text clipped - 93 lines] > > > > Tim F Tim Ferguson - 14 Sep 2005 18:18 GMT > Please take a look at the code and give me a clue as > to what may be going on. See below: I'm not very impressed with the standard of coding going on here...
> I am really trying to learn and am not asking > to have code rewritten. I guess it probably needs it...
> Call ReplaceText(MyTextBox.Text, TextOld.Text, TextNew.Text) This is garbage and never, ever, could have worked. The text property is only available when a control has the focus, and three controls cannot have the focus at the same time. In addition, there is no check that the passed objects are text boxes (or listboxes, or combos..?)
The .Value property would have worked here.
> Public Function ReplaceText(CleanThis As String, _ > OldText As String, _ > NewText As String) It's not strictly illegal, but it's really shabby to declare a function without a return: this should be a Sub not a Function.
> Do While InStr(1, CleanThis, OldText) <> 0 Unfortunately, this is going to fall apart badly if the target text is a substring of the inserted text. Try
ReplaceText(ExampleText, "open", "unopened")
and wait for a very long time...
> 'REBUILD THE STRING BEFORE NEXT LOOP > 'This adds a space before and after NewText [quoted text clipped - 3 lines] > Trim(NewText) & " " & _ > Trim(PartB) This is pretty wanton inserting and removing white space, which may or may not be appropriate depending on the user's needs...
In my view, you probably need to start again, this time with a properly working algorithm. If I had to do it, I'd use a tool other than VBA; but I do recognise that I don't really understand what it is you are trying to do.
Best wishes
Tim F
Jack - 14 Sep 2005 18:44 GMT Thank you again Tim for they reply. Maybe I should start from stratch as to what I am looking for.
I have a .txt file with 3 variables that are located throughout the file (##replace1##,##replace2##,etc) These are FIXED points. I am wanting to have the table I have with 4 fields (one for each replace# and one for what the output filename should be) I am wanting to have a command button on form that when clicked will go through text file and replace ALL instances of ##replace1## with [replace1.tbl_replace] and do the same thing for ##replace2## and ##replace3## After its complete I want it saved as [saveitas_tbl_replace] Two things are important 1) It needs to loop through ALL the records I believe I would use a dcount and dlookup to do that. A new file would be created for EACH record. 2) the text that is replaced does not include any additional whitespace (ie i want ##replace1## apples -> i want [green] apples OR I want [to buy more] apples.)
In meantime I will look at your suggestions. Again thanks for your help. Jack
> > Please take a look at the code and give me a clue as > > to what may be going on. [quoted text clipped - 53 lines] > > Tim F Dirk Goldgar - 14 Sep 2005 19:02 GMT > It's not strictly illegal, but it's really shabby to declare a > function without a return: this should be a Sub not a Function. I know what you're getting at, Tim, and I would agree except that Access has a particular feature that makes declaring "conceptual Subs" as Functions very attractive in some cases. Specifically, you can call a function directly from the event property of a form, report, or control, using the syntax
=FunctionName(arguments ...)
Any return value from the function is discarded.
This can come in very handy, both for creating lightweight, module-less forms that nevertheless incorporate programmed behaviors, and for easily sharing a behavior among many controls on a form without creating an event procedure for each control. You can't do the same thing with a Sub.
That's not to say that this consideration applies in the OP's specific case. I'm just debating your blanket statement.
 Signature Dirk Goldgar, MS Access MVP www.datagnostics.com
(please reply to the newsgroup)
Dirk Goldgar - 14 Sep 2005 19:19 GMT > Ok... I believe I am getting somewhere. I scoured the net and found > the following code. I created a sample form with 3 text boxes [quoted text clipped - 4 lines] > as to what may be going on. I am really trying to learn and am not > asking to have code rewritten. [...]
> Public Function ReplaceText(CleanThis As String, _ > OldText As String, _ > NewText As String) If you're using Access 2000 or later, there's not much point to this function. The built-in Replace function that is part of the version of VBA that ships with Access 2000 will do the job better.
 Signature Dirk Goldgar, MS Access MVP www.datagnostics.com
(please reply to the newsgroup)
jussij@zeusedit.com - 14 Sep 2005 00:51 GMT I would suggest using a tool like 'sed' to do this:
http://www.student.northpark.edu/pemente/sed/
Jussi Jumppanen Author: Zeus for Windows http://www.zeusedit.com/lookmain.html
|
|
|