Change a block layer using excel vlookup

[no1_fred]

We have an excel table with two columns: Name & Value
We are looking for a way to have a block lookup the table, check for the row with its own name find the value.
Then using the value either change the blocks layer or change the objects hatch colour accordingly.
 
Eg
1=red, 2=green, 3=blue, 

Possible Steps.
1. On lisp run from cmd or button.
2. Search for all blocks in current drg.
3. Use block name. (Or other attribute value if eaisier)
4. Lookup excel file sheet 1.
5. Find block name in column A.
6. Return value from same row in column B.
7. Use value from column B to either move the block to a layer by the value or edit the colour of blocks hatch to the column B value.

I have looked at a lot of forums in search for this but can't see anything that would work..anyone any ideas?

[marko_ribar]

Not to complicate too much, I suggest that you save *.xls or *.xlsx file to *.csv (comma separated value file) and then use (read-line) function to read each row of sheet which is separated by "," ";" or "\t" string after which you can sort columns values and create corresponding list of strings of complete sheet excel table... Then do your task you described, it shouldn't be to hard for you (I suppose you are not total begginger in lisp)...

[BIGAL]

Here is an example of Marko_ribar idea agree csv makes life easy.

; thanks to Lee-mac for this defun
(defun _csv->lst ( str / pos )
(if (setq pos (vl-string-position 44 str))
(cons (substr str 1 pos) (_csv->lst (substr str (+ pos 2))))
(list str)
)
)


; hard coded for file at this stage
(setq fo (open "P:\\Autodesk\\lisp\\bradcodes.csv" "r"))
(setq lst '())
(while
(setq keyvals (read-line fo))
(setq lst (cons (_csv->lst keyvals) lst))
)
(close fo)

(princ lst)