TheSwamp
Code Red => AutoLISP (Vanilla / Visual) => Topic started by: andrew_nao on March 26, 2008, 02:34:50 PM
-
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
-
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"
-
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))
;; 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))
)
-
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
(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
)
-
Another one, same usage as Keith's.
It returns nil for over range row or column.
;; 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)
)
)
-
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
(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)
-
Test this:
(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.
-
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"
-
Give this a test drive.
(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)
)
-
thanks
but its not working
Loading...; error: bad argument type: numberp: nil
-
Download the attached lisp, run it.
If an error occurs copy the output from the command line & post it here.
-
that worked, what was done differently?
ill have to go back and how you did that.
your help is much appreciated
-
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.
-
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. :smitten:
Give this a test drive.
(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)
)