Topic: reading csv files 1 cell at a time

[andrew_nao]

anyone have any code they can share on reading cells in a csv file?
I have code that will read the whole row but im looking for a way to read each cell individually and set a variable to it
something along the lines of

row 1 cell a is hello
row 1 cell b is world

(setq 1a = "row1" "cell a")
(setq 1b = "row1" "cell b")

(princ 1a)
(princ 1b)

hope this is understandable

[CAB]

If you are reading row at a time the data should look like this:
(setq rowData '("Cell 1" "Cell 2" "Cell 3" .....))

to get the cell info use this:
(setq cellData (nth 1 rowData))
and you will get this: "Cell 2"

(setq cellData (nth 0 rowData))
and you will get this: "Cell 1"

[gile]

Hi,

Here's a way, the read-csv routine returns an association list like: (("A1" . value_1) ("A2" . value_2) ...)

Using example:
(setq cells (read-csv (getfiled "Choose a file" "" "csv" 0)))
to get the C2 cell value:
(cdr (assoc "C2" cells))

Code: [Select]

;; Num2Alpha
;; Converts Number into Alpha string
;; Arguments:
;;   Num = Number to convert
;; Syntax example: (Num2Alpha 731) = "ABC"

(defun num2alpha (num / r)
  (if (< num 27)
    (chr (+ 64 num))
    (if (= 0 (setq r (rem num 26)))
      (strcat (num2alpha (1- (/ num 26))) "Z")
      (strcat (num2alpha (/ num 26)) (chr (+ 64 r)))
    )
  )
)

;; STR2LST
;; Converts a string with separator into a list of strings
;;
;; Arguments
;;   str = the string
;;   sep = the separator pattern
;; Syntax example: (str2lst "A,B,C" ",") = ("A" "B" "C")

(defun str2lst (str sep / pos)
  (if (setq pos (vl-string-search sep str))
    (cons (substr str 1 pos)
  (str2lst (substr str (+ (strlen sep) pos 1)) sep)
    )
    (list str)
  )
)


;; READ-CSV
;; Reads a csv file and returns an association list (("A1" . value_1) ("A2" . value_2) ...)
;;
;; Argument
;;   path = the file complete path
;; Syntax example: (read-csv "C:\\Temp\\test.csv")

(defun read-csv (path / file cell row col lst)
  (and
    (setq file (open path "r"))
    (setq row 1)
    (setq col 0)
    (while (setq line (read-line file))
      (setq lst (cons
  (mapcar
    (function
      (lambda (x)
(cons
  (strcat (num2alpha (setq col (1+ col))) (itoa row))
  x
)
      )
    )
    (str2lst line ",")
  )
  lst
)
    row (1+ row)
    col 0
      )
    )
    (close file)
  )
  (apply 'append (reverse lst))
)

[Keith™]

This has little error checking, but returns a specific row,cell from a specified file.

usage is
(getCell 3 10 "C:\\MyDocs\\ThisFile.csv")

returns cell 3 10 i.e. C10 out of the specified file

If the cell does not exist it crashes

Code: [Select]

(defun getCell (rowX columnY file / begin count fn index row rval)
  (setq fn    (open file "r")
index 0
  )
  (while (< index rowX)
    (setq row (read-line fn)
  index (1+ index)
    )
  )
  (close fn)
  (setq index 1
begin 1
count 1
  )
  (while (< index (+ (strlen row) 1))
    (if (= (substr row index 1) ",")
      (if (= count columnY)
(if (= begin index)
  (setq rval "")
  (setq rval (substr row begin (- index begin))
  index (+ (strlen row) 1))
)
(setq count (1+ count)
      begin (1+ index)
      index (1+ index))
      )
      (setq index (1+ index))
    )
  )
  (if (= rval nil)
    (setq rval (substr row begin (- (strlen row) begin)))
  )
  rval
)


[gile]

Another one, same usage as Keith's.
It returns nil for over range row or column.

Code: [Select]

;; STR2LST
;; Transforms a string with separator into a list of strings
;;
;; Arguments
;;   str = the string
;;   sep = the separator pattern
;; Syntax example: (str2lst "A,B,C" ",") = ("A" "B" "C")

(defun str2lst (str sep / pos)
  (if (setq pos (vl-string-search sep str))
    (cons (substr str 1 pos)
  (str2lst (substr str (+ (strlen sep) pos 1)) sep)
    )
    (list str)
  )
)

;; GET-CELL
;; Returns the cell value of a csv file
;;
;; Arguments
;;   path: the complete file path
;;   row: the row number
;;   col: the column number
;; Syntax example: (get-cel "C:\\Temp\\test.csv" 2 3) = "C2" cell value

(defun get-cell (path row col / file line lst sub)
  (and
    (setq file (open path "r"))
    (while (setq line (read-line file))
      (setq lst (cons (str2lst line ",") lst))
    )
    (close file)
  )
  (if (and lst (setq sub (nth (1- row) (reverse lst))))
    (nth (1- col) sub)
  )
)

[andrew_nao]

If you are reading row at a time the data should look like this:
(setq rowData '("Cell 1" "Cell 2" "Cell 3" .....))

to get the cell info use this:
(setq cellData (nth 1 rowData))
and you will get this: "Cell 2"

(setq cellData (nth 0 rowData))
and you will get this: "Cell 1"

this is what im looking for however im doing something wrong
could you please give me some feedback on what im doing wrong?
this is what i have so far

Code: [Select]

(defun filetolist(f / *list* ltext f*1)
(setq *list* '())
(setq f*1 (open f "r"))
(while (setq ltext (read-line f*1))
(setq *list* (cons ltext *list*))
)
(close f*1)
(setq *list* (reverse *list*))
)


(setq newlist (filetolist "c:\\TEST.csv"))

(setq line1 (cons 1 (nth 0 newlist)))

(setq ROWDATA '(LINE1))
(setq CELLDATA (nth 0 ROWDATA))

(PRINC CELLDATA)
(PRINC)


[CAB]

Test this:

Code: [Select]

(defun filetolist (f / *list* ltext f*1)
  (setq f*1 (open f "r"))
  (while (setq ltext (read-line f*1))
    (setq *list* (cons ltext *list*))
  )
  (close f*1)
  (reverse *list*) ; return the list
)


(setq newlist (filetolist "c:\\TEST.csv"))
;;  note that nth is base zero, first item is 0 and not 1
(setq RowNum 3) ; get row 3 as an example, need error check incase there is no row 3
(setq lineData (nth RowNum newlist))

(setq ColNum 2) ; example, need error check incase there is no column data
(setq CELLDATA (nth ColNum lineData))

(PRINC CELLDATA)
(PRINC)
PS This code need refinement & error checking & more help.
So post your next version so these items can be addressed.

[andrew_nao]

i appreciate your time and help
I dont know how to do error checking so that might be a problem
because when i load the code you posted it i get
Loading...; error: bad argument type: consp "hello,world,hello,world"

[CAB]

Give this a test drive.

Code: [Select]

(defun c:test (/ FullFileName RowNum ColNum FileName NewList LineData
               CellData filetolist sparser)

  (defun filetolist (fln / lst ltext fn)
    (if (setq fn (open fln "r"))
      (progn
        (while (setq ltext (read-line fn))
          (setq lst (cons ltext lst))
        )
        (close fn)
      )
    )
    (reverse lst)                       ; return the list
  )
 
  ;;  parser by CAB single character delim, match ","
  (defun sparser (str delim / ptr lst)
    (while (setq ptr (vl-string-search delim str))
      (setq lst (cons (substr str 1 ptr) lst))
      (setq str (substr str (+ ptr 2)))
    )
    (reverse (cons str lst))
  )

  ;;  note that nth is base zero, first item is 0 and not 1
  ;; So let user use base 1 & we will subtract 1
  (initget 7)
  (setq RowNum (getint "\nEnter the Row Number. "))
  (initget 7)
  (setq ColNum (getint "\nEnter the Cell Number. "))
  (setq FileName "c:\\TEST.csv")

  ;;  Open & Read File, attempt to get cell data
  (cond
    ((null (setq FullFileName (findfile FileName)))
     (princ "\nFile not found.")
    )
    ((null (setq newlist (filetolist FullFileName)))
     (PRINC "\nNo data found in file.")
    )
    ((null (setq lineData (nth (1- RowNum) newlist)))
     (PRINC "\nNo row data found in file.")
    )
    ;;  seperate row data into cells & get the cell
    ((null (setq CELLDATA (nth (1- ColNum) (sparser LineData ","))))
     (PRINC "\nNo column data found in row.")
    )
    (t ; if we are here all worked OK
     (princ CellData))
  )

 
  (PRINC)
)

[andrew_nao]

thanks
but its not working

Loading...; error: bad argument type: numberp: nil

[CAB]

Download the attached lisp, run it.
If an error occurs copy the output from the command line & post it here.

[andrew_nao]

that worked, what was done differently?

ill have to go back and how you did that.

your help is much appreciated

[CAB]

All I added was an error subroutine.
I suspect when you copied the lisp some of it was not copied.
It happens a lot, so by attaching the lisp file I eliminated the chance for that error.

[dubb]

Excuse my lack of knowledge on how this script works. I would like to use this script to also parse an entire column or row instead of a single cell.

Give this a test drive.

Code: [Select]

(defun c:test (/ FullFileName RowNum ColNum FileName NewList LineData
               CellData filetolist sparser)

  (defun filetolist (fln / lst ltext fn)
    (if (setq fn (open fln "r"))
      (progn
        (while (setq ltext (read-line fn))
          (setq lst (cons ltext lst))
        )
        (close fn)
      )
    )
    (reverse lst)                       ; return the list
  )
 
  ;;  parser by CAB single character delim, match ","
  (defun sparser (str delim / ptr lst)
    (while (setq ptr (vl-string-search delim str))
      (setq lst (cons (substr str 1 ptr) lst))
      (setq str (substr str (+ ptr 2)))
    )
    (reverse (cons str lst))
  )

  ;;  note that nth is base zero, first item is 0 and not 1
  ;; So let user use base 1 & we will subtract 1
  (initget 7)
  (setq RowNum (getint "\nEnter the Row Number. "))
  (initget 7)
  (setq ColNum (getint "\nEnter the Cell Number. "))
  (setq FileName "c:\\TEST.csv")

  ;;  Open & Read File, attempt to get cell data
  (cond
    ((null (setq FullFileName (findfile FileName)))
     (princ "\nFile not found.")
    )
    ((null (setq newlist (filetolist FullFileName)))
     (PRINC "\nNo data found in file.")
    )
    ((null (setq lineData (nth (1- RowNum) newlist)))
     (PRINC "\nNo row data found in file.")
    )
    ;;  seperate row data into cells & get the cell
    ((null (setq CELLDATA (nth (1- ColNum) (sparser LineData ","))))
     (PRINC "\nNo column data found in row.")
    )
    (t ; if we are here all worked OK
     (princ CellData))
  )

 
  (PRINC)
)