CSV Search AutoIT

Question

I have a CSV file that contains 4 columns, I want to search column 2 and change the corresponding data in column 4 using AutoIT:

col 1  col 2  col 3  col 4
1      502    shop   25.00
2      106    house  50.00
3      307    boat   15.00

Answer 1

If the columns are separated by tabs then you could use StringSplit for that.

$s1 = '1    502 shop    25.00'
$s2 = '2     106    house   50.00'
$s3 = '3    307 boat    15.00'

For $i=1 To 3
    $array = StringSplit(Eval('s' & $i), @TAB)
    ConsoleWrite('Column 2: "' & StringStripWS($array[2], 8) & '"' & @CRLF)
    ConsoleWrite('Column 4: "' & StringStripWS($array[4], 8) & '"' & @CRLF)
Next

This sample code will print:

Column 2: "502"
Column 4: "25.00"
Column 2: "106"
Column 4: "50.00"
Column 2: "307"
Column 4: "15.00"

EDIT

This example creates a CSV file, then reads the file back in and searches every line for '106'. If the string is found and the last column has the value of '50.00', then this value is replaced with '22.00'. The result is written to a new CSV file.

; write the data to the CSV file
Global $hFile = FileOpen('test.csv', 10)
If $hFile = -1 Then Exit
FileWrite($hFile, '1' & @TAB & '502 ' & @TAB & 'shop' & @TAB & '25.00' & @CRLF & _
              '2' & @TAB & '106 ' & @TAB & 'house' & @TAB & '50.00' & @CRLF & _
              '3' & @TAB & '307' & @TAB & 'boat' & @TAB & '15.00')

FileClose($hFile)

; read the CSV file and create a new one
If Not FileExists('test.csv') Then Exit
Global $hFileIn  = FileOpen('test.csv')
Global $hFileOut = FileOpen('test_new.csv', 10)

While 1
    Global $sLine = FileReadLine($hFileIn)
    If @error = -1 Then ExitLoop

    If StringInStr($sLine, '106') Then
        $sLine = _ReplacePrices($sLine)
        ConsoleWrite('New price: ' & $sLine & @CRLF)
    EndIf
    FileWriteLine($hFileOut, $sLine)
WEnd
FileClose($hFileIn)
FileClose($hFileOut)
Exit

; search for "106" find that and the corresponding value in
; column 4 (50.00) and change the column 4 value to "22.00"
Func _ReplacePrices($sLineFromCSVFile)
    Local $array = StringSplit($sLineFromCSVFile, @TAB)

    If StringStripWS($array[2], 8) = '106' And _
       StringStripWS($array[4], 8) = '50.00' Then
        Return $array[1] & @TAB & $array[2] & @TAB & _
                   $array[3] & @TAB & '22.00'
    EndIf
EndFunc

If you run the example this will be the result: