Skip to content

Instantly share code, notes, and snippets.

@mrowe
Created August 3, 2010 01:07
Show Gist options
  • Save mrowe/505633 to your computer and use it in GitHub Desktop.
Save mrowe/505633 to your computer and use it in GitHub Desktop.
;;
;; Read a CSV file and look up the product ids it contains in a
;; database. Report all the products in the CSV that do not exist in
;; the database.
;;
;; Usage: $0 <path-to-csv-file>
;;
(import 'java.io.FileReader 'au.com.bytecode.opencsv.CSVReader)
(use 'clojure.contrib.str-utils)
(use 'clojure.contrib.sql)
;; OpenCSV gives us a List of String[]s... ugh.
(defn read-csv [file-name]
(with-open [reader (CSVReader. (FileReader. file-name))]
(rest ;; skip the header row
(map seq (seq (. reader readAll))))))
;; extract interesting fields from a CSV row
(defn product-from [row]
{:product-id (nth row 0 "")
:title (nth row 1 "")})
;; set up the db connection
(def db {:classname "org.h2.Driver"
:subprotocol "h2"
:subname (str "file:///Users/mrowe/.h2data/mydata")
:user "sa"
:password ""})
(defn sql-query [q]
(with-query-results res q (doall res)))
(defn count-products [product-id]
(:count
(first
(sql-query ["select count(1) as count from product where id = ?" product-id]))))
(defn exists? [product-id]
(>= (count-products product-id) 1))
(defn product-missing? [csv-row]
(let [product (product-from csv-row)]
(not (exists? (product :product-id)))))
;;;;;;;;;;
(def filename (first *command-line-args*))
(def feed (read-csv filename))
(defn report-product-id [row]
(let [product (product-from row)]
(format "Not in product catalog: %s - %s" (product :product-id) (product :title))))
(with-connection db
(println (str-join "\n" (map report-product-id (filter product-missing? feed)))))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment