none
Powershell import-csv then replace character then export-csv

    Question

  • Hi all,

    Hope you can help:

    I need to import-csv then replace certain characters in a field then export it, example below.

    CSV:
    Date, Time
    -------------------
    [21-10-2009, 09-10]

    So the script:

    $a = import-csv c:\test.csv | select Date

    $b = $a -replace "[\[]", ""

    $b | export-csv c:\test1.csv -noTypeInformation

    Yes I could do a replace on the whole csv using get-content but I need to be particular on what I replace per column, for example the Time column I need to replace the ] with nothing.

    Any ideas or any clarification please ask.

    Thanks

    Dave

    Monday, August 30, 2010 8:27 PM

Answers

  • Okay.  See if this helps:

    $test = import-csv c:\test.csv

    $test | foreach-object {

    $_.date = $_.date.replace("[","")

    $_.time = $_.time.replace("]","")

    }

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


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    Monday, August 30, 2010 8:45 PM

All replies

  • If the [] are the leading and trailing characters in each line, you can use .trim()

    $newfile = @()

    get-content c:\test.csv |% {$newfile += $_.trim("[]")}

    $newfile | out-file c:\newtest.csv


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    Monday, August 30, 2010 8:37 PM
  • Hi mjolinor,

    I probably gave a bad example, the example are the first two columns of the csv, there are 12 columns that may contain [ or ] thats why I need to do replaces on a column by column basis as each column may need something different replacing.

    Hope that makes things abit clearer.

    Dave

    Monday, August 30, 2010 8:40 PM
  • Okay.  See if this helps:

    $test = import-csv c:\test.csv

    $test | foreach-object {

    $_.date = $_.date.replace("[","")

    $_.time = $_.time.replace("]","")

    }

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


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    Monday, August 30, 2010 8:45 PM
  • That exports the same data i.e. $test not with the replace of [ to nothing :(

    I thought that would have worked.

    Monday, August 30, 2010 8:53 PM
  • Sorry it does, I was trying to use a regex not [ itself.

    Thankyou soo much!!

    Dave

    Monday, August 30, 2010 8:55 PM
  • It works for me.

     

    [PS] C:\testfiles>$y = import-csv test.csv
    [PS] C:\testfiles>$y

    Date                                                        Time
    ----                                                        ----
    08/21/2010                                                  15:41
    [08/21/2010                                                 15:41]
    [08/22/2010                                                 16:41]


    [PS] C:\testfiles>$y |% {$_.date = $_.date.replace("[","");$_.time = $_.time.replace("]","")}
    [PS] C:\testfiles>$y

    Date                                                        Time
    ----                                                        ----
    08/21/2010                                                  15:41
    08/21/2010                                                  15:41
    08/22/2010                                                  16:41


    [PS] C:\testfiles>$y | export-csv new-test.csv -notype
    [PS] C:\testfiles>gc new-test.csv
    "Date","Time"
    "08/21/2010","15:41"
    "08/21/2010","15:41"
    "08/22/2010","16:41"
    [PS] C:\testfiles>


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    Monday, August 30, 2010 9:03 PM