none
Power-Shell Search and Replace values in csv file. RRS feed

  • Question

  • Hi,

    I have a csv file and I would like to search and replace all "Spaces" with a periods "." in a particular column.

    Example:

    Name

    Joe Smith

    Jane Smith

    Name

    Joe.Smith

    Jane.Smith

    ...etc

    Thursday, March 1, 2018 10:29 PM

Answers

  • Sorry but you are all wrong. Using the pipeline will produce no output if you fail to produce output.

    import-csv Names.csv |
        foreach-object {
            $_.Name -replace '\s', '.'
        }

    THe above produces output wwhich CAN be sent to a file.


    \_(ツ)_/



    • Edited by jrv Friday, March 2, 2018 3:03 PM
    • Marked as answer by mcrazz Friday, March 2, 2018 5:16 PM
    Friday, March 2, 2018 3:02 PM
  • Thanks for your help everyone.

    In the end, this is what i got to work.. (not sure why this didn't work when I tried it yesterday)

    # Replaces blank spaces with periods for all values in the row NAME.
    $test = import-csv c:\temp\FileName.csv
    $test | foreach-object {

    $_.Name = $_.Name.replace(' ','.')

    }

    $test | export-csv C:\temp\NewFileName.csv -notype

    *******************

    Changes This....

    Name
    ----
    02-01_User One
    02-02_User Two

    To This... 

    Name

    ----

    02-01_User.One
    02-02_User.Two

    thanks gain :)

    • Marked as answer by mcrazz Friday, March 2, 2018 5:16 PM
    Friday, March 2, 2018 5:12 PM

All replies

  • Sounds interesting. Are you asking for someone to write the code for this for you? Perhaps you missed the post at the top of this forum:

    This forum is for scripting questions rather than script requests


    -- Bill Stewart [Bill_Stewart]

    Thursday, March 1, 2018 10:35 PM
    Moderator
  • 'Joe X Smith' -replace '\s','\.'

    \_(ツ)_/

    Thursday, March 1, 2018 10:38 PM
  • Sorry , not looking for someone to write the code.

    Here's what I have so far.

    ############

    $test = import-csv c:\temp\FileName.csv
    $test | foreach-object {

    $_.Name = $_.Name.replace("' '","\.")

    }

    $test | export-csv new-test.csv -notype

    ##############

    Friday, March 2, 2018 1:56 PM
  • Almost ... ;-)

    This should do it actually

    import-csv c:\temp\FileName.csv | 
        foreach-object {
            $_.Name = $_.Name -replace '\s', '.'
        } | 
            export-csv new-test.csv -notype
    


    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    Friday, March 2, 2018 2:13 PM
  • PS C:\temp> Import-Csv .\FileName.csv | Select Name

    Name
    ----
    02-01_User One
    02-02_User Two
    02-03_User Three
    02-04_User Four

    ************

    What I want is 

    02-01_User.One
    02-02_User.Two
    02-03_User.Three
    02-04_User.Four

    ************

    #Here's the script I'm using with the -replace options you provided. But still no luck.

    $test = import-csv c:\temp\FileName.csv
    $test | foreach-object {

    $_.name = $_.name.replace('\s','\.')

    }

    $test | export-csv C:\temp\NewFileName.csv -notype


    • Edited by mcrazz Friday, March 2, 2018 2:16 PM
    Friday, March 2, 2018 2:14 PM
  • #Here's the script I'm using with the -replace options you provided. But still no luck.

    If you expect someone to help you should specify what exactly "... still no luck" means. Does it not run, does it produce errors, what errors?  .....  ;-)  Most of us do not have crystal balls. :-D

    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    Friday, March 2, 2018 2:20 PM
  • Sorry but you are all wrong. Using the pipeline will produce no output if you fail to produce output.

    import-csv Names.csv |
        foreach-object {
            $_.Name -replace '\s', '.'
        }

    THe above produces output wwhich CAN be sent to a file.


    \_(ツ)_/



    • Edited by jrv Friday, March 2, 2018 3:03 PM
    • Marked as answer by mcrazz Friday, March 2, 2018 5:16 PM
    Friday, March 2, 2018 3:02 PM
  • Thanks for your help everyone.

    In the end, this is what i got to work.. (not sure why this didn't work when I tried it yesterday)

    # Replaces blank spaces with periods for all values in the row NAME.
    $test = import-csv c:\temp\FileName.csv
    $test | foreach-object {

    $_.Name = $_.Name.replace(' ','.')

    }

    $test | export-csv C:\temp\NewFileName.csv -notype

    *******************

    Changes This....

    Name
    ----
    02-01_User One
    02-02_User Two

    To This... 

    Name

    ----

    02-01_User.One
    02-02_User.Two

    thanks gain :)

    • Marked as answer by mcrazz Friday, March 2, 2018 5:16 PM
    Friday, March 2, 2018 5:12 PM
  • import-csv Names.csv |
        foreach-object {
            $_.Name -replace '\s', '.'
        } |
        Export-Csv newfile.csv -NoType

    This is what you could have done:


    \_(ツ)_/

    Friday, March 2, 2018 5:18 PM