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

Possible duplicate of [Parse all the rows of a CSV file in a loop using AutoIt](https://stackoverflow.com/questions/13908625/parse-all-the-rows-of-a-csv-file-in-a-loop-using-autoit) — rugk, Mar 12 '19 at 07:11

Andreas · Accepted Answer · 2013-08-23T09:22:26.623

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:

enter image description here

user4157124 · Answer 2 · 2018-07-02T12:54:22.060

search column 2 and change the corresponding data in column 4

Example using _ArraySearch() :

#include <FileConstants.au3>
#include <File.au3>
#include <Array.au3>

Global Enum  $CSV_COL1, _
             $CSV_COL2, _
             $CSV_COL3, _
             $CSV_COL4

Global Const $g_sFileInp   = @ScriptDir & '\input.csv'
Global Const $g_sFileOut   = @ScriptDir & '\output.csv'
Global Const $g_sFileDelim = @TAB
Global Const $g_iColSearch = $CSV_COL2
Global Const $g_iColRepl   = $CSV_COL4
Global Const $g_sValSearch = '502'
Global Const $g_sValRepl   = '35'

Global       $g_iRow       = 0
Global       $g_aCSV

_FileReadToArray($g_sFileInp, $g_aCSV, $FRTA_NOCOUNT, $g_sFileDelim)

While True

    $g_iRow = _ArraySearch($g_aCSV, $g_sValSearch, ($g_iRow ? $g_iRow + 1 : $g_iRow), 0, 0, 0, 1, $g_iColSearch, False)
    If @error Then ExitLoop

    $g_aCSV[$g_iRow][$g_iColRepl] = $g_sValRepl

WEnd

_FileWriteFromArray($g_sFileOut, $g_aCSV, $CSV_COL1, Default, $g_sFileDelim)
ShellExecute($g_sFileOut)

score -1 · Answer 3 · answered Mar 12 '19 at 07:17

As for parsing a CSV file, you are likely better off using a library (called user-defined functions in AutoIt), especially if you e.g. have complex CSVs with quoted strings (commas inside of the "cell"/string) or line breaks, which are hard to handle.

The best I can recommend is CSVSplit. Basically you have a function _CSVSplit that takes a whole CSV file (content, i.e. string!) and returns you a two-dimensional array:

Local $sCSV = FileRead($sFilePath)
If @error Then ; ....

$aSplitArray = _CSVSplit($sCSV, ",")

As in your case you do not really have a comma-seperated file you may anyway use the function with a space as a seperator () though I am not sure it 100%ly works then.

You can then do everything you want with this array. Obviously, CSVSplit also provides the "reverse" function for turning an array into a CSV string again, _ArrayToCSV.

So for your use case, you can then just loop through the array or directly use functions such as _ArraySearch to search for your entry and do something with it afterwards.

Originally posted as an answer here, which I consider a duplicate of this question.

CSV Search AutoIT

3 Answers3