• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4102
  • Last Modified:

Powershell add new column to CSV from other CSV or txt


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





appreciate for your help
3 Solutions
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?
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.
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
$file1 | Export-CSV result.csv -notype

Open in new window

WEBINAR: GDPR Implemented - Tips & Lessons Learned

Join the WatchGuard team on Thursday, March 29th as we recount some valuable lessons learned in weighing the needs of a business against the new regulatory environment, look ahead at the two months left before implementation, and help you understand the steps you can take today!

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
QlemoBatchelor and DeveloperCommented:
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
(b) merge by trying to match given name and last name.
But I don't know if it is worth the effort ...
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?
QlemoBatchelor and DeveloperCommented:
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?
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.

Join & Write a Comment

Featured Post

We Need Your Input!

WatchGuard is currently running a beta program for our new macOS Host Sensor for our Threat Detection and Response service. We're looking for more macOS users to help provide insight and feedback to help us make the product even better. Please sign up for our beta program today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now