Need to remove specific portion from rows in a csv using powershell

Question

I have a csv file with two columns and multiple rows, which has the information of files with folder location and its corresponding size, like below

"Folder_Path","Size"
"C:\MSSQL\DATA\UsersData\FTP.txt","21345"
"C:\MSSQL\DATA\UsersData\Norman\abc.csv","78956"
"C:\MSSQL\DATA\UsersData\Market_Database\123.bak","1234456"

What i want do is remove the "C:\MSSQL\DATA\" part from every row in the csv and keep the rest of the folder path after starting from UsersData and all other data intact as this info is repetitive. So my csv should like this below.

"Folder_Path","Size"
"UsersData\FTP.txt","21345"
"UsersData\Norman\abc.csv","78956"
"UsersData\Market_Database\123.bak","1234456"

What i am running is as below

Import-Csv ".\abc.csv" |
    Select-Object -Property @{n='Folder_Path';e={$_.'Folder_Path'.Split('C:\MSSQL\DATA\*')[0]}}, * |
    Export-Csv '.\output.csv' -NTI

Any help is appreciated!

SO isn't a code writing service. You're more likely to get help if you post your own attempt(s) first. — boxdog
– boxdog, Commented Jan 28, 2019 at 11:22
why do you have a 2-hours-younger repeat of this @ Need to remove one constant portion from rows in a csv using powershell - Stack Overflow — stackoverflow.com/questions/54403415/… — Lee_Dailey
– Lee_Dailey, Commented Jan 28, 2019 at 14:08
Why not a simple text replace in the file? Could do that even in any text editor. — marsze
– marsze, Commented Jan 28, 2019 at 14:24

marsze · Accepted Answer · 2019-01-28 14:35:31Z

1

Seems like a job for a simple string replace:

Get-Content "abc.csv" | foreach { $_.replace("C:\MSSQL\DATA\", "") | Set-Content "output.csv"

or:

[System.IO.File]::WriteAllText("output.csv", [System.IO.File]::ReadAllText("abc.csv" ).Replace("C:\MSSQL\DATA\", ""))

edited Jan 28, 2019 at 14:35

answered Jan 28, 2019 at 14:30

marsze

17.5k6 gold badges59 silver badges67 bronze badges

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

1 Comment

darc Over a year ago

Thank you so much Sir. This is exactly what i needed. Works like a charm!

boxdog · Accepted Answer · 2019-01-28 11:50:08Z

0

This should work:

Import-Csv ".\abc.csv" |
    Select-Object -Property @{n='Folder_Path';e={$_.'Folder_Path' -replace '^.*\\(.*\\.*)$', '$1'}}, Size |
        Export-Csv '.\output.csv' -NoTypeInformation

answered Jan 28, 2019 at 11:50

boxdog

8,5023 gold badges22 silver badges30 bronze badges

2 Comments

darc Over a year ago

This one is working, but what this is doing is keeping one folder name just before the file name, but this is not giving me the output that i need, cuz there are a lot of subfolders too in this folder whose info i want to retain. Can there a way in which i can just remove the "C:\MSSQL\DATA\" portion only?

boxdog Over a year ago

"this is not giving me the output that i need" Possibly not, but it is giving the output you asked for. Your question/example wasn't clear that part to be left was variable.

Collectives™ on Stack Overflow

Need to remove specific portion from rows in a csv using powershell

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related