How to combine columns in a CSV using PowerShell

Question

I am trying to automate a CSV file that contains lots of rows of data. Here is a sample:

= ID = Last Name = User ID =
============================
= 22 =  Smith    =  0077   =
= 22 =  Smith    =  0078   =
= 22 =  Smith    =  0079   =
= 22 =  Jones    =  0081   =

and the list goes on.

What I want to do is combine the ID column with the Last Name column and put it in a new CSV file using PowerShell. I tried the following:

@{n=’ID’;e={$_.ID + ”-” + $_.Last Name}}

but did not have much luck with this.

Shay Levy · Accepted Answer · 2013-08-27 08:13:14Z

1

I would first start making the content csv compatiable:

Get-Content file.txt | 
Where-Object {$_ -match '^\s+=\s.+\s+=$'} | 
ForEach-Object { $_ -replace '^\s+=\s*|\s+=$' -replace '\s+=\s+',','} | 
ConvertFrom-Csv -Header ID,LastName,UserID

ID LastName UserID
-- -------- ------
22 Smith    0077  
22 Smith    0078  
22 Smith    0079  
22 Jones    0081

And then use Select-Object to create a new column:

...
ConvertFrom-Csv -Header ID,LastName,UserID | 
Select @{n=’ID’;e={$_.ID + '-' + $_.LastName}},UserID

ID       UserID
--       ------
22-Smith 0077  
22-Smith 0078  
22-Smith 0079  
22-Jones 0081

answered Aug 27, 2013 at 8:13

Shay Levy

128k34 gold badges193 silver badges210 bronze badges

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

1 Comment

Timo Over a year ago

Can you explain the match and replace strings? \s is a string? ^ is beginning and $ is end.

Mitul · Accepted Answer · 2013-08-27 02:09:30Z

Your column headers have space in them and that should be the first thing you can fix. But other than that this is my sample emp.csv file, script and then newemp.csv as solution.

id  loginid firstname   lastname
1   abc123  John    patel
2   zxy321  Kohn    smith
3   sdf120  Maun    scott
4   tiy123  Dham    rye
5   k2340   Naam    mason
6   lk10j5  Shaan   kelso
7   303sk   Doug    smith

Script

$objs =@();
$output = Import-csv -Path C:\Scripts\so\emp.csv | ForEach { 
$Object = New-Object PSObject -Property @{            
        Computed  = [String]::Concat($_.id,$_.lastname)
        Firstname = $_.firstname            
    }   
    $objs += $Object;
} 
$objs 
$objs | Export-CSv C:\Scripts\so\newemp.csv

Computed    Firstname
1patel  John
2smith  Kohn
3scott  Maun
4rye    Dham
5mason  Naam
6kelso  Shaan
7smith  Doug

Collectives™ on Stack Overflow

How to combine columns in a CSV using PowerShell

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related