1

I have multiple separate CSV files that are just a single column list of email addresses, where each file represents a 'tag' associated with that email. I'd like to merge them into one CSV file, such that all email addresses in any files are listed in the first column, and all other columns are the file names and a true/false or 1/0 if the email is in that file.

Which software would have this feature, and how would I implement it? I own the latest version of Excel if that can do it.

  • 1
    Do you have any programming experience? This would be pretty easy in Python with Pandas. – Eric Shain May 15 '17 at 14:38
  • @EricShain I do have some programming experience. It's been a long time, but I could tweak a template for sure. I just watched an intro video explaining Pandas and wasn't sure which way to implement my request based on what I saw. Would you know of an example I could base what I need on, or should I just ask that question in one of the programming stackexchanges? – Benny Lewis May 15 '17 at 15:02
  • 1
    There are enormous resources just a Google search away. Here is one from StackOverflow: stackoverflow.com/questions/36749741/…. If you don't have Python and Pandas installed, I recommend using the Anaconda distribution: continuum.io/why-anaconda – Eric Shain May 15 '17 at 18:12
  • @EricShain Thank you! The Anaconda distribution installation went well, and I'm completing a well-reviewed course on pandas to get what I need down. So I should be good to go now. Appreciate the help! – Benny Lewis May 16 '17 at 4:17
  • I would think bash would be plenty for this... – Evan Carroll May 18 '17 at 4:56
0

Perl is great for this.. You can also whip up an easy bash one liner too. This should work though.

#!/usr/bin/env perl
use strict;
use warnings;

use Text::CSV_XS;

my $csv = Text::CSV_XS->new;
my %db, @tags;

while (my $email = <<>>) {
  chomp $email;
  push @tags, $ARGV;
  $db{$email}{$ARGV} = 1;
}

# Header
$csv->say(*STDOUT, ["Email", map $_, @tags]);

foreach my $email (keys %db) {
  $csv->say(*STDOUT, [$email, map $db{$email}{$_}//0, @tags]);
}

If you don't have Text::CSV_XS, you can install it with

cpan Text::CSV_XS;

Save this to a file, runme, chmod u+x runme, and then ./runme *

0

In the end, it seems like there are two ways to handle this based on the level of experience I had, that I can share here in case anyone else is in a similar situation.

  1. Very simply do it in Excel using a macro. This SO answer explains well how to do a basic merge on common values. If you want to solve the problem quickly, this is likely your best bet.
  2. As Eric Shain said in the first comment, the pandas library within Python is designed especially for things like this, and allows for much more interesting possibilities. Since I needed more flexibility and the ability to do more down the road, I went with this solution, since simple programming within Python is very easy, even if you are rusty as I was. See the third comment for excellent links to get started!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.