Ruby Script read excel file to then read a directo

Ruby
1

at the moment i have a excel spreadsheet with a list of the current
images by product code (17153, 17154 etc) and i have to go through the
directory manually searching for each image associated with the product
( 17153.jpg, 17153a.jpg, 17153b.jpg, 17154.jpg etc)

I am wanting to create a script that goes through a excel file cell by
cell (column A) to then find the corresponding images (17153.jpg,
17153a.jpg, 17153b.jpg, etc) in a directory and then copy these image in
another directory

Regards

2

Treat this as a step-by step process:

Step 1: Read the data from the spreadsheet. Try a gem like
“spreadsheet”, “roo”, or any of the other great ones which can handle
reading excel documents.

Step 2: Find the files. Look up Dir.glob or Ruby’s “Find”.

Step 3: Move the files. Look up Ruby’s File or Fileutils. There are
several options there.

3

I have now come up with this script that does part one but im getting a
error on part 2

dont know if anyone could help

regards

require ‘fileutils’
require ‘spreadsheet’

input = “/Users/eccleshall/ImageCleanUp/” #where images are stored
output = “/Users/eccleshall/ImageCleanUp/Keep” #where images will be
copied to

book = Spreadsheet.open
‘/Users/eccleshall/Desktop/ImageCleanUpScript/B002.xls’ #opens workbook

sheet1 = book.worksheet 0 # sets worksheet

sheet1.each do |row| #for each row output
puts row

Dir.glob("/Users/eccleshall/ImageCleanUp/" row).each do|f| #search

/Users/eccleshall/ImageCleanUp/ for files startig with row
puts f
end
end

error i’m getting

ImageCleanUp.rb:14: syntax error, unexpected tIDENTIFIER, expecting ‘)’
…s/eccleshall/ImageCleanUp/" row).each do|f| #search /Users/e…

4

Excel File and directory are set up like this

Excel File Directory
24005 24005.jpg
20007 20007.jpg
20008 20007a.jpg
20009 20008.jpg
20010 20008a.jpg
20021 20010.jpg
20022 20022.jpg
20024 20022a.jpg
20022b.jpg
20024.jpg

5

You’re missing the wildcard from the search, which would defeat the
point of using glob, that case you might as well just specify the
filename directly.

Isn’t “row” an array? I’m not sure you can add the array to a string
like that. To debug, try breaking this down into smaller steps, each
with their own variable assignation and debugging output.

Compare the filename strings you’re picking up from the sheet to the
real filenames you have to check for differences.

I dont’ know how the spreadsheet data compares to the file itself, but
you could try something like
("/Users/eccleshall/ImageCleanUp/#{ row[0] }*")
That is: your directory path + the first cell of data in your row + a
wildcard to account for file extensions.