Powershell csv combine two columns and delete data after space

Question

I have a csv that will need to be scripted to update monthly it currently looks like this

UserID    Name
120       john doe
121       jane doe
122       mike name
123       joe blow

I need to keep the existing UserID and add the UserID data before the Name and delete the last name.

UserID    ID
120       120-john
121       121-jane
122       122-mike
123       123-joe

I have figured out renaming removing columns but I have no idea how to combine data from one to the other and remove the last name....

any help would be amazing! even if you can just fill in part of the issue I can keep playing

are you're saying the first example (w/ UserID,Name) is your input file? .. and the second example (w/ UserID,ID) is what you want the output to look like? — Ricc Babbitt
– Ricc Babbitt, Commented Jan 5, 2018 at 6:28

Ricc Babbitt · Accepted Answer · 2018-01-05 07:03:46Z

3

This can be done easily by importing the csv and passing it to Select-Object with a calculated property...

Import-Csv .\Input.csv | Select-Object UserID, @{n="ID";e={$_.UserID + "-" + $_.Name.Split(" ")[0]}}

And Pipe to Export-Csv if you want to save the output...

Import-Csv .\Import.csv | Select-Object UserID, @{n="ID";e={$_.UserID + "-" + $_.Name.Split(" ")[0]}} | Export-Csv -NoTypeInformation Output.csv

So what's going on here? In the second part of my select statement, (the @{..} part) :

@{n="ID";e={$_.UserID + "-" + $_.Name.Split(" ")[0]}}

.. I'm saying, the name (n=) of the column is "ID", and the value is determined by the expression (e=), which is between the next pair of braces.
And that expression is saying, take the UserID ($.UserID), plus a hyphen and append the first part of my $.Name column (split by spaces).

Hope that helps (and made sense), Cheers :)

edited Jan 5, 2018 at 7:03

answered Jan 5, 2018 at 6:38

Ricc Babbitt

3921 silver badge7 bronze badges

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

Collectives™ on Stack Overflow

Powershell csv combine two columns and delete data after space

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related