Add Column to CSV file Using Other CSV file

Question

I am working with two CSV files. One holds the name of users and the other one holds their corresponding email address. What I want to do is to combine them both so that users is column 1 and email is column 2 and output it to one file. So far, I've managed to add a second column from the email csv file to the user csv file, but with blank row data. Below is the code that I am using:

$emailCol= import-csv "C:\files\temp\emailOnly.csv" | Select-Object -skip 1
$emailArr=@{}
$i=0

$nameCol = import-csv "C:\files\temp\nameOnly.csv"

foreach ($item in $emailCol){
                             $nameCol | Select *, @{
                                                    Name="email";Expression= 
                                                    {$emailArr[$i]}
                                                    } | Export-Csv -path 
                             C:\files\temp\revised.csv -NoTypeInformation
                             }

Updated: Below is what worked for me. Thanks BenH!

function combineData    {
                        #This function will combine the user CSV file and 
                        #email CSV file into a single file

                        $emailCol = Get-Content "C:\files\temp\emailOnly.csv" 
                        | Select-Object -skip 1
                        $nameCol = Get-Content "C:\files\temp\nameOnly.csv" | 
                        Select-Object -skip 1

                        # Max function to find the larger count of the two 
                        #csvs to use as the boundary for the counter.
                        $count = [math]::Max($emailCol.count,$nameCol.count)

                        $CombinedArray = for ($i = 0; $i -lt $count; $i++)  {
                                                    [PSCustomObject]@{
                                                    fullName = $nameCol[$i]
                                                    email = $emailCol[$i]
                                                                     }
                                                                            }

                        $CombinedArray | Export-Csv C:\files\temp\revised.csv 
                        -NoTypeInformation

                        }

user9952217 · Accepted Answer · 2018-06-20 20:35:18Z

2

To prevent some additional questions about this theme let me show you alternative approach. If your both CSV files have same number of lines and each line of the first file corresponds to the first line of the second file and etc. then you can do next. For example, users.csv:

User
Name1
Name2
Name3
Name4
Name5

and email.csv:

Email
[email protected]
[email protected]
[email protected]

[email protected]

Our purpose:

"User","Email"
"Name1","[email protected]"
"Name2","[email protected]"
"Name3","[email protected]"
"Name4",
"Name5","[email protected]"

What we do?

$c1 = 'C:\path\to\user.csv'
$c2 = 'C:\path\to\email.csv'
[Linq.Enumerable]::Zip(
  (Get-Content $c1), (Get-Content $c2),[Func[Object, Object, Object[]]]{$args -join ','}
) | ConvertFrom-Csv | Export-Csv C:\path\to\output.csv

If our purpose is:

"User","Email"
"Name1","[email protected]"
"Name2","[email protected]"
"Name3","[email protected]"
"Name5","[email protected]"

then:

$c1 = 'C:\path\to\user.csv'
$c2 = 'C:\path\to\email.csv'
([Linq.Enumerable]::Zip(
  (Get-Content $c1), (Get-Content $c2),[Func[Object, Object, Object[]]]{$args -join ','}
) | ConvertFrom-Csv).Where{$_.Email} | Export-Csv C:\path\to\output.csv

Hope this helps you in the future.

answered Jun 20, 2018 at 20:35

user9952217

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

2 Comments

Lilith Mae Over a year ago

Wow! Thanks for the tips @greg!

f6a4 Over a year ago

Great, I like Linq-Adaptions in PS

BenH · Accepted Answer · 2018-06-20 18:52:29Z

1

A for loop would be better suited for your loop. Then use the counter as the index for each of the arrays to build your new object.

$emailCol = Get-Content "C:\files\temp\emailOnly.csv" | Select-Object -Skip 2
$nameCol = Get-Content "C:\files\temp\nameOnly.csv" | Select-Object -Skip 1

# Max function to find the larger count of the two csvs to use as the boundary for the counter.
$count = [math]::Max($emailCol.count,$nameCol.count)
$CombinedArray = for ($i = 0; $i -lt $count; $i++) {
    [PSCustomObject]@{
        Name = $nameCol[$i]
        Email = $emailCol[$i]
    }
}
$CombinedArray | Export-Csv C:\files\temp\revised.csv -NoTypeInformation

Answer edited to use Get-Content with an extra skip added to skip the header line in order to handle blank lines.

edited Jun 20, 2018 at 18:52

answered Jun 20, 2018 at 17:58

BenH

10.1k1 gold badge26 silver badges38 bronze badges

4 Comments

Lilith Mae Over a year ago

Great! This adds more progress! However, the objects in each row data display this way: Name Email @{displayName=Administrator} @{[email protected]} Also, one of the users do no have an email assigned, so they would need to be skipped. However, the code you've causes the email row to skip the blank line, which throws off the rest of the users corresponding email.

user9952217 Over a year ago

@LilithMae Maybe it's better show the parts of samples your CSV files first and explain all nuances what you actually want?

BenH Over a year ago

@LilithMae Answer updated, to get rid of the @{email= you would use the property that matched the header in your csv. e.g. Name = $nameCol.DisplayName[$i] However just using Get-Content solves your blank line issue as well.

Lilith Mae Over a year ago

@BenH This works out great! THanks for all your help :) While I was awaiting your response I realized that there were some scripts I ran previously that caused blank rows to be removed. After I modified that script, then this on worked. I've updated my question with what worked for me :) Thank you very much and have a nice day.

Collectives™ on Stack Overflow

Add Column to CSV file Using Other CSV file

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related