asked on

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).

i.e.

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:

123938108,2093420348
2938108310,10293931
213912391,12301384
91010,109201
223020,1353453

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
Input.csv
Transplation.csv

Bill Prew

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>"
    WScript.Quit
Else
    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)
objFile.Close

' 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
Next

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

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
Next

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

' 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)
    Loop 
End Function

Open in new window

~bp

DebbieFost

ASKER

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.

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial