Manipulating CSV files and getting two new columns

I’m having some fun with CSV files and I have one CSV file with these headers:

primaryEmail,customSchemas.Designations.Designation0,name.familyName,name.fullName,name.givenName,organizations,organizations.0.customType,organizations.0.department,organizations.0.name,organizations.0.primary,organizations.0.title,phones,phones.0.type,phones.0.value,phones.1.type,phones.1.value,phones.2.type,phones.2.value,phones.3.type,phones.3.value,phones.4.type,phones.4.value 

So first things first, I’d like to keep the following headers in my new CSV.

  • primaryEmail
  • name.FullName
  • organizations.0.title
  • customSchemas.Designations.Designation0

I would also like to keep phone numbers but they currently look like this.

primaryEmail phones.0.type phones.0.value phones.1.type phones.1.value
[Myemail@domain.local](mailto:Myemail@domain.local) work 123.456.7890 mobile 987.654.3211
[someoneelse@domain.local](mailto:someoneelse@domain.local) mobile 987.654.3210 work 123.456.7891

I function my way out of that fun and end up with the correct $worknumber and $mobilenumber.

Now I need some way to generate a new CSV file that essentially just is

Do I Import it and select my way out of it?

primaryEmail FullName Title Designation WorkNumber MobileNumber
[Myemail@domain.local](mailto:Myemail@domain.local) FirstName LastName IT Janitor BSc 123.456.7890 987.654.3211
[someoneelse@domain.local](mailto:someoneelse@domain.local) FirstName LastName IT Director MSc 123.456.7891 987.654.3210

This is what I have at the moment:

$DebugPreference = "Continue" #$DebugPreference = "SilentlyContinue" #Clear-Host #region functions function Get-WorkNumber { Param ( [Parameter(Mandatory = $true, Position = 0)] [string] $UserEmail ) Write-Debug "Running Get-WorkNumber function on $(($row.primaryEmail).ToLower())" foreach ($row in $csv) { if ($row.PrimaryEmail -eq "$UserEmail") { foreach ($Index in 0..5) { switch ($row."Phones.$Index.Type") { "work" { $worknumber = $row."Phones.$Index.Value" $worknumber } } } } } } function Get-Mobilenumber { Param ( [Parameter(Mandatory = $true, Position = 0)] [string] $UserEmail ) Write-Debug "GET-MOBILENUMBER - Running Get-Mobilenumber function on $(($row.primaryEmail).ToLower())" foreach ($row in $csv) { if ($row.PrimaryEmail -eq "$UserEmail") { foreach ($Index in 0..5) { switch ($row."Phones.$Index.Type") { "mobile" { $mobilenumber = $row."Phones.$Index.Value" $mobilenumber } } } } } } #endregion Functions $csv = import-csv "C:gam-workrawdata.csv" foreach ($row in $csv) { $Get_worknumber = "" $Get_Mobilenumber = "" $Get_worknumber = get-worknumber -UserEmail $row.PrimaryEmail $Get_Mobilenumber = get-mobilenumber -UserEmail $row.PrimaryEmail $Get_worknumber $Get_Mobilenumber ###Something export-csv "C:gam-workfixeddata.csv" } 

submitted by /u/sysadminworkaccount
[link] [comments]

Leave a Reply