Trimming the file path in a CSV up to a certain path/file name stored in a variable

Question

I have a PowerShell script that stores the full paths of the files in a specific directory along with some other information. I have exported the CSV file. Now, the paths are actually being combined together to comprise of the full path. Let me give an example below:

$root= C:\Users\home\
$web = facebook website\domain
$app = facebook android\latest

These paths are then joined together with either Join-Path or $fbweb = $root + $web to make up the full path: C:\Users\home\facebook website\domain

Now the above mentioned path will have other files, subfolders etc in it but that's the gist of how the paths are structured. I have exported them in the CSV file but I'm having trouble with the following. I need the CSV file to have paths in such a way that the part leading up to the $web is trimmed out.

For instance if the CSV file is like this:

Path
C:\Users\home\facebook website\domain\version\version-feb-2020.txt
C:\Users\home\facebook website\domain\interface\somefile.html
C:\Users\home\facebook android\latest\messenger\messenger app files\code.js
C:\Users\home\facebook android\latest\application\somecode.js

I want it to turn out like this:

Path
facebook website\domain
\version\version-feb-2020.txt
\interface\somefile.html
facebook android\latest
\messenger\messenger app files\code.js
application\somecode.js

I have tried using the following to trim it out:

$number = [regex]::matches($fbweb,"\\").count
Select-Object Hash,
        @{
            Name = "FilePath";
            Expression = { [string]::Join("\", ($_.Path -split "\\" | Select-Object -skip ($number)))}
        }

Update: I have tried this:

$replace = Join-Path -Path $root -ChildPath $web
echo $replace
$RefHash = Import-csv "C:\Users\Admin\Desktop\fb.csv"
$RefHash | ForEach-Object {
           echo $_.Path
           ($_.Path).Replace($replace, "\")
} | Export-csv "C:\Users\Admin\Desktop\replaced.csv"

But this just results in the exported csv showing the following:

#TYPE System.String
"Length"
"numbers"
"numbers"
"numbers"

Answer 1

As discussed, you have 2 methods to manage this:

1. Treat the csv file as a text file and do a replace on a get-content:

(Get-Content -Path "C:\temp\TestMD5.csv").Replace($replace, "\") | Set-Content "C:\temp\TestMD5updated.csv"

2. Import the CSV, separate the each of the parameters, modify what you require and then build a pscustomobject which you then export as csv:

#Preparing variables
$scriptdir = [System.IO.Path]::GetDirectoryName($MyInvocation.MyCommand.Path)
$sourcecsv = Import-csv -path "C:\temp\TestMD5.csv"
$obj = @()
$root = "C:\Temp"
$web = "Test01\Test02\"
$replace = Join-Path -Path $root -ChildPath $web
$target = "\"

#Executing replace
foreach ($line in $sourcecsv) {
    $object = New-Object -TypeName psobject
    $algo = $line | Select-Object -ExpandProperty 'Algorithm'
    $Hash = $line | Select-Object -ExpandProperty 'Hash'
    $Path = ($line | Select-Object -ExpandProperty 'Path').Replace($replace, $target)
    $object | Add-Member -MemberType NoteProperty -Name Algorithm -Value $algo
    $object | Add-Member -MemberType NoteProperty -Name Hash -Value $Hash
    $object | Add-Member -MemberType NoteProperty -Name Path -Value $Path
    $obj += $object
    $object
}

$obj | Export-Csv -NoTypeInformation -Path "$scriptdir\UpdatedVars.csv" 

The first one is faster, the 2nd one provides you the flexibility that would allow you to build functions that are generalized and modify additional parameters as required.

Answer 2

OK assuming you don't actually need to import and deal with the file as a CSV file. You simply need to replace the strings in a text file. If so then you can use get-content instead of import-csv

You want to use REPLACE.

$SourceFile = Get-Content -Path "D:\URL.txt"
$root= "C:\Users\home\"
$web = "facebook website\domain"
$app = "facebook android\latest"
$replace1 = $root+$web
$replace2 = $root+$app

$SourceFile -replace [Regex]::Escape($replace1), "\" -replace [Regex]::Escape($replace2), "\" | Set-Content -Path "D:\urlreplaced.txt"

This will do the replace and output the new file to D:\urlreplaced.txt

Answer 3

To convert the csv data into a new format as you would like, you could do the following:

$root= 'C:\Users\home'
$web = 'facebook website\domain'
$app = 'facebook android\latest'

$webPath = [regex]::Escape((Join-Path -Path $root -ChildPath 'facebook website\domain'))
$appPath = [regex]::Escape((Join-Path -Path $root -ChildPath 'facebook android\latest'))

$data    = Import-Csv -Path "C:\Users\Admin\Desktop\fb.csv"
$appData = ($data | Where-Object { $_.Path -match "^$appPath" } | Select-Object @{Name = 'Path'; Expression = {$_.Path -replace "^$appPath" }}).Path
$webData = ($data | Where-Object { $_.Path -match "^$webPath" } | Select-Object @{Name = 'Path'; Expression = {$_.Path -replace "^$webPath" }}).Path

# manually create the one-column csv (easiest is to do this in a Here-String)
$newData = @"
Path
$web
$($webData -join [Environment]::NewLine)
$app
$($appData -join [Environment]::NewLine)
"@

# output on screen
$newData

# output to new CSV file
$newData | Set-Content -Path "C:\Users\Admin\Desktop\replaced.csv" -Force

Output on screen

Path
facebook website\domain
\version\version-feb-2020.txt
\interface\somefile.html
facebook android\latest
\messenger\messenger app files\code.js
\application\somecode.js