none
PowerShell to Change CSV row based upon multiple criteria is row RRS feed

  • Question

  • Good Morning, Everyone!

    I've searched through the forums for answer, but everything I'm coming up with is short of what I'm trying to achieve, but great starting spots.. I just can't figure out how to utilize what's out here to finish it.

    Let's say that I have a CSV file that has 3 columns of data, as show below:

    54,R,5,MEETINGS
    54,R,25,INDLB
    54,R,7,MISCMTA
    56,R,9,MAINT
    56,R,5,INDLB
    56,R,1,MAINT
    57,R,1,MAINT
    62,R,5,EN
    62,R,5,INDLB
    62,R,1,MEETINGS

    I need the PowerShell script to do the following: If Column1 equals 54,56,62 (and other specified numbers) AND Column4 equals MEETINGS, MISCMTA, MAINT (and other specified codes) THEN change Column4 to UNKNOWN.

    What we would end up with is:

    54,R,5,UNKNOWN
    54,R,25,INDLB
    54,R,7,UKNOWN
    56,R,9,UNKNOWN
    56,R,5,INDLB
    56,R,1,UNKOWN
    57,R,1,MAINT
    62,R,5,EN
    62,R,5,INDLB
    62,R,1,UNKNOWN

    All help is GREATLY appreciated!

    • Moved by Bill_Stewart Friday, July 7, 2017 4:35 PM This is not "scripts on demand"
    Wednesday, May 3, 2017 10:43 AM

All replies

  • Start by learning PowerShell.  This is a good project to help you learn.

    Note that we don't write scripts on request.

    Read this to set you expectations for technical forums.


    \_(ツ)_/

    Wednesday, May 3, 2017 10:52 AM
  • @jrv Thanks you, for absolutely no help at all. I'm actually quite surprised that you wasted any time responding at all. So, as your last comment directed me to 'start', I'll direct you to 'finish' by kissing my a$$. PR|CK
    • Marked as answer by scsh4 Thursday, May 4, 2017 4:51 PM
    • Unmarked as answer by Bill_Stewart Thursday, May 4, 2017 5:20 PM
    Thursday, May 4, 2017 4:45 PM
  • -source-

    -----------------------------------------------------------

    clear-variable z_*
    clear-variable z_* -scope global
    cls
    cd $PSScriptRoot
    $PSVersionTable.PSVersion -split("`t","")
    $z_csvsourcefile=".\test.csv"
    $z_csvtargetfile=".\test2.csv"

    function f_csvsplit($Global:z_a){
    $z_var=$z_a[3]
    if("$z_var" -eq "MEETINGS"){$z_a[3]="UNKNOWN"}
    if("$z_var" -eq "MISCMTA"){$z_a[3]="UNKNOWN"}
    if("$z_var" -eq "MAINT"){$z_a[3]="UNKNOWN"}
    return $z_a
    }

    if(test-path "$z_csvtargetfile"){remove-item $z_csvtargetfile}
    foreach ($z_csvfilecount in (Get-Content "$z_csvsourcefile")){
    $z_data=([string]$z_csvfilecount).split(",")
    $z_csvdate1=$z_data[0]
    if ($z_data[0] -eq 54){$z_data=f_csvsplit $z_data}
    if ($z_data[0] -eq 56){$z_data=f_csvsplit $z_data}
    if ($z_data[0] -eq 57){$z_data=f_csvsplit $z_data}
    if ($z_data[0] -eq 62){$z_data=f_csvsplit $z_data}
    $z_data="$z_data" -replace(' ',',')
    echo "$z_data"|out-file $z_csvtargetfile -Encoding utf8 -Append
    }

    Remove-Variable z_*
    Remove-Variable z_* -Scope global

    --------------------------------------------------------------

    test os windows 10 korea version

    powershell version 5.1.14393.1066

    ----------------------------------------------------------------

    input files

    $z_csvsourcefile=".\test.csv"

    ROOM,SEARTH,MAN,JOB
    1,R,5,MEETINGS
    2,R,5,MEETINGS
    3,R,5,MEETINGS
    54,R,5,MEETINGS
    54,R,25,INDLB
    54,R,7,MISCMTA
    56,R,9,MAINT
    56,R,5,INDLB
    56,R,1,MAINT
    57,R,1,MAINT
    62,R,5,EN
    62,R,5,INDLB
    62,R,1,MEETINGS
    2,R,5,MEETINGS
    3,R,5,MEETINGS

    output files

    $z_csvtargetfile=".\test2.csv"

    ROOM,SEARTH,MAN,JOB
    1,R,5,MEETINGS
    2,R,5,MEETINGS
    3,R,5,MEETINGS
    54,R,5,UNKNOWN
    54,R,25,INDLB
    54,R,7,UNKNOWN
    56,R,9,UNKNOWN
    56,R,5,INDLB
    56,R,1,UNKNOWN
    57,R,1,UNKNOWN
    62,R,5,EN
    62,R,5,INDLB
    62,R,1,UNKNOWN
    2,R,5,MEETINGS
    3,R,5,MEETINGS


    Thursday, May 4, 2017 10:03 PM
  • @jrv Thanks you, for absolutely no help at all. I'm actually quite surprised that you wasted any time responding at all. So, as your last comment directed me to 'start', I'll direct you to 'finish' by kissing my a$$. PR|CK

    We do not write scripts on request.  It is up to you to write a script and ask a specific question.

    Profanity will not win you help in any technical forum and you do need to learn the fundamentals of the technology so yuo can ask a clear question.


    \_(ツ)_/

    Friday, May 5, 2017 8:13 AM