CODEWITHSUNDEEP
textvbscriptreplace
Patrick Stoddard
Patrick Stoddard

Reputation: 95

Replace a number in CSV file with VBscript without replacing all text

I'm working on this code

Dim strFirm,soNumber,strValues,arrStr,strCitrix,NewText,text

strFirm = "Gray"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("cloud.csv",1,True)

Do while not objTextFile.AtEndOfStream
    arrStr = Split(objTextFile.ReadLine, ",")
If arrStr(0) = strFirm Then
    soNumber = arrStr(1)
Exit Do
End If
Loop

objTextFile.Close

strCitrix = soNumber + 1

MsgBox "Cloud Client " & strFirm & " is now using " & strCitrix & " Citrix licenses."

NewText = Replace(soNumber, soNumber, strCitrix)
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("cloud.csv",2,True)
objTextFile.Writeline NewText
objTextFile.Close

However when I run the code the replacement wipes out all the text on my file with the exception of the number I'm writing.

What I want it to do is to leave all the other text in place and only change the one specified variable.

Example 

Client1,5
Client2,7
Client3,12
Gray,6
Client4,9
Client5,17
Client6,8

And after running the script

Client1,5
Client2,7
Client3,12
Gray,7
Client4,9
Client5,17
Client6,8

Can anyone point out what I'm doing wrong?

Thank you in advance for your help.

Upvotes: 3

Views: 4100

Answers (2)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200293

Your output file contains only the number you're changing, because you extract just that number from the text you read from the file:

soNumber = arrStr(1)

increment it by one:

strCitrix = soNumber + 1

replace the number in soNumber (which contains only the number anyway) with the incremented number:

NewText = Replace(soNumber, soNumber, strCitrix)

and then write only that new number back to the file:

objTextFile.Writeline NewText

To preserve those parts of the original content that you want to keep you need to write them back to the file as well, not just the modified content.

If you read the source file line-by-line (which is a good idea when processing large files, as it avoids memory exhaustion), you should write the output to a temporary file as you go:

Set inFile  = objFSO.OpenTextFile("cloud.csv")
Set outFile = objFSO.OpenTextFile("cloud.csv.tmp", 2, True)

Do while not objTextFile.AtEndOfStream
  line = inFile.ReadLine
  arrStr = Split(line, ",")
  If arrStr(0) = strFirm Then
    soNumber = CInt(arrStr(1))
    outFile.WriteLine arrStr(0) & "," & (soNumber + 1)
  Else
    outFile.WriteLine line
  End If
Loop

inFile.Close
outFile.Close

and then replace the original file with the modified one:

objFSO.DeleteFile "cloud.csv", True
objFSO.MoveFile "cloud.csv.tmp", "cloud.csv"

However, if your input file is small, it's easier to just read the entire file, process it, and overwrite the file with the modified content:

text = Split(objFSO.OpenTextFile("cloud.csv").ReadAll, vbNewLine)

For i = 0 To UBound(text)
  If Len(text(i)) > 0 Then
    arrStr = Split(text(i), ",")
    If arrStr(0) = strFirm Then
      soNumber = CInt(arrStr(1))
      text(i) = arrStr(0) & "," & (soNumber + 1)
    End If
  End If
Next

objFSO.OpenTextFile("cloud.csv", 2, True).Write Join(text, vbNewLine)

The Len(text(i)) > 0 check is for skipping over empty lines (including a trailing newline at the end of the file), because empty strings are split into empty arrays, which would in turn make the check arrStr(0) = strFirm fail with an index out of bounds error.

Upvotes: 5

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38755

For short file, I'd prefer a .ReadAll()/RegExp strategy:

  Dim oFS    : Set oFS   = CreateObject("Scripting.FileSystemObject")
  Dim sFirma : sFirma    = "Gray"
  Dim sFSpec : sFSpec    = "..\data\cloud.csv"
  Dim sAll   : sAll      = oFS.OpenTextFile(sFSpec).ReadAll()
  Dim reCut  : Set reCut = New RegExp
  reCut.Global    = True
  reCut.Multiline = True
  reCut.Pattern   = "^(" & sFirma & ",)(\d+)"
  Dim oMTS   : Set oMTS  = reCut.Execute(sAll)
  If 1 = oMTS.Count Then
     oFS.CreateTextFile(sFSpec).Write reCut.Replace(sAll, "$1" & (CLng(oMTS(0).SubMatches(1)) + 1))
  Else
     ' handle error
  End If
  WScript.Echo oFS.OpenTextFile(sFSpec).ReadAll()

output:

Client1,5
Client2,7
Client3,12
Gray,7
Client4,9
Client5,17
Client6,8

Upvotes: 1

Related Questions