Script searches for a code then looks in a position and writes a new number in the content

I am working with a .csv file and I need to change some of the contents.  - let's name it newfile.csv
Below is an example of the content.  These are only 2 lines, however there could be multiple lines in one .csv file.
In this sample, each line starts with 9,J.. and in this case ends with 002.. however each line can be different..

I would like a simple .bat script which will look at position 4 first - if it sees 65-ABCDEF then it looks at position 101.
If position 101 is blank, then it looks in position 100, and looks at tast 6 digits of position 100,  takes those six numbers - removes the last didit, and
write the 5 digits in position 101 - without touching the rest of the data and saving it to a new file called newfile1.csv

So in the examples below, since in position 4 it sees 65-ABCDEF, it then looks in position 101 and sees that it's blank.. therefore it looks at position 100.
There in the first line it sees the number 12345678123452 - therefore it will look at the last 6 digits.. being 123452, remove the last digit, and then it will write those digits
in position 101.. so side by side positions 100 and 101 would look like this before it moves on to the next line..   12345678123452,12345,

Thanks in advance for any assistance you can provide.


9,J,01234,65-ABCDEF,N,1234/2,12345612314,,20190220,20190319,20190319,,1234567890000,Company Inc.,987 Street,,Somewhere,AB,ABC DEF,AM,,,1234567891234,ABC 1234,1 Boulevard,,Here,AB,ABC FGH,AM,,,9876543219876,CORP,0001-,AMA,Y,,,,,,,,,,N,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,20190313,,,,,,,12345678123452,,123456,Desc,,,,10.00,AM,0.00,BC,0.00,AK,10.0100,0.0000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,001
9,J,01234,65-ABCDEF,N,1234/2,12345612314,,20190220,20190319,20190319,,1234567890000,Company Inc.,987 Street,,Somewhere,AB,ABC DEF,AM,,,1234567891234,ABC 1234,1 Inkster Boulevard,,Here,AB,ABC FGH,AM,,,9876543219876,ORP,0001-,AMA,Y,,,,,,,,,,N,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,20190313,,,,,,,1234567867892,,789123,Desc,,,,11.00,AM,0.00,BC,0.00,AK,11.0200,0.0000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,002
100questionsAsked:
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.

John TsioumprisSoftware & Systems EngineerCommented:
Check this script  VBScript (untested...maybe needs some tweaking)
dim inputFilePath,outputFilePath
dim outputFile,fso,inputFile
Set fso = CreateObject("Scripting.FileSystemObject") 
inputFilePath = GetFileName("C:\Temp\", "Csv files|*.csv")
set inputFile = fso.OpenTextFile(inputFilePath)
outputFilePath = mid(inputFilePath,1,InStr(strReverse(inputFilePath),"\")-2)
outputFilePath=outputFilePath & "output.csv"

Set outputFile = fso.CreateTextFile(outputFilePath,  True)
Do While inputFile.AtEndOfStream <> True
    arr = Split(inputFile.ReadLine, ",")
	
    if arr(3) = "65-ABCDEF" Then
	if len(arr(100))=0 then 
	
	arr(100) =left(right(arr(99),6),5)
	
	end if
	end if
    outputString = Join(arr, ",")
    outputFile.WriteLine outputString
Loop

Function GetFileName( myDir, myFilter )
  ' Written by Rob van der Woude
  ' http://www.robvanderwoude.com

  ' Standard housekeeping
  Dim objDialog

  ' Create a dialog object
  Set objDialog = CreateObject( "UserAccounts.CommonDialog" )

  ' Check arguments and use defaults when necessary
  If myDir = "" Then
    ' Default initial folder is "My Documents"
    objDialog.InitialDir = CreateObject( "WScript.Shell" ).SpecialFolders( "MyDocuments" )
  Else
    ' Use the specified initial folder
    objDialog.InitialDir = myDir
  End If
  If myFilter = "" Then
    ' Default file filter is "All files"
    objDialog.Filter = "All files|*.*"
  Else
    ' Use the specified file filter
    objDialog.Filter = myFilter
  End If

  ' Open the dialog and return the selected file name
  If objDialog.ShowOpen Then
    GetFileName = objDialog.FileName
  Else
    GetFileName = ""
  End If
End Function

Open in new window

Bill PrewIT / Software Engineering ConsultantCommented:
Here is a VBS script approach that got the job done in a test here.  Your 100 column reference didn't match the data though, so I adjusted for that.  My output file from a test here attached also.  Keep in mind that arrays are zero based in VBS, so column 4 has an array index of 3, etc.

Save this as a VBS file, and then execute as follows from a command line:

cscript EE29136743.vbs filein.txt fileout.txt

or

cscript EE29136743.vbs filein.txt

The first will read from filein.txt and write to fileout.txt.  The second will read from filein.txt and write the update version of the data back to that same file, overwriting it.

' Define needed I/O constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Get input file name from command line parm
If (WScript.Arguments.Count > 0) Then
   strInFile = objFSO.GetAbsolutePathname(WScript.Arguments(0))
Else
   WScript.Echo "No input filename specified."
   WScript.Quit
End If

' Get output file name from command line parm (if none, overwrite input file)
If (WScript.Arguments.Count > 1) Then
   strOutFile = WScript.Arguments(1)
   strOutFile = objFSO.GetAbsolutePathname(WScript.Arguments(1))
Else
   strOutFile = strInFile
End If

' Make sure input file exists
If Not objFSO.FileExists(strInFile) Then
   Wscript.Echo "ERROR: Input file """ & strInFile & """ does not exist."
   Wscript.Quit
End If

' Read entire file contents
Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
strData = objFile.ReadAll
objFile.Close

' Split input data at line breaks
arrData = Split(strData, vbCrLf)

' Look at each line and edit as needed
For i = 0 To UBound(arrData)

    ' I would like a simple .bat script which will look at position 4 first - if it sees 65-ABCDEF then it looks at position 101. 
    ' If position 101 is blank, then it looks in position 100, and looks at tast 6 digits of position 100,  takes those six numbers - removes the last didit, and 
    ' write the 5 digits in position 101 - without touching the rest of the data and saving it to a new file called newfile1.csv

    If arrData(i) <> "" Then
        ' Split this input line into it's fields
        arrFields = Split(arrData(i), ",")
        
        ' Make sure we have at least 101 fields
        If UBound(arrFields) > 100 Then
            blnLineChanged = False

            ' Look for matching field value and perform needed edit
            If UCase(arrFields(3)) = UCase("65-ABCDEF") Then
                If arrFields(99) = "" Then
                    arrFields(99) = Left(Right(arrFields(98), 6), 5)
                    blnLineChanged = True
                End If
            End If

            ' If changes made update this line in data array
            If blnLineChanged Then
                arrData(i) = Join(arrFields, ",")
            End If

        End If

    End If

Next

' Write output file with any changes
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.Write Join(arrData, vbCrLf)
objFile.Close

Open in new window

fileout.txt


»bp

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
100questionsAuthor Commented:
Worked perfectly Bill.  My next project will be to do the same thing using a Powershell .ps1 file which will be my next question.  As usual many thanks for your assistance.
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.