Powershell add new column to CSV from other CSV or txt

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
LVL 2
Mandy_Asked:
Who is Participating?
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
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
 
footechCommented:
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
 
Rajitha ChimmaniCommented:
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
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
footechConnect With a Mentor Commented:
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
 
Mandy_Author Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
footechConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.