CODEWITHSUNDEEP
powershellcsv
Daniel
Daniel

Reputation: 107

Changing a .csv file with PowerShell

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:

Start

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

After the script

This is how I want it to look:

How it should 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

Upvotes: 1

Views: 366

Answers (3)

Esperento57
Esperento57

Reputation: 17462

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

Upvotes: 0

Snak3d0c
Snak3d0c

Reputation: 626

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

Upvotes: 0

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200203

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'

Upvotes: 2

Related Questions