AutoLISP for excel to separate lists

[JGranata_GdB]

Hello all. 

I am looking for a LISP or help in creating one that will look to a specific excel sheet (outside of the support file search paths; i.e. D:\Custom\AutoDesk\ExcetoListAutoCAD.xlsx) that has multiple columns and converts each column into a list. This code will currently be placed in an existing LISP that will replace the current setq lists, as the list is always changing and I would like to make it accessible for those with limited to no Auto Lisp knowledge. 

I would like the code to read the excel and skip the first 2 rows of the Excel, create a lists with the list name as whats in the 3rd row, and start the list with the 4 row in the column. There is no predefined number of lists (columns) or predetermined length of each list (number of rows). 

I have attached an example excel file. For simplicity, this has the same format that the excel would have, but only 3 columns and 4 items in each list. 

Thank you for the help and Guidance, 

Jon

[hencoop · Accepted solution]

Hi,

I found it much easier to code for reading the file if I saved it as a CSV (Comma delimited) (*.csv).  Excel will still display it the same as an .xlsx file except that line-feeds will put the next line on the next row.  I've adjusted your .xlsx contents to account for this in the attached .csv file.  The row numbers in your instructions will need to increase by 3 each to match the attached .csv file.

Also, you cannot use commas anywhere in your cells or they will be read as a new column break causing havoc with your data.

Autolisp easily opens the file with (OPEN <file name> "r") and reads each line with (READ-LINE ...).  You can hard-code the path to your location or put it where 'this-file is in (GETFILED...) to take your users there.

The line (row) with your your symbol names (variable names) can be stored as it is read and each subsequent (READ-LINE...) can be appended to your data-list.

DOSLIB has a nice function for making a list of the delimited data:

(DOS_STRTOKENS <the read line> ",") returns a list of values that were separated by the delimiter.

Here is how I would do it with a .csv file and DOSLIB (you will need to get DOSLIB and load the version for your AutoCAD version if you haven't done that already):

(defun make-item-list (/ open-csv cnt sym-list next-line)
  (SETQ data-list NIL)
  (setq this-file (getfiled "Open CSV data file" (if this-file this-file "") "csv" 0))
  (if this-file
    (progn
      (setq open-csv (open this-file "r"))
      (if open-csv
        (progn
	  (setq cnt 0)
          (while
            (setq next-line (read-line open-csv))
            (setq cnt (1+ cnt))
            (if (= cnt 6))
            (setq sym-list (dos_strtokens next-line ","))
          )
          (if (and (> cnt 6)(=(type next-line)'STR)(wcmatch next-line "*`,*`,*"))
            (setq data-list (append data-list (list (dos_strtokens next-line ","))))
          )
        )
        (close open-csv)
      )
      (princ
        (strcat
          "\nUnable to open \""
          this-file
          "\" for reading! "
        )
      )
      (if (and sym-list data-list)
        (progn
          (set (read (car sym-list)) (mapcar 'car data-list))
          (set (read (cadr sym-list)) (mapcar 'cadr data-list))
          (set (read (caddr sym-list)) (mapcar 'caddr data-list))
          (princ "\n")
          (princ (read (car sym-list)))
          (princ " = ")
          (princ (eval (read (car sym-list))))
          (princ "\n")
          (princ (read (cadr sym-list)))
          (princ " = ")
          (princ (eval (read (cadr sym-list))))
          (princ "\n")
          (princ (read (caddr sym-list)))
          (princ " = ")
          (princ (eval (read (caddr sym-list))))
          (princ)
        )
      )
    )
    (princ "\nNo CSV file was selected! ")
  )
  (princ)
)

I princ'd all of the information so it can be easily seen.  You can comment them out to keep that from happening.

I've included the CSV version of your .xlsx file.

[JGranata_GdB]

Hencoop, 

Thank you for the suggestion to switch to a csv instead of using an excel file and the knowledge of how to read the csv. I was hoping to do this without installing any other programs onto the computer.

I do not have any time to work on this today or tomorrow, but I will take a look soon to see if it is possible to write something only using AutoLISP.  

[hencoop]

If you are concerned about DOSLIB, it is essentially an autolisp library that is provided free by Robert McNeel & Associates.  It works inside AutoCAD and Autolisp.  What I've done with it can be done using only Autolisp but not near a easily as it is using DOSLIB.  DOSLIB has a great many tools that will help you in your Autolisp programming.  I highly recommend that you get it and start making use of it in your programs.  If you would still like to do what I've done without it I can show you how if you would like.

If it is the CSV file that bothers you, that is a DOS text file with the extension .csv which EXCEL renders as a spreadsheet.  It does not contain formatting for column widths, row heights or such but it can contain anything that can be presented as text (including formulas). It is necessary to save it as a DOS CSV file from EXCEL or it will no longer be a text file that Autolisp can read and becomes a .xlsx file.

[JGranata_GdB]

That would be fantastic, I would love to see how it can be done with just AutoLISP. 

I have no objection to using a CSV. Using a CSV makes sense, as it can still be opened in Excel for easy editing and saved back to the csv. I initially was working with an excel file as it is the easiest for other coworkers, management or new hires to edit.

I do not doubt that DOSLIP saved alot of coding and offers more tools and options. I will take a look into DOSLIP and see the functionality it offers for future work. However currently this is the only work that my company right now requires reading an external source, or interacting outside of the AutoCAD environment.  

[hencoop]

Okay,

Here it is without DOSLIB.  I've commented out the two individual lines of code that used the DOSLIB function (DOS_STRTOKENS...) .

It was necessary to create about 14 new lines of code for each instance in order to do the same thing without DOSLIB (I mention this just to help emphasize the point that DOSLIB has some very useful tools).

Also, I added the ability to use Tab delimited files as well (about another 14 lines of code each).  Tab delimited files are handled the same as CSV files by EXCE; however, you can use commas in them (you cannot use tabs for anything but a delimiter or they will cause havoc with your data).  Vice-versa for the CSV file.

I've attached both the CSV and the Tab delimited files and the revised make-item-list.lsp file.

[hencoop]

I found a couple of problems with the code and fixed them.

I also modified it:

  (1) It will now do as many columns as you want (as long as your system's memory can handle it).

  (2) If you put the text EndOfList (not case sensitive) on a row the list building will stop there.

I tested it with the CSV and Tab delimited files after adding three more columns.  All are attached

[JGranata_GdB]

I managed to get a license to do some testing. I noticed some weird occurrences. I have attached the files I am working with. 

- I made it a command just to test it. 

- I tabbed/added a few comment lines for the ) to make the code easier for me to read.

- I commented the get file out and replaced with a findfile, as I want this to run and not have user input. 

- I changed the order of where the "Unable to open..." occurred as it was displaying whenever the code ran. 

I then ran into a few errors/issues. Most of these issues I could resolve in a workaround dealing with the csv, but in a perfect world the lisp will take care of these situations: 

1) Dealing with the ENDOFLIST. When replacing 

(setq next-line (read-line open-ssht))

with 

(and
	(setq next-line (read-line open-ssht))
	(not (wcmatch (strcase next-line) "*ENDOFLIST*"))
);;End And

It truncates the list by 1. If there are 5 items in the list and then the ENDOFLIST, the resulting list will be 4 items long. {Obvious workaround is to duplicate the final line in the csv before the ENDOFLIST} When i remove the *'s from the code, it treats the ENDOFLIST as another item within the list. 

2) When implementing the Loop:

(if (and sym-list data-list)
	(progn
		(setq col-cnt 0)
		(while (< col-cnt (length sym-list))
			(set (read (nth col-cnt sym-list)) (mapcar '(lambda (x) (nth col-cnt x)) data-list))
			(princ "\n")
			(princ (read (nth col-cnt sym-list)))
			(princ " = ")
			(princ (eval (read (nth col-cnt sym-list))))
			(setq col-cnt (1+ col-cnt))
		);;End While
		(princ)
	);;End Progn
);;End If

If the *ENDOFLIST* is in the code, i only receive a list of the minimum items for ALL lists.  If i remove the *, then it acts as if that was not the case, and the resulting lists have the ENDOFLIST as an item. {Workaround is to use the previous method of (set (read (car sym-list)) (mapcar 'car data-list)) and just add a new line for each new column ie. for a 4th column adding (set (read (cadddr sym-list)) (mapcar 'cadddr data-list))}

3) In odd cases, the lists will be different lengths, as seen in the attached .csv. Sometimes newly added columns will be less or more than the previous columns.  In these cases currently the extra ,,,, to get to the newest longer column are handled oddly by the code. {workaround is by opening the csv into excel and moving/ordering the columns so the longer line is always on the left and the shorter list is on the right}

Could these issues be solved by using the DOSLIB and that coding in LISP without it is causing these issues? 

[hencoop · Accepted solution]

I fixed the message issue you mentioned and replaced the code as I suggested in the lisp file attached to my previous post.

In your CSV file line 10 would end all list making.  The first occurrence of ENDOFLIST on a line in any position will stop list making as the code is now.

Since each list is created down the column for each symbol name (from line 6) it is not necessary to specify any ENDOFLIST as you have used them.

A (WHILE ...) loop that checks the (LAST ...) of each list and continues to strip it off until it is not blank will serve the purpose.

I fixed that and a couple of other little things... like resetting char-cnt to 1 when I should have reset it to 0 in the while loops.  That did not show up as a problem until there were empty cells.  It would skip over the next delimiter because of this error. The attached lisp file has the corrections and updates.

Also, I think you intended to have a symbol name ALPHA (on line 6) but it is spelled APLHA.  If I'm right that might cause problems in your other code.

P.S. You will need to fix the file selection method again, sorry.