0

I have a csv file with list of people (first name, last name, email) and then later on I will get a list of people in another csv file that unsubscribed ( only one column with email).

Q: How can I delete all rows from the first file in case the row contain email of someone that unsubscribed?

I was thinking of using a spreadsheet (MS Excel, Open Office, google) but I can use any other solution.

I tried MS Excel but simple remove duplicate is not working. I guess I need to use other approach. But do not know what to do next.

Note that I need to export the 'clean' csv into new file. Of course I don't want the file to have the 'unsubscribed' email.

Improve this question
3
  • We aren't here to tell you what program to use and how to do it. We are here to help you when you tell us what you have tried. Decide what you are going to use and share where you get stuck.
    – CharlieRB
    Commented Feb 10, 2015 at 23:33
  • I tried MS Excel but simple remove duplicate is not working. I guess I need to use other approach. But do not know what to do next.
    – Radek
    Commented Feb 10, 2015 at 23:37
  • @sunk818: it's a great idea. It deserves an answer...
    – Radek
    Commented Feb 11, 2015 at 22:21

2 Answers 2

Reset to default
2

If you use Microsoft Access, you can just link to the unsub CSV (assuming it is the same file every time with just different e-mails). Then create a query an update query that will flag your subscribers with an unsubscribe flag. Perhaps that same query can update another column with the current date/time. You can then run another query that will export updated table to a clean CSV. You can do all of this as a Macro in Access. As long as your linked CSV file is the same, everything else can be automatically processed and a clean CSV be created in a matter of seconds.

Improve this answer
1
  • That's why asked for suggestion of other kinds of solution. I think that using db might be bit easier and quicker to do.
    – Radek
    Commented Feb 11, 2015 at 23:00
1

I have a semi-automated way to do this in Excel:

  1. Import the CSV files into two sheets. I called them MasterList and UnsubList
  2. Sort the UnsubList (Sort A to Z) using the Sort & Filter button
  3. In the MasterList, I created another column called Unsub?

  4. In each row under the Unsub? column, enter the following formula:

    =IF(C2=VLOOKUP(C2,UnsubList!$A$2:$A$1000,1),"Unsub","Keep")

    In this example, it would be for the 2nd row. If the unsub list has more than 999 entries, you need to make the range bigger.

  5. Then turn on Filter (under the Sort & Filter button). You should have something similar to the screenshot below.

  6. Unselect only the Keep rows using the dropdown for the Unsub? column. This means that only the rows that require removal will remain visible
  7. Select all these rows except the heading by dragging the row numbers from top to bottom, right click, and click Delete in the pop-up menu
  8. Now they are gone from the MasterList
  9. Delete the Unsub column and re-export to csv

MasterList

UnsubList

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .