xlsx

;; example: creating a sqlite database from an excel sheet
;; (the contents of the first row are used as column names)

(asdf:load-system :sqlite)

(asdf:load-system :xlsx)

(defun fix-name (str)
  (loop for char in '(#\- #\/ #\Space)
     do (setf str (substitute #\_ char str)))
  (setf str (remove #\.	str))
  (if (digit-char-p (elt str 0))
      (concatenate 'string "_" str)
      str))

(defun xlsx-to-sqlite (file &optional (na "_NA_"))
  (let ((xlsx (probe-file file)))
    (when (null xlsx) (error "~A not found" file))
    (let ((sqlite (merge-pathnames (make-pathname :type "sqlite") xlsx)))
      (when (probe-file sqlite) (error "~A exists already" sqlite))
      (let* ((data (xlsx:as-matrix (xlsx:read-sheet file 1) na))
	     (fields (loop for col from 0 below (array-dimension data 1)
			collect (fix-name (aref data 0 col))))
             (numeric (loop for col from 0 below (array-dimension data 1)
			 collect (every (lambda (x) (or (numberp x) (null x)))
					(loop for row from 1 below (array-dimension data 0)
					   collect (aref data row col)))))
	     (create-sql (format nil "CREATE TABLE data(~{~A~^,~})"
	     		 	 (loop for f in fields for n in numeric
				    collect (if n (format nil "~A NUMBER" f) f))))
	     (insert-sql (format nil "INSERT INTO data VALUES (~{~A~^,~})"
				 (loop repeat (length fields) collect "?"))))
	(sqlite:with-open-database (db sqlite)
	  (sqlite:execute-to-list db create-sql)
	  (loop for row from 1 below (array-dimension data 0)
	     do (apply #'sqlite:execute-non-query db insert-sql
		       (loop for col from 0 below (length fields)
			  for item = (aref data row col)
			  collect (if (equal item na) nil item)))))))))


xlsx

Upstream URL

Author

License

xlsx

License:

Authors:

Dependencies:

Dependencies (3)

Dependents (0)