CODEWITHSUNDEEP
powershellcsvimportimport-csv
Koobah84
Koobah84

Reputation: 185

Import data from one CSV to another CSV in a specific column

I'm having some trouble copying data from 1 CSV and pasting it into a template of another one. The template has specific column names. and the csv file I have with the data, I'm able to get each column, but I am having trouble pasting it into a the template.

I'm trying to copy the following data from 1 csv to the template and here are the columns

email --> internal_customer_ID  
givenname --> first_name
surname --> last_name
mail --> email_address
mobilephone --> mobile_phone_1
officePhone --> landline_phone_1

Here is my current code. 

#Clear Screen
CLS

#The path we are working with (use the path where we execute this script from)
$global:path = Split-Path $script:MyInvocation.MyCommand.Path

$DataFile = $path + "\dataFile.csv"
$ExportedFileCSV = $path + "\PopulatedTemplate.csv"

#Export the data file with our user info
Get-ADGroupMember -Identity SomeGroup | Get-ADUser -Properties * | Select-Object -Property GivenName, SurName, Mail, MobilePhone, OfficePhone | Export-Csv -path $DataFile -NoTypeInformation -Force

$dataInput = Import-Csv $DataFile
$dataOutput = Import-Csv $ExportedFileCSV

$dataInput | ForEach-Object {

    $newData = $_

    $dataOutput | 
    Add-Member -MemberType NoteProperty -Name "internal_customer_ID" -Value $newData.Mail -PassThru -Force|
    Add-Member -MemberType NoteProperty -Name "landline_phone_1" -Value $newData.OfficePhone -PassThru -Force|
    Add-Member -MemberType NoteProperty -Name "email_address" -Value $newData.Mail -PassThru -Force|
    Add-Member -MemberType NoteProperty -Name "mobile_phone_1" -Value $newData.MobilePhone -PassThru -Force|
    Add-Member -MemberType NoteProperty -Name "last_name" -Value $newData.SurName -PassThru -Force|
    Add-Member -MemberType NoteProperty -Name "first_name" -Value $newData.GivenName -PassThru -force

} | Export-CSV $ExportedFileCSV

If I can avoid exporting the datafile in the first place and just appending the result from the 

Get-ADGroupMember -Identity SomeGroup | Get-ADUser -Properties * | Select-Object -Property GivenName, SurName, Mail, MobilePhone, OfficePhone

Straight to the csv template, that would work for my needs too, I just wasn't sure how to do that.

Upvotes: 1

Views: 3982

Answers (1)

veefu
veefu

Reputation: 2890

Your line reading $dataOutput | Add-Member ... is the problem, I think. Add-Member is for adding an attribute to a single object, but $dataOutput at this point is a collection of objects. I think the interpreter thinks you're trying add a member attribute to an object array.

Try creating a new object for each output record, then do an Export-CSV -append onto your output CSV file.

I think something like this should work:

$dataInput | ForEach-Object {

    $newData = $_
    $newRecordProperties = [ordered]@{
                    "internal_customer_ID"=$newData.Mail
                    "landline_phone_1" = $newData.OfficePhone 
                    "email_address" = $newData.Mail
                    "mobile_phone_1" = $newData.MobilePhone 
                    "last_name" = $newData.SurName
                    "first_name" = $newData.GivenName
                    }
    $newRecord = new-object psobject -Property $newRecordProperties
    Write-Output $newRecord

} | Export-CSV $ExportedFileCSV -Append

As long as the columns names in the output CSV are the same as your new record object, I think it should be okay. I am not sure what happens if the columns in $ExportedFileCSV are in a different order than the $newRecord being exported, so I added [ordered] to the hash table. You may want to test this yourself.

For the second part of your question, pipe-lining the whole thing, something like this is probably what you're after:

Get-ADGroupMember -Identity SomeGroup | 
    Get-ADUser -Properties * | 
    Select-Object -Property @(
        @{label="internal_customer_ID"; expression={$_.Mail}}
        @{label="email_address";        expression={$_.Mail}}
        @{label="landline_phone_1";     expression={$_.OfficePhone}}
        @{label="first_name";           expression={$_.GivenName}}
        @{label="last_name";            expression={$_.SurName}}
        @{label="mobile_phone_1";       expression={$_.MobilePhone}}
     ) |
    Export-Csv $ExportedFileCSV -Append

Select-Object above creates a custom object with the attribute name and attribute value matching label and the result of expression. Again, re-order to match the order the CSV columns should be in.

Upvotes: 2

Related Questions