De-duplicate and then show unique names/strings

Question

Working on SEO, I have a client site that has hundreds, maybe thousands, of images uploaded.

I have crawled the site with Screaming Frog SEO spider and have segmented crawl data to list all images in use on the site and I have downloaded a local copy of the folder where the images are uploaded.

I don't need to compare the actual files just 2 lists of the names of all files (these or other files, tasks like this are fairly common for me).

I'm comfortable with Sublime Text (and its multiple cursors and advanced find and replace functions) and I'm familiar with command line tools like grep. (Mac user)

I assume that the first step would be to eliminate duplicates within each list, and then compare the lists which would yield a list of files which are in both "list 1" and "list 2" and/or which are not in "list 1" but are in "list 2."

Answer 1

You can use VLOOKUP() function in Excel (or in equivalent spreadsheet). It is the fastest way and requires no programming. The VLOOKUP is well-described across the internet. But if you get stuck somewhere, add the comment and I'll help you.

Possible limitation is height limit of sheet - it can have up to 1,048,576 rows.

Answer 2

When I looked into both answers above they were "right" and I marked them as such.

In sorting it out a friend of mine told me about the Bash command for "unique."

Turns out it is uniq used in conjunction with sort.

Once I knew the name, a quick search pulled up this Stackoverflow answer.

Answer 3

I would use python, (already installed on mac/linux/unix and available for windows), something like:

from glob import glob

actual_files = set(glob('/path/to/images/*.jpg')) # The actual files as a set

with open('list/of/Images/Referenced') as f: # Assuming that you have the referenced files as one per line with just the image tags
   rf = [fp for fp in f.readlines().split('/')[-1]] # You only need the actual filename
referenced_files = set(rf) # Make a list of unique references
unused = actual_files - referenced_files
print('Files Present But Unused:', unused)
missing = referenced_files - actual_files
print ('Files Referenced But Missing;', missing)

N.B. I have not tested the above and am assuming that your list of files is one path to the file per line with nothing else on the line.

Note that you could use a python crawler, (scrapy), to crawl the site and remote ssh, also from within python, to get the listing to automate the whole process if you need to do something like this regularly.

Answer 4

Given two sorted files, the command comm prints out the lines that are unique to one file or the other. You'll need to remove the directory part first, which sed. Use comm -12 to see the common lines, comm -23 to see lines unique to the first file and comm -13 to see lines unique to the second file.

<remote-name-list.txt sed -e 's!.*/!!' | sort >remote-name-list.sorted.txt
<local-name-list.txt sed -e 's!.*/!!' | sort >local-name-list.sorted.txt
comm -23 remote-name-list.sorted.txt local-name-list.sorted.txt >remote-only.txt

If you only want to see a list of names with no duplication, you can use sort -u. If you're interested in the duplicates, pipe into uniq.

<local-name-list.txt sed -e 's!.*/!!' | sort | uniq -c | grep -v '^ *1 '

If at some point you want to compare the file contents, you can use fdupes (available in Homebrew). To search for visually similar images, you can use findimagedupes.