473,246 Members | 1,848 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,246 developers and data experts.

Replacing text in a text file

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:


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
  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
  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
Dec 29 '11 #1
0 4051

Sign in to post your reply or Sign up for a free account.

Similar topics

by: pankaj.khandelwal | last post by:
Hi, How can I bundle a text/gif file in the shared object. Basically I want to read the content of this text file but I cannot have it seperately on the disc. Any clues. ? Pankaj
by: David Dvali | last post by:
How can I load text from file to TextBox?
by: Suresh Tri | last post by:
Hi, I was trying to overload concat operator ||(text,text) such a way that it behaves like Oracle. i.e. I want 'abc' || null to return 'abc' instead of null. I know that it is not the expected ...
by: Johann Robette | last post by:
Hi, I'm trying to call the array_to_string function like this : SELECT array_to_string(array, '~^~') --> it comes directly from the doc. I get this error msg : ERROR: parser: parse error at...
by: David Garamond | last post by:
What is "text + text" supposed to do right now? It doesn't seem very useful to me. What about making "text + text" as an equivalent for "text || text"? Most strongly-typed programming languages do...
by: Prakash Singh Bhakuni | last post by:
am replacing the default "Browse..." button for input type=file. This works fine except that the form will only submit after the SUBMIT button is clicked twice. Any ideas on why this is happening...
by: PRITPAL | last post by:
Hi There, I want a code for Saving and Reading formatted text (RTF File) in MS Access using ole Objects, i want to save 20 such records in DB using VB 6.0. Plz Help
by: shireen Eason | last post by:
How to create a text output file from access database?
by: xtremebass | last post by:
hi every one. i am new to this forum. i have two doubts. one is , is it possible to read a text/html file in Jsp? if so, how can we find out the end of each line in the text/html file? ...
by: dlite922 | last post by:
Hey guys, I need to maintain short list of messages in a text log file. I was just googling for the best way to delete the first or last line and insert a new one. Basically with each access...
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.