import csv into sql server

deps.edn

{:deps {seancorfield/next.jdbc {:mvn/version "1.0.13"}
        org.clojure/data.csv {:mvn/version "1.0.0"}
        clojure.java-time {:mvn/version "0.3.2"}
        com.microsoft.sqlserver/mssql-jdbc {:mvn/version "8.2.0.jre8"}}}

import-csv.clj

(ns import-csv
  (:require [next.jdbc.sql :as sql]
            [next.jdbc :as jdbc]
            [clojure.data.csv :as csv]
            [clojure.java.io :as io]
            [java-time :as jt]))

(def conversions {"datetime" #(some->> %
                                       (jt/local-date-time "yyyy-MM-dd HH:mm:ss.SSSSSSS"))})

(def datasource nil) ;; insert something here

(defn csv->maps
  [csv-path table-name]
  (let [fields (let [sql ["
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ?"
                          table-name]]
                 (->> (sql/query datasource sql)
                      (map (fn [{:keys [COLUMN_NAME] :as data}]
                             [(keyword COLUMN_NAME)
                              (assoc data :COLUMN_NAME
                                     (keyword COLUMN_NAME))]))
                      (into {})))
        
        NULLs->nils    (map (fn [[k v]]
                              [k (if (= "NULL" v) nil v)]))
        
        convert-values (map (fn [[k v]]
                              [k ((get conversions
                                       (get-in fields [k :DATA_TYPE])
                                       identity)
                                  v)]))
        
        maps (->> (let [[columns & rows] (with-open [reader (io/reader csv-path)]
                                           (doall
                                            (csv/read-csv reader)))
                        columns (map keyword columns)]
                    columns
                    (map (fn [cs]
                           (let [ms (map (fn [c d] [c d]) columns cs)]
                             (into {}
                                   (comp NULLs->nils
                                         convert-values)
                                   ms)))
                         rows)))]
    maps))

(comment
  (def table-identifier "[SCHEMA].[TABLE]")
  (def table-name "TABLE")
  (def csv-path "data.csv")

  ;; safe to play around with, it just reads the database and prints stuff
  (doseq [row (take 5 (csv->maps csv-path table-name))]
    (println row))
  
  ;; inserts stuff
  (jdbc/with-transaction [tx datasource]
    (doseq [row (csv->maps csv-path table-name)]
      (try
        (sql/insert! tx table-identifier row)
        (catch Throwable t
          (println "Failed on row: " row)
          (throw t)))))
  
  ;; be careful, this line is dangerous
  (sql/query datasource [(str "TRUNCATE TABLE " table-name ";")])
  
  )