Solved

Powershell add new column to CSV from other CSV or txt

Posted on 2013-05-17
7
3,338 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_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 40

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 40

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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
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 70

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 40

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 70

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Utilizing an array to gracefully append to a list of EmailAddresses
This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

691 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