By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,968 Members | 1,852 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Replacing text in a text file

P: 1
I needed to replace some text in several string in a CSV file, so what I did was, I opened the original file as a TextStream using the FileSystemObject (set a reference to Microsoft Scripting Runtime). I also created a new empty text file. Now I walk through the original file and write a copy of each line out to my new text file. If I come to a line with the string I'm searching for, I write the corrected new line of text into my new text file.

Keep going to the end of the text file(s). When you're done, save the new text file, delete the original file, and rename the new file to the old one's filename. (I didn

To explain the sample code below, I was searching for a particular string (in this case I was looking for lines like this:

,,R01,222,14876.56,,,,,,

where I wanted to add a dollar sign to the 5th field (14876.56)





Expand|Select|Wrap|Line Numbers
  1.     Dim FSO              As Scripting.FileSystemObject
  2.     Dim tsFix            As Scripting.TextStream
  3.     Dim tsOrig           As Scripting.TextStream
  4.     Dim str_TextLine     As String
  5.     Dim str_TempFile     As String
  6.     Dim as_Items()       As String
  7.     Dim str_OutgoingText As String
  8.     Dim lng_ItemNum      As Long
  9.  
  10.  
  11. Set FSO = New Scripting.FileSystemObject
  12.     If FSO.FileExists(str_SavedCSVFileName) Then
  13.         ' open a new (temp) file
  14.         str_TempFile = "TMP_" & str_SavedCSVFileName
  15.         Set tsFix = FSO.CreateTextFile(str_TempFile, True)
  16.         ' open original CSV file
  17.         Set tsOrig = FSO.OpenTextFile(str_SavedCSVFileName)
  18.             Do Until tsOrig.AtEndOfStream
  19.                 str_TextLine = tsOrig.ReadLine
  20.                     If Len(str_TextLine) > 2 And Len(str_TextLine) < 40 Then
  21.                         ' should be a Totals line as below:
  22.                         ' find 4th comma
  23.                         as_Items = Split(str_TextLine, ",")
  24.                             For lng_ItemNum = LBound(as_Items) To UBound(as_Items)
  25.                                 ' when we find the 4th comma, if the next char is a number, we'll add a $ in front of it
  26.                                 If lng_ItemNum = 4 And IsNumeric(as_Items(lng_ItemNum)) Then
  27.                                     ' this should be a dollar value
  28.                                     If InStr(as_Items(lng_ItemNum), ".") Then
  29.                                         str_OutgoingText = Replace(str_TextLine, as_Items(lng_ItemNum), "$" & as_Items(lng_ItemNum))                                                                            End If
  30.                                 End If
  31.                                 DoEvents
  32.                             Next
  33.                         ' write out contents of original CSV file but replace line above with one containing $
  34.                         tsFix.WriteLine Replace(str_TextLine, as_Items(lng_ItemNum), "$" & as_Items(lng_ItemNum))
  35.                     Else
  36.                         ' write out contents of original CSV file as is
  37.                         tsFix.WriteLine str_TextLine
  38.                     End If
  39.                 DoEvents
  40.             Loop
  41.     End If
  42.  
  43.     tsOrig.Close
  44.     Set tsOrig = Nothing
  45.     tsFix.Close
  46.     Set tsFix = Nothing
  47.     If FSO.FileExists(str_TempFile) Then
  48.         FSO.CopyFile str_TempFile, str_SavedCSVFileName, True
  49.     End If
  50.  
  51.  
Dec 29 '11 #1
Share this Article
Share on Google+