CODEWITHSUNDEEP
powershellcsv
user4537481
user4537481

Reputation:

Powershell: Merge multiple lines into one

I have a .csv file which looks like:

employeenumber;phone;mobile;fax;userid;Email
99999991;+1324569991;+234569991;+5234569991;user01;[email protected]
99999992;+1234569992;+234569992;;user02;[email protected]
99999993;+1234569993;+234569993;;user03;[email protected]
99999993;+12345699933;;;user03;[email protected]
99999993;;;+5234569993;user03;[email protected]
99999994;+1234569994;;;user04;[email protected]

As you can see there are different employeenumbers and some lines with the same employeenumber.

Is there any way to merge the lines with the same employeenumber in powershell?

Similar Output:

employeenumber;phone;mobile;fax;userid;Email
99999991;+1324569991;+234569991;+5234569991;user01;[email protected]
99999992;+1234569992;+234569992;;user2;[email protected]
99999993;+1234569993 / +12345699933;+234569993;+5234569993;user03;[email protected]
99999994;+1234569994;;;user04;[email protected]

Thank you

Upvotes: 0

Views: 1679

Answers (2)

FoxDeploy
FoxDeploy

Reputation: 13537

I've taken a shot at it. I believe my answer is easier to read than Mjolinor's.

I group the entries from the CSV into either $singletons or $duplicates, based on using the Group-Object command. Then, I pipe through the $duplicates and merge the records found in either the phone,mobile, or fax fields, using a '/' character as you've indicated.

#$csv = get-content .\CSVNeedstoMerge.csv
$csvValues = $csv | ConvertFrom-Csv -Delimiter ';'
$duplicates = $csvValues | group-object EmployeeNumber | ? Count -gt 1
$objs = New-Object System.Collections.ArrayList

$singletons = $csvValues | group-object EmployeeNumber | ? Count -eq 1  | %     {$objs.Add($_.Group)}

ForEach ($duplicate in $duplicates){
$objs.Add([pscustomobject]@{employeenumber=($duplicate.Group.employeenumber  | select -Unique) -as [int];
    phone=($duplicate.Group.phone | ? Length -gt 0) -join '/';
    mobile=($duplicate.Group.mobile| ? Length -gt 0) -join '/';
    fax=($duplicate.Group.fax | ? Length -gt 0) -join '/';
    userid = $duplicate.Group.userid | select -Unique
    email= $duplicate.Group.email  | select -Unique })
}

$objs | Sort EmployeeNumber

Upvotes: 1

mjolinor
mjolinor

Reputation: 68273

I'll give that a shot:

(@'
employeenumber;phone;mobile;fax;userid;Email
99999991;+1324569991;+234569991;+5234569991;user01;[email protected]
99999992;+1234569992;+234569992;;user02;[email protected]
99999993;+1234569993;+234569993;;user03;[email protected]
99999993;+12345699933;;;user03;[email protected]
99999993;;;+5234569993;user03;[email protected]
99999994;+1234569994;;;user04;[email protected]
'@).split("`n") |
foreach {$_.trim()} | sc test.csv

$ht = @{}
$props = (Get-Content test.csv -TotalCount 1).split(';')

import-csv test.csv -Delimiter ';' |
foreach {
 if ( $ht.ContainsKey($_.employeenumber) )
  {
    foreach ($prop in  $props )
     { 
       if ($_.$prop )
        {$ht[$_.employeenumber].$prop = $_.$prop }
     }
  }

  else { $ht[$_.employeenumber] = $_ }
 }

 $ht.values | sort employeenumber



employeenumber : 99999991
phone          : +1324569991
mobile         : +234569991
fax            : +5234569991
userid         : user01
Email          : [email protected]

employeenumber : 99999992
phone          : +1234569992
mobile         : +234569992
fax            : 
userid         : user02
Email          : [email protected]

employeenumber : 99999993
phone          : +12345699933
mobile         : +234569993
fax            : +5234569993
userid         : user03
Email          : [email protected]

employeenumber : 99999994
phone          : +1234569994
mobile         : 
fax            : 
userid         : user04
Email          : [email protected]

Upvotes: 1

Related Questions