Subject: Re: Reading an excel spreadsheet using Lisp
From: (Rob Warnock)
Date: Mon, 15 Dec 2003 05:25:23 -0600
Newsgroups: comp.lang.lisp
Message-ID: <>
james anderson  <> wrote:
| export the spreadsheet as comma separated values, define a readtable...

Using readtables for that is overkill, and besides, it doesn't
handle quoted strings with the field delimiter in it, or escaped
quotes or escaped escapes [all of which I have seen in CSV files].
Here's a quick hack[1] I wrote once that handles all of those;

;;; PARSE-CSV-LINE -- Parse one CSV line into a list of fields,
;;; stripping quotes and field-internal escape characters.
(defun parse-csv-line (line)
  (when (string= line "")
    (return-from parse-csv-line '()))
  ;; assert: line contains at least one field
  (loop for c across line
        with state = :normal
        and results = '()
        and chars = '()
    do (ecase state
	  (case c
	    ((#\") (setq state :quoted))
	    ((#\\) (setq state :escaped))
	     (push (coerce (nreverse chars) 'string) results)
	     (setq chars '()))
	    (t (push c chars))))
	  (case c
	    ((#\") (setq state :normal))
	    ((#\\) (setq state :quoted+escaped))
	    (t (push c chars))))
	 ((:escaped) (push c chars) (setq state :normal))
	 ((:quoted+escaped) (push c chars) (setq state :quoted)))
       (push (coerce (nreverse chars) 'string) results) ; close open field
       (return (nreverse results)))))

;;; sample driver
(defun parse-csv-file (filename)
  (with-open-file (s filename)
	 (loop for line = (read-line s nil nil)
	       while line
	   collect (parse-csv-line line))))


[1] Magic constants for delimiter & escape characters are hard-coded,
    unlike Alain Picard's nice, generalized, parameterized version.

Rob Warnock			<>
627 26th Avenue			<URL:>
San Mateo, CA 94403		(650)572-2607