• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 939
  • Last Modified:

Require a script to find and replace values in a CSV file those from a TXT file

I have a CSV file that contains two fields per row, and the first field MAY contain a a value that needs to be replaced with a number from a translation table CSV).


C:\INPUT.CSV contains the following:

1233456,09/06/13 - 21:24 - Note to file - John Smith
89012345,09/06/13 - 21:24 - TItle - Jackson Jones
ZMOD91010,09/06/13 - 21:24 - Notices - Greg Whote
ABC223020,09/06/13 - 21:24 - Summary - Frank Bray

C:\Translation.CSV contains the following:


I need a script to do the following:

1. Where Field1 in C:\Input.csv contains any prefixed alpha characters, remove them from the field to leave the number only in FIELD1

2. Where any FIELD1 value in C:\Input.csv contains a number that is in Field1 of Translation.CSV, replace that value with the referenced value in FIELD2  of Translation.CSV

The output will then be written to C:\Output.CSV (using the above examples):

1233456,09/06/13 - 21:24 - Note to file - John Smith
89012345,09/06/13 - 21:24 - TItle - Jackson Jones
109201,09/06/13 - 21:24 - Notices - Greg Whote
1353453,09/06/13 - 21:24 - Summary - Frank Bray
  • 2
1 Solution
Bill PrewCommented:
Okay, I think this does what you described. Save as a VBS file, and then run as follows.

cscript EE28248438.vbs control.txt in.txt out.txt

Where control.txt is the name of your translation file, and then the names of the input and output files follow that.

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
Const Delim = ","
' Get input and output file names from command line
If (WScript.Arguments.Count < 3) Then
    WScript.Echo "Usage: " & Wscript.ScriptName & " <mapping-file1> <input-file> <output-file>"
    strMapFile = WScript.Arguments(0)
    strInpFile = WScript.Arguments(1)
    strOutFile = WScript.Arguments(2)
End If

' Create dictionary object to locate matches
Set dicMap = CreateObject("Scripting.Dictionary")

' Read mapping file into an array
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strMapFile, ForReading, False, TriStateUseDefault)
arrLine = Split(objFile.ReadAll, VbCrLf)

' Loop through all lines of the file, load into a dictionary
For Each strLine In arrLine
    If strLine <> "" Then
        arrField = Split(strLine, ",")
        If Not dicMap.Exists(arrField(0)) Then
            dicMap.Add arrField(0), arrField(1)
        End If

    End If

' Read input file into an array
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strInpFile, ForReading, False, TriStateUseDefault)
arrLine = Split(objFile.ReadAll, VbCrLf)

For i = 0 To UBound(arrLine)
    If arrLine(i) <> "" Then
        arrField = Split(arrLine(i), ",")
        arrField(0) = RemoveLeadingChars(arrField(0))
        If dicMap.Exists(arrField(0)) Then
            arrField(0) = dicMap.Item(arrField(0))
        End If
        arrLine(i) = Join(arrField, ",")
    End If

' Output file with any changes made
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.WriteLine Join(arrLine, vbCrLf)

' Remove any leading non-numeric characters from a string
Function RemoveLeadingChars(strText)
    RemoveLeadingChars = strText
    Do While Left(RemoveLeadingChars, 1) < "0" Or Left(RemoveLeadingChars, 1) > "9"
        RemoveLeadingChars = Mid(RemoveLeadingChars, 2)
End Function

Open in new window

DebbieFostAuthor Commented:
That is awesome Bill - initial testing looks great. Is it possible to code it so it will CREATE a new file called OutPut.CSV? The environment this is in will always move the Output.CSV file out of the folder, so there will not be an existing file to write to.
Bill PrewCommented:
Not sure I understand, it already creates whatever the third file is on the command line as the output file.  So you would just invoke this script as:

cscript EE28248438.vbs control.txt input.csv output.csv

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now