I want to replace a data of an exact column in the csv file

[ElPandaRojo1]

Good day

I need help on how I can replace an exact data of a column in a csv file

 

 

Example

 

I have this base

I want to replace the data in the PROYECTO column

I just need a function to replace the data in that column knowing that there is also a zero in the other columns.
I have already used the StringReplace function and the StringRegExpReplace function

 

and I want it to be like this

 

Someone to help me would appreciate it

 

Thank You Very Much!

 

 

Help.au3

[Subz]

Have you tried using _FileReadToArray?  You can then loop through the array change the column details and then paste it back into the document.  You could also do it with the _Excel functions (if you have access to Excel).

[Malkey]

A _FileReadToArray() method.

expandcollapse popup

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

Local $sFileName = "TestData1.csv"
_CreateTestCvsFile($sFileName)

Local $sNewFileName = "TestData2.csv"
Local $sColReplace = "Item 3", $sReplace = "Home"

_SaveBaseSend($sFileName, $sNewFileName, $sColReplace, $sReplace)

ShellExecute($sNewFileName)
;FileDelete($sNewFileName) ; Clean up


; The column named in $sColReplace will have its entries replaced with $sReplace in each row, if
;   the column entries from 3rd column to the end of the row are all zero.
; The first and second columns may contain any data.
;
Func _SaveBaseSend($sFile, $sNewFile, $sColReplace, $sReplace)
    Local $aArray[0][0]
    _FileReadToArray($sFile, $aArray, 0, ",")
    ;_ArrayDisplay($aArray)

    Local $iColReplaceNum, $bRow = 1
    For $i = 0 To UBound($aArray) - 1
        For $j = 0 To UBound($aArray, 2) - 1
            If $i = 0 Then ; The first column header row.

                ; In column header names (row 0) find column number.
                If $aArray[0][$j] == $sColReplace Then
                    $iColReplaceNum = $j
                    ExitLoop 1 ; Once the header name is found, exit that first row.
                EndIf

                $bRow = 0
                ;ConsoleWrite($i & "  " & $j & "  " & $aArray[$i][$j] & "  " & $bRow & @CRLF)
            Else ; All rows except first row.
                If $j > 1 And $aArray[$i][$j] <> 0 Then ; "$j > 1" will only match columns 3 (or, array index 2, zero based) to the end of the row.
                    $bRow = 0
                    ExitLoop 1 ; Once one non-zero entry appears in a row, the replacement will not happen, so exit row.
                EndIf
            EndIf
            ;ConsoleWrite($i & " > " & $j & "  " & $aArray[$i][$j] & "  " & $bRow & @CRLF)
        Next
        If $bRow Then $aArray[$i][$iColReplaceNum] = $sReplace
        $bRow = 1
    Next
    ;_ArrayDisplay($aArray)
    _FileWriteFromArray($sNewFile, $aArray, Default, Default, ",")
EndFunc   ;==>_SaveBaseSend

; --------------- Create test CSV file ---------------
Func _CreateTestCvsFile($sFileName)
    If FileExists($sFileName) Then FileDelete($sFileName)
    FileWrite($sFileName, _
                    "Item 1,Item 2,Item 3,Item 4,Item 5,Item 6,Item 7,Item 8,Item 9,Item 10" & @CRLF & _
                    "21,22,0,0,0,0,0,0,0,0"                 & @CRLF & _ ; Replacement will happen here
                    "31,32,0,0,0,0,0,38,0,0"                & @CRLF & _
                    "41,42,0,0,0,0,0,0,0,0"                 & @CRLF & _ ; Replacement will happen here
                    "51,52,53,54,55,56,57,58,59,60"         & @CRLF & _
                    "61,62,63,64,65,66,67,68,69,70"         & @CRLF & _
                    "71,72,0,74,75,76,77,78,79,80"          & @CRLF & _
                    "81,82,0,84,85,86,87,88,89,90"          & @CRLF & _
                    "91,92,0,0,0,0,0,0,0,0"                 & @CRLF & _ ; Replacement will happen here
                    "101,102,0,104,105,106,107,108,109,110")
EndFunc   ;==>_CreateTestCvsFile

 

[rudi]

Hi.

this should solve your task:

expandcollapse popup

#include <Debug.au3>
#include <File.au3>


$SampleCSV="C:\temp\Sample.csv"

$hSampleCSV=FileOpen($SampleCSV,2+8)
FileWriteLine($hSampleCSV,"FECHA,DOCUMENTO,NOMBRE,PROYECTO,SUBPROJECTO,CIXY,PREXX,EXXX,PAIS,CIUXX,MASXX,CORXX,TELXX")
FileWriteLine($hSampleCSV,"30/04/2020,123456789,Julio,0,0,0,0,0,0,0,0,0,0")
FileWriteLine($hSampleCSV,"01/05/2020,123456790,Pete,0,0,0,0,0,0,0,0,0,0")
FileClose($hSampleCSV)

Dim $aCSV

_FileReadToArray($SampleCSV,$aCSV)


$OutTxt=$aCSV[1]

_DebugArrayDisplay($aCSV)
$aHeader=StringSplit($aCSV[1],",")

$ReplaceCol="PROYECTO"
$iReplace=_ArraySearch($aHeader,$ReplaceCol)

_DebugArrayDisplay($aHeader,"Column to be replaced = " & $iReplace)
$ReplaceWith="House"


For $i = 2 To $aCSV[0]
    $aContent=StringSplit($aCSV[$i],",")
    $aContent[$iReplace]=$ReplaceWith
    _DebugArrayDisplay($aContent)
    $OutTxt&=@CRLF & _ArrayToString($aContent,",",1)
Next

ConsoleWrite("---------------------" & @CRLF)
ConsoleWrite($OutTxt & @CRLF)
ConsoleWrite("---------------------" & @CRLF)

$out="C:\temp\Sample-fixed.csv"
$hOut=FileOpen($out,2+8)
FileWrite($hOut,$OutTxt)
FileClose($hOut)
ShellExecute("notepad.exe",$out)

Rudi.