Need to split the values in few cells in all the cells in CSV

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of Manoj D Manoj D 1 year, 9 months ago.

  • Author
    Posts
  • #28388
    Profile photo of Manoj D
    Manoj D
    Participant

    Need to split Database-A and 40005 from CELL A4 and need to place Database-A in Column A4 and need to replace 1414 with 40005 in Column B4. Could anyone helpme out on this?

    URL Port
    Application-a.com 1414
    Application-b.com 1414
    website-x.com 25025
    website-y.com 20208
    Database-A,40005 1414
    Database-B,40000 1414

    COLUMN A:
    URL
    Application-a.com
    Application-b.com
    website-x.com
    website-y.com
    Database-A,40005
    Database-B,40000

    COLUMN B:
    Port
    1414
    1414
    25025
    20208
    1414
    1414

  • #28399
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    Saving your first example as Test.csv:

    #Open CSV with space delimiter and write results to $results var
    $results = Import-CSV C:\Temp\test.csv -Delimiter " " | 
    foreach{ 
        #if the URL contains a comma
        if ($_.URL -like "*,*") {
            #Split and create and array at the comma
            $split = $_.URL -Split ","
            #Update the URL and port with the split data
            $_.URL = $Split[0]
            $_.Port = $Split[1]
        }
        $_
    }
    
    $results | Format-Table -AutoSize
    

    Output:

    URL               Port 
    ---               ---- 
    Application-a.com 1414 
    Application-b.com 1414 
    website-x.com     25025
    website-y.com     20208
    Database-A        40005
    Database-B        40000
    
  • #28437
    Profile photo of Manoj D
    Manoj D
    Participant

    Hi Rob,

    Thanks for your reply. I tried your logic but I am getting the ouput as you specied. I am getting the output like

    URL,PORT
    ——–
    Application-a.com,1414
    Application-b.com,1414
    website-x.com,25025
    website-y.com,20208
    Database-A,40005,1414
    Database-B,40000,1414

  • #28468
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    The example you posted was space delimited:

    URL Port
     Application-a.com 1414
     Application-b.com 1414
     website-x.com 25025
     website-y.com 20208
     Database-A,40005 1414
     Database-B,40000 1414
    

    If it is comma-delimited, you need to simply remove -Delimiter " "

  • #28718
    Profile photo of Manoj D
    Manoj D
    Participant

    Hi Rob,

    Thanks it got resolved

You must be logged in to reply to this topic.