Solved

Powershell add new column to CSV from other CSV or txt

Posted on 2013-05-17
7
2,670 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 2

Author Comment

by:Mandy_
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Are you one of those front-line IT Service Desk staff fielding calls, replying to emails, all-the-while working to resolve end-user technological nightmares? I am! That's why I have put together this brief overview of tools and techniques I use in o…
Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

743 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

15 Experts available now in Live!

Get 1:1 Help Now