Solved

Powershell add new column to CSV from other CSV or txt

Posted on 2013-05-17
7
2,755 Views
Last Modified: 2013-05-24
Hi,

i like to combine 2 CSV's and add new column and save as result to new csv

e.g.

test1.csv
Firstname,lastname
Pedro,Martinez
Steve,Hawker

test2.csv
pedro.martinez@test.com
steve.hawker@test.com

Result.csv
Firstname,lastname,email
Pedro,Martinez,pedro.martinez@test.com
Steve,Hawker,steve.hawker@test.com

appreciate for your help
mandy
0
Comment
Question by:Mandy_
7 Comments
 
LVL 39

Expert Comment

by:footech
ID: 39176353
Honestly if I had to do this I would just open both files in Excel and copy and paste the one column into the other file.

Unless you have to do this a lot?
0
 
LVL 16

Expert Comment

by:Rajitha Chimmani
ID: 39176357
If it is a one-one  mapping you can do it from MS Excel itself.

Even with Powershell it does the same only with one-one mapping. It cannot be intelligent enough to match the email address based on their names by just using import/export of csv.
0
 
LVL 39

Assisted Solution

by:footech
footech earned 333 total points
ID: 39176374
This code would do what you want, with the following requirements:
 - the 2nd file needs a header named "email"
 - all the addresses need to be listed in the same order as the names they will match from the first file
$file1 = Import-CSV test1.csv
$file2 = Import-CSV test2.csv
$i = 0
$file1 | ForEach `
{
  $_ | Add-Member -type NoteProperty -name email -value $file2[$i].email
  $i++
}
$file1 | Export-CSV result.csv -notype

Open in new window

0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 2

Author Comment

by:Mandy_
ID: 39176757
Thanks for your help. just one question. How could write this in one line

$file = "c:\result.csv"
(gc $file) -replace(' " ',' ') | Out-File $file -Force
(gc $file) -replace(' , ',' ; ') | Out-File $file -Force
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39177356
If you use the Replace method instead of the operator, it is getting a little more straightforward:
$file = "C:\Result.CSV"
(gc $file).Replace(' " ',' ').Replace(' , ',' ; ') | Out-File -Force $file

Open in new window

Now to the merge: If we can be sure the email address is constructed the same way, we can
(a) just add a "calculated" email address to the original csv
or
(b) merge by trying to match given name and last name.
But I don't know if it is worth the effort ...
0
 
LVL 39

Assisted Solution

by:footech
footech earned 333 total points
ID: 39177571
So if I'm interpreting your goal correctly, you don't want the resulting merged CSV to have quotes and should be delimited by semi-colons instead of commas.  The way I would do it is to skip creation of the result.csv until the end so we don't have to write it, read it back in, then write it out again.  My suggestion would be to replace line 9 in my code with the following.
($file1 | ConvertTo-CSV -delimiter ";" -notype) -replace '"' | Out-File test3.csv -force

Open in new window


However, that may not be what you're after at all, since it looks like you're trying to match against quotes surrounded by spaces, and commas surrounded by spaces.  By the way, I noticed Qlemo's method only works if you're using PS 3.0.

Can you clarify what you're trying to do with the replace?
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 167 total points
ID: 39177825
footech is correct about my code and PS 3. It is the way it should have worked with PS 2, but was never implemented. With PS 2, we need the replace operator or a foreach loop. I'll show the former:
$file = "C:\Result.CSV"
(gc $file) -replace ' " ',' ' -replace ' , ',' ; ' | Out-File -Force $file

Open in new window

I take it you use that commands to generate the files to merge?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Synchronize a new Active Directory domain with an existing Office 365 tenant
This article will help you understand what HashTables are and how to use them in PowerShell.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now