0

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!

darc
  • 25
  • 1
  • 6
  • SO isn't a code writing service. You're more likely to get help if you post your own attempt(s) first. – boxdog Jan 28 '19 at 11:22
  • @boxdog Sure, Edited and added my script. – darc Jan 28 '19 at 11:28
  • 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 — https://stackoverflow.com/questions/54403415/need-to-remove-one-constant-portion-from-rows-in-a-csv-using-powershell – Lee_Dailey Jan 28 '19 at 14:08
  • Not getting a proper solution to my query – darc Jan 28 '19 at 14:24
  • Why not a simple text replace in the file? Could do that even in any text editor. – marsze Jan 28 '19 at 14:24
  • @marsze I need to generate such huge reports using powershell, cant do it manually. – darc Jan 28 '19 at 14:26
  • Powershell can do that just as well as any editor. But CSV parsing is probably, especially **because** the reports are huge. – marsze Jan 28 '19 at 14:26
  • Pretty new to PS, could you please guide me to it... – darc Jan 28 '19 at 14:27

2 Answers2

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\", ""))
marsze
  • 15,079
  • 5
  • 45
  • 61
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
boxdog
  • 7,894
  • 2
  • 18
  • 27
  • 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? – darc Jan 28 '19 at 12:54
  • _"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. – boxdog Jan 28 '19 at 12:58