Skip to content

Instantly share code, notes, and snippets.

@abelards
Created October 28, 2014 16:46
Show Gist options
  • Save abelards/fc005fc92ee9c1837629 to your computer and use it in GitHub Desktop.
Save abelards/fc005fc92ee9c1837629 to your computer and use it in GitHub Desktop.
XLS2PG transform
(defun xls2f (str)
"Transform a '42,42' string into a 42.42 float."
;; (atof (replace-regexp-in-string "," "." "42,42"))
(format nil "~{~a~^.~}" (sq:split-sequence #\, str))
)
LOAD CSV FROM FILENAME MATCHING ~/huge_file.csv/
WITH ENCODING iso-8859-1
INTO postgresql://login_x@localhost:5432/db
(
float num using (xls2f str)
)
WITH skip header = 1,
fields optionally enclosed by '\"',
fields escaped by double-quote,
fields terminated by ',';
@dimitri
Copy link

dimitri commented Oct 28, 2014

See dimitri/pgloader#92 for a full blown example using user defined transformation functions, and to debug use a very small file and both the following options: --debug --verbose, when both specified that's the same as --client-min-messages data which is the more advanced debug logs available.

Your transforms file should begin with the form:

(in-package :pgloader.transforms)

Also you can name the file with a .lisp extension ;-)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment