Changing a .csv file with PowerShell

Question

I got this .csv file, and I'm trying to make a PowerShell script that changes some things in it.

This is how the files look from the start:

This is how it looks after I run the script I made (see below):

This is how I want it to look:

The script I made is below, what should I change to make it work?

[IO.File]::ReadAllText("C:\Users\Daniel\Desktop\as2.csv").Replace("[","").Replace("\","").Replace("]","").Replace("Evidence 1","c:").Replace(",","\").Replace("Path,","") |
    Out-File C:\Users\Daniel\Desktop\new.csv -Encoding Ascii –Force
$a = Get-Content c:\Users\Daniel\Desktop\new.csv
$a[2..($a.length - 2)] > c:\Users\Daniel\Desktop\new.csv
Import-Csv c:\Users\Daniel\Desktop\new.csv

Ansgar Wiechers · Accepted Answer · 2017-09-29 09:48:15Z

2

Don't break the replacements up too much. You can simply replace the whole sequence [Evidence 1, with C:\, remove ], and replace all commas followed by a space with backslashes.

(Get-Content 'C:\path\to\input.csv' | Select-Object -Skip 2) `
        -replace '\[Evidence 1, ', 'C:\' `
        -replace '\],' `
        -replace ', ', '\' |
    Set-Content 'C:\path\to\output.csv'

edited Sep 29, 2017 at 9:48

answered Sep 29, 2017 at 8:40

Ansgar Wiechers

201k27 gold badges286 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ansgar Wiechers Over a year ago

@DanielOppenlænder The backslash in the replacement string doesn't need to be escaped, and apparently there are no double quotes in your input file. Fixed.

Daniel Over a year ago

@asgarwiechers Thanks alot. it looks ALOT better now. But i still got 1 minor error. i.imgur.com/zlB4B5p.jpg . If you look at the screenshot, some of the lines still got a "/", and a "," in every line in the end. Cant i get thouse removed some how ?

Ansgar Wiechers Over a year ago

@DanielOppenlænder Remove \], instead of just \]. See updated answer.

Snak3d0c · Accepted Answer · 2017-09-29 10:18:35Z

For patterns like these you could also work with Regular Expressions. Underneath i use the .NET class of REGEX and not the build in REGEX function (-match). The script would look like :

$string = "
Path,                                                                  
[Evidence 1],
[Evidence 1, Folder A,],
[Evidence 1, Folder A, AK-472.png ],
[Evidence 1, Folder A, data.lua ],
[Evidence 1, Folder A, glock_19.jpg],
[Evidence 1, Folder A, Nuix 7.2.lnk],
[Evidence 1, Folder A, Nuix Web review.url],
"
# Display string for debugging
$string

$m = ([regex]::Matches($string,'((?<=, ).*(?=]))') | % { $_.groups[0].value } )

foreach($x in $m){
    $folder = [regex]::Matches($x,'(.*(?=,))') | % { $_.groups[0].value }
    $item = [regex]::Matches($x,'((?<=, ).*)') | % { $_.groups[0].value }
    "c:\"+$folder.Trim(' ')+"\"+$item
}

This would give us the following output:

c:\Folder A\
c:\Folder A\AK-472.png 
c:\Folder A\data.lua 
c:\Folder A\glock_19.jpg
c:\Folder A\Nuix 7.2.lnk
c:\Folder A\Nuix Web review.url

What i do is i take only the content i need and put it into $m

So at that point, $M contains: "Folder A, AK-472.png "

What i then do is i loop through all the items and catch the folder and item with an individual regex (based upon the first one).

So

$folder = [regex]::Matches($x,'(.*(?=,))') | % { $_.groups[0].value }

would give us "Folder A"

and

$item = [regex]::Matches($x,'((?<=, ).*)') | % { $_.groups[0].value }

would give us "AK-472.png"

All there is left to do now, is to create out own new path:

"c:\"+$folder.Trim(' ')+"\"+$item

I trim the space otherwise it would look like:

c:\Folder A \AK-472.png

Esperento57 · Accepted Answer · 2017-09-29 13:45:45Z

0

try this:

import-csv "c:\temp\as2.csv" -Header Unused, Path, File | where Path -ne '' | 
select @{N="Path";E={"c:\{0}\{1}" -f $_.Path, $_.File.Replace(']', '')}} | Export-Csv "c:\temp\newas2.csv" -NoType

answered Sep 29, 2017 at 13:45

Esperento57

17.6k6 gold badges42 silver badges47 bronze badges

Collectives™ on Stack Overflow

Changing a .csv file with PowerShell

3 Answers 3

3 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Related