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
DebbieFostAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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>"
    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
0
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.
0
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

~bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Batch

From novice to tech pro — start learning today.