I am trying to analyze some email data but lack the scripting skills to work with the source files (csv text files).
The script should be able to take two source files and build a third using content from both files.
Source files CSV_FILE1 and CSV_FILE2:
CSV_FILE1 contains information related to the user with the e-mail address "email", this includes gender, nationality and location.
CSV_FILE1 has +/- 5000 lines
email, gender, nationality, location
, "M;MEX", LOC\001
, "F;USA", LOC\001
, "F;GER", EUR\002
CSV_FILE2 is an excerpt from the email tracking logs and has sender information "email_from", date information and recipient information "email_to"
CSV_FILE2 has millions of lines (more than excel can handle)
email_from, email_to, date
, user2ATdomain,com, 09\29\2014 16:02:00
, user1ATdomain,com, 09\29\2014 16:03:00
, "user2ATdomain,com; ", 09\29\2014 16:04:00
The output file should list all the transactions from CSV_FILE2 as follows:
- replace the email_to and email_from addresses with one of the fileds of CSV_FILE1 - for example nationality
- if more than one email_to is listed, split the line so that there is one transaction per recipient
Example CSV_FILE3 if I'm looking to report on "nationality":
email_to_nat, email_from_nat, date
MEX, USA, 09\29\2014 16:02:00
USA, MEX, 09\29\2014 16:03:00
MEX, USA, 09\29\2014 16:04:00
MEX, GER, 09\29\2014 16:04:00
You may suggest the best method for achieving this. Ideally it should be something that I can replicate without much effort. My environment is Windows 8. If for example this can be done with PowerShell that would be great.
I would like to have access to the script and understand it enough that I can modify it to work with different files and report on different properties. If you need more details please contact me.
8 freelancers are bidding on average $77 for this job
we would suggest using excel macro for this - should be easy for you to maintain... please share your CSV files, please reply, we can discuss further, thank you !
Hi, I'm writing python scripts. I think I can help you handle this task. If you're interested I can provide some more detailed design of the script. On Windows 8 python should work normaly after instalation.