VBScript - Find specific term in between commas in .csv and append columns based on values found

The proper tool for .csv files is ADO. All you need to create a new (text) table by appending columns to an existing table is an SQL statement like:

SELECT S.*, 'whatever' & (IIF([B]='J','-j', '-n')) As [NewCol] INTO [dst.csv] FROM [src.csv] S

and - in general - a schema.ini file like

[src.csv]
ColNameHeader=True
Format=Delimited(;)
Col1=A Integer
Col2=B Char Width 15

[dst.csv]
ColNameHeader=True
Format=Delimited(;)
Col1=A Integer
Col2=B Char Width 15
Col3=NewCol Char Width 15

to specify your table structures unequivocally.

In code:

' Absolute path to .CSV folder
  Dim oFS   : Set oFS = CreateObject("Scripting.FileSystemObject")
  Dim sDS   : sDS     = oFS.GetAbsolutePathName("..\Data\txt")
' Connectionstring
  Dim sCS   : sCS     = Join(Array( _
       "Provider=Microsoft.Jet.OLEDB.4.0" _
     , "Data Source=" & sDS _
     , "Extended Properties=""" & Join(Array( _
            "Text" _
          , "HDR=Yes" _
     ), ";") & """" _
  ), ";")
' Database/Connection
  Dim oDb   : Set oDb = CreateObject("ADODB.Connection")
  Dim sSQL

  oDb.Open sCS

' show src
  sSQL = "SELECT * FROM [src.csv]"
  WScript.Echo sSQL
  WScript.Echo oDb.Execute(sSQL).GetString(adClipString, , vbTab, vbCrLf, "null")

' copy/append col to new dst
  If oFS.FileExists(oFS.BuildPath(sDS, "dst.csv")) Then oFS.DeleteFile oFS.BuildPath(sDS, "dst.csv")
  sSQL = "SELECT S.*, 'whatever' & (IIF([B]='J','-j', '-n')) As [NewCol] INTO [dst.csv] FROM [src.csv] S"
  WScript.Echo "--------------"
  WScript.Echo "Exec:", sSQL
  oDb.Execute sSQL

' show dst
  sSQL = "SELECT * FROM [dst.csv]"
  WScript.Echo "--------------"
  WScript.Echo sSQL
  WScript.Echo oDb.Execute(sSQL).GetString(adClipString, , vbTab, vbCrLf, "null")

output:

SELECT * FROM [src.csv]
1       J
2       N
3       J
4       N

--------------
Exec: SELECT S.*, 'whatever' & (IIF([B]='J','-j', '-n')) As [NewCol] INTO [dst.csv] FROM [src.csv] S
--------------
SELECT * FROM [dst.csv]
1       J       whatever-j
2       N       whatever-n
3       J       whatever-j
4       N       whatever-n

That way you reduce the risk of blunders like

1. polluting your code with un-used (und un-usable) variables (rowcounter)
2. trying to split an element from an array created by split on the same separator
3. accessing the next/wrong line by .ReadLine() twice

to zero

If you are looking for a simple down and dirty method...

Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Path\Original.csv", ForReading, False)
arrLines = Split(objFile.ReadAll, vbCrLf)
objFile.Close

Set objFile = objFSO.CreateTextFile("C:\Path\Appended.csv", True)

For Each strLine In arrLines
    strType = Split(strLine, ",")(4)

    Select Case True
    Case InStr(1, strType, "N", 1) > 0
        objFile.WriteLine strLine & ",Type N,USD"
    Case InStr(1, strType, "J", 1) > 0
        objFile.WriteLine strLine & ",Type J,USD"
    Case Else
        objFile.WriteLine strLine
    End Select
Next
objFile.Close

First you will want to open and read the file to a variable, you can split each line in the process. Close the file

Create a New file to write to and loop through each line. You can pull out the 5th column by doing a split and using the (#) can return only the value for that place in the array.

Do a select case checking for the string value and rewrite the line plus your two column values to the new file

close the new file when your done with the loop...

Like I said its down and dirty and may need some adjustments and modifications depending on the actual file and values being used, but it should work for your purpose.