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 FileSystemObjec t (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)
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)
Code:
Dim FSO As Scripting.FileSystemObject
Dim tsFix As Scripting.TextStream
Dim tsOrig As Scripting.TextStream
Dim str_TextLine As String
Dim str_TempFile As String
Dim as_Items() As String
Dim str_OutgoingText As String
Dim lng_ItemNum As Long
Set FSO = New Scripting.FileSystemObject
If FSO.FileExists(str_SavedCSVFileName) Then
' open a new (temp) file
str_TempFile = "TMP_" & str_SavedCSVFileName
Set tsFix = FSO.CreateTextFile(str_TempFile, True)
' open original CSV file
Set tsOrig = FSO.OpenTextFile(str_SavedCSVFileName)
Do Until tsOrig.AtEndOfStream
str_TextLine = tsOrig.ReadLine
If Len(str_TextLine) > 2 And Len(str_TextLine) < 40 Then
' should be a Totals line as below:
' find 4th comma
as_Items = Split(str_TextLine, ",")
For lng_ItemNum = LBound(as_Items) To UBound(as_Items)
' when we find the 4th comma, if the next char is a number, we'll add a $ in front of it
If lng_ItemNum = 4 And IsNumeric(as_Items(lng_ItemNum)) Then
' this should be a dollar value
If InStr(as_Items(lng_ItemNum), ".") Then
str_OutgoingText = Replace(str_TextLine, as_Items(lng_ItemNum), "$" & as_Items(lng_ItemNum)) End If
End If
DoEvents
Next
' write out contents of original CSV file but replace line above with one containing $
tsFix.WriteLine Replace(str_TextLine, as_Items(lng_ItemNum), "$" & as_Items(lng_ItemNum))
Else
' write out contents of original CSV file as is
tsFix.WriteLine str_TextLine
End If
DoEvents
Loop
End If
tsOrig.Close
Set tsOrig = Nothing
tsFix.Close
Set tsFix = Nothing
If FSO.FileExists(str_TempFile) Then
FSO.CopyFile str_TempFile, str_SavedCSVFileName, True
End If