Replace variable in CSV using Powershell

Question

I have a messy CSV file that gets generated each week. This is what I start with:

Company,Division,Department,Location,Job Title,Username
1-Acme INC,9000-CSD,6003-CSD IT,2-Downtown,IT Technician,user1
1-Acme INC,9000-CSD,6002-CSD HR,2-Downtown,HR Generalist,user2

What I really want is this:

Company,Division,Department,Location,Job Title,Username
Acme INC,CSD,CSD IT,Downtown,IT Technician,user1
Acme INC,CSD,CSD HR,Downtown,HR Generalist,user2

Basically I just want to strip the "[numeric]-" portion of that field. I have searched high and low and found different variations of replacing a CSV string with powershell, but haven't found the right syntax to replace variable text in a CSV using powershell.

This is what I have so far. When I check the output, nothing has changed.

$csv = Import-Csv .\test.csv
$csv | %{ $_ -replace "^[0-9]+-"}
$csv| Export-Csv .\test.csv -NoTypeInformation -Force

Any help would be greatly appreciated!

Why not process the csv file as a text file? You can still import the resulting output file as a csv. — Walter Mitty
– Walter Mitty, Commented Apr 17, 2015 at 21:34

Matt · Accepted Answer · 2015-04-18 00:27:37Z

2

You need to use:

$csv = get-content .\test.csv
$csv = $csv -replace "[0-9]+-", ""
$csv | out-file .\test.csv

or line 2 could be just:

$csv = $csv -replace "[0-9]+-"

edited Apr 18, 2015 at 0:27

Matt

47k9 gold badges90 silver badges125 bronze badges

answered Apr 17, 2015 at 20:31

Tony Hinkle

4,7627 gold badges25 silver badges35 bronze badges

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

7 Comments

jdubs Over a year ago

When I tried that, it actually killed all of my text in the CSV file and replaced it with this: Length 388

Tony Hinkle Over a year ago

Need line feeds to do this right, but hopefully you can figure it out. $csv = "1-Acme INC, 9000-CSD, 6003-CSD IT" $csv = $csv -replace "[0-9]+-" $csv returns Acme INC, CSD, CSD IT for me.

Tony Hinkle Over a year ago

Note that I've removed the ^ as that caused it to only replace the numbers at the beginning of the line.

jdubs Over a year ago

Tony, this is what I was trying to get away from. We have thousands of possible options here, so I may as well not script it if I have to enter each possibility. I was hoping to use regex to just replace the "numeric-" entries.

Tony Hinkle Over a year ago

The import-csv I think is the sticking point here. I was using the $csv variable as if you were using get-content. Let me test it...

|

Collectives™ on Stack Overflow

Replace variable in CSV using Powershell

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related