mhmnibras Posted February 25, 2015 Share Posted February 25, 2015 hi all, please any one help me how to use this auto cad text to excel Lisp KAL to CSV.LSP Quote Link to comment Share on other sites More sharing options...
ReMark Posted February 25, 2015 Share Posted February 25, 2015 The program's description reads "A program to create a csv file from a text based table in AutoCAD." So the first question is have you created a text based table in AutoCAD? The second question is do you know how to load a lisp routine? And the third question is once the program is loaded do you know how to run it? Your turn. Quote Link to comment Share on other sites More sharing options...
mhmnibras Posted February 26, 2015 Author Share Posted February 26, 2015 yah i have created text based table in autocad and i know how to load lisp but it is not working please help me i have 740 Cross section every cross section have 15 item every item have LHS, RHS i don't know how to do...? Quote Link to comment Share on other sites More sharing options...
ReMark Posted February 26, 2015 Share Posted February 26, 2015 For someone to test the lisp program it would be helpful to have one of your drawings so they can troubleshoot the problem. Why? Because they will see exactly what you are seeing. Quote Link to comment Share on other sites More sharing options...
mhmnibras Posted February 26, 2015 Author Share Posted February 26, 2015 here i attached sample drawing Drawing2.dwg Quote Link to comment Share on other sites More sharing options...
ReMark Posted February 26, 2015 Share Posted February 26, 2015 Are you talking about the tables in the upper right hand corner of each sheet? Quote Link to comment Share on other sites More sharing options...
mhmnibras Posted February 26, 2015 Author Share Posted February 26, 2015 cage measurement Quote Link to comment Share on other sites More sharing options...
mhmnibras Posted February 26, 2015 Author Share Posted February 26, 2015 yes that is the text table which is drawn by line Quote Link to comment Share on other sites More sharing options...
mhmnibras Posted February 27, 2015 Author Share Posted February 27, 2015 hi all, please any one help me i have created text based table in autocad and and i downloaded this lisp from internet but it is not working i have 740 Cross section every cross section have 13 item every item have LHS, RHS i don't know how to do...? please any one tel me a easy way to export Quantity here i attached sample Drawing and lisp. ;; KAL to CSV; A program to create a csv file from a text based table in AutoCAD ;; Author: Doug Barnes ;; Company: Grantek Systems Integration (defun Cleanup_Text () ;; find all the text with no content and erase them (setq nil_text (ssget "x" '((-4 . "")) ) ) ;; search for text with no content (if nil_text (command "erase" nil_text "") ) ;; erase text with no content ) ;; end cleanup_text defun ;;========================================================================================== (defun Gather_Text () (setq Text_SS (ssget '((0 . "TEXT")))) ;; create a selection set for all text selected by the user. ) ;; end Gather_Text defun ;;========================================================================================== (defun MakeList_X_Y_Text (Text_SS) ;; Make a list of x-coords, y-coords and text (setq MakeList_cnt 0) (repeat (sslength Text_SS) (setq X_cord (if (= (cdr (assoc 72 (entget (ssname Text_SS MakeList_cnt)))) 0 ) ;; check for the text's justification (cadr (assoc 10 (entget (ssname Text_SS MakeList_cnt)))) ;; if left justified use first alignment point (cadr (assoc 11 (entget (ssname Text_SS MakeList_cnt)))) ;; else use second alignment point ) ;;end if ) ;;end Setq (setq y_cord (if (= (cdr (assoc 72 (entget (ssname Text_SS MakeList_cnt)))) 0 ) ;; check for the text's justification (caddr (assoc 10 (entget (ssname Text_SS MakeList_cnt)))) ;; if left justified use first alignment point (caddr (assoc 11 (entget (ssname Text_SS MakeList_cnt)))) ;; else use second alignment point ) ;;end if ) (setq List_X_Y_Text (append List_X_Y_Text (list (list (cons "X coord" X_cord ) (cons "Y coord" y_cord) (cons "Text" (cdr (assoc 1 (entget (ssname Text_SS MakeList_cnt)))) ) ) ) ) ) ;;make assocation list of x, y and text dotted pairs (setq MakeList_cnt (1+ MakeList_cnt)) ;; index to next entity ) ;;repeat ) ;;end MakeList_X_Y_Text defun ;;========================================================================================== (defun Sort_by_X (List_X_Y_Text) ;; re-order list from left to right (setq X_List (vl-sort List_X_Y_Text (FUNCTION (LAMBDA (E1 E2) ( ) ) ) ;; end Sort_by_X defun ;;========================================================================================== (defun Sort_by_Y (List_X_Y_Text_Col) ;; re-order list from top to bottom (setq Y_List (vl-sort List_X_Y_Text_Col (FUNCTION (LAMBDA (E1 E2) (> (cdadr E1) (cdadr E2)))) ) ) ) ;; end Sort_by_Y defun ;;========================================================================================== (defun Get_Columns (X_List) ;; determine the number of columns text (setq Column_Qty 1 X_List_cnt 0 ) (repeat (length X_List) ;; add a column number to List_X_Y_Text (setq List_X_Y_Text_Col (append List_X_Y_Text_Col (list (list (assoc "X coord" (nth X_List_cnt X_List)) (assoc "Y coord" (nth X_List_cnt X_List)) (assoc "Text" (nth X_List_cnt X_List)) (cons "Column" Column_Qty) ) ) ) ) (if (/= (nth (+ X_List_cnt 1) X_List) nil) ;; Check for end of list (progn (if (>= (- (cdr (car (nth (+ X_List_cnt 1) X_List))) Line_Tolerance) (cdr (car (nth X_List_cnt X_List))) ) ;; if the X Coord of the next text is out of tolerance with the current text then increase the column count (setq Column_Qty (1+ Column_Qty)) ) ) ) (setq X_List_cnt (1+ X_List_cnt)) ;; index list position ) ;; end repeat ) ;; end Get_Columns defun ;;========================================================================================== (defun Get_Rows (Y_List) ;; determine the number of rows of text (setq Row_Qty 1 Y_List_cnt 0 ) (repeat (length Y_List) ;; add a row number to List_X_Y_Text_Col (setq List_All (append List_All (list (list ;; make a list of x, y, text, column and row (assoc "X coord" (nth Y_List_cnt Y_List)) (assoc "Y coord" (nth Y_List_cnt Y_List)) (assoc "Text" (nth Y_List_cnt Y_List)) (assoc "Column" (nth Y_List_cnt Y_List)) (cons "Row" Row_Qty) ) ) ) ) (if (/= (nth (+ Y_List_cnt 1) Y_List) nil) ;; Check for end of list (progn (if ( (- (cdr (cadr (nth Y_List_cnt Y_List))) Line_Tolerance) ) ;; if the Y Coord of the next text is out of tolerance with the current text then increase the row count (setq Row_Qty (1+ Row_Qty)) ) ;; end if ) ;;end progn ) ;; end if (setq Y_List_cnt (1+ Y_List_cnt)) ;; index list position ) ;; end repeat ) ;; end Get_Rows defun ;;========================================================================================== (defun Write_KAL_to_CSV () (setq Position 0) (setq Path (getvar "dwgprefix")) ;; Obtain the file path of the drawing (setq Dwg_Name (substr (getvar "dwgname") 1 (- (strlen (getvar "dwgname")) 4) ) ) ;; remove the .dwg from the file name (setq KAL_CSV_File (open (strcat Path Dwg_Name " KAL.csv") "a")) ;; Open a CSV file for ammending (while (/= (nth Position List_All) nil) (setq Current_Row (cddr (nth Position List_All))) ;; setup to gather all of the same row text (setq Next_Row (cddr (nth (+ Position 1) List_All))) ;; setup to check end of row (while (= (cdr (assoc "Row" Current_Row)) (cdr (assoc "Row" Next_Row)) ) ;; loop for all the same row (setq Text_Column (append Text_Column (list (list (cdr (assoc "Text" Current_Row)) (cdr (assoc "Column" Current_Row)) ) ) ) ) ;; create a list of text and column only (setq Position (1+ Position)) ;; index position in list (setq Current_Row (cddr (nth Position List_All))) (setq Next_Row (cddr (nth (+ Position 1) List_All))) ) ;; end if (setq Text_Column (append Text_Column (list (list (cdr (assoc "Text" Current_Row)) (cdr (assoc "Column" Current_Row)) ) ) ) ) ;; append Text_Column with the last text in the current row (setq Text_Column (vl-sort Text_Column (FUNCTION (LAMBDA (E1 E2) ( ) ) ;; Sort list by Column number (left to right) (setq Column_Qty_Count 1 Write_Row "" Column_List_Position 0 ) (repeat Column_Qty ;; repeat for the maximum number of columns (if (= (cadr (nth Column_List_Position Text_Column)) Column_Qty_Count ) ;; check the position of the current text's postion with the column count (progn ;; if equal string together the cuurent text and a comma (setq Write_Row (strcat Write_Row (car (nth Column_List_Position Text_Column)) "," ) ) (setq Column_List_Position (1+ Column_List_Position)) ;; index position ) ;; end progn ;; if not equal string together previous text and a comma (setq Write_Row (strcat Write_Row ",")) ) ;; end if (setq Column_Qty_Count (1+ Column_Qty_Count)) ;; index the column count ) ;; end repeat ;;(print Write_Row) (write-line Write_Row KAL_CSV_File) ;; write the current row to the csv file (setq Position (1+ Position)) ;; index list position (setq Text_Column nil) ;; reset for next row ) ;; end while (close KAL_CSV_File) ;; close the csv file ) ;; end Write_KAL_to_CSV defun ;;========================================================================================== ;; Create a CSV file from a text based Bill of Materials in AutoCAD (defun c:KAL (/ X_List Y_List Text_Column List_X_Y_Text List_X_Y_Text_Col List_All ) (Cleanup_Text) (Gather_Text) (MakeList_X_Y_Text Text_SS) (Sort_by_X List_X_Y_Text) (setq Text_Height (cdr (assoc 40 (entget (ssname Text_SS 0))))) ;; get the text height used (setq Line_Tolerance (+ Text_Height (* Text_Height 0.1))) ;; set up a line spacing tolerance (Get_Columns X_List) (Sort_by_Y List_X_Y_Text_Col) (Get_Rows Y_List) (Write_KAL_to_CSV) ) ;; end defun Drawing2.dwg KAL to CSV.LSP Quote Link to comment Share on other sites More sharing options...
ReMark Posted February 27, 2015 Share Posted February 27, 2015 Please do not double post as it only leads to confusion. I believe you already have a thread about this very same problem. Quote Link to comment Share on other sites More sharing options...
mhmnibras Posted February 27, 2015 Author Share Posted February 27, 2015 yah of course but i didn't get answer yet... Quote Link to comment Share on other sites More sharing options...
ReMark Posted February 27, 2015 Share Posted February 27, 2015 yah of course but i didn't get answer yet... You just need to show some patience. We're all volunteers here not paid staff and sometimes requests for this type of help (lisp) come fast and furious or the members that really know this stuff are busy doing one thing or another. As to your problem didn't you receive three suggestions for solving it over at the Czech run AutoCAD help website CADforum? Have you tried another search for a custom lisp routine that will take AutioCAD text and create a .csv file? Quote Link to comment Share on other sites More sharing options...
mhmnibras Posted February 27, 2015 Author Share Posted February 27, 2015 yah i tried here i attache some lisp routine anothe one is VLX it is not allowed to attached CAD to Excel.LSP Quote Link to comment Share on other sites More sharing options...
ReMark Posted February 27, 2015 Share Posted February 27, 2015 I see you were given not three but four different suggestions at the CADforum. How many of them did you try? Quote Link to comment Share on other sites More sharing options...
SLW210 Posted February 27, 2015 Share Posted February 27, 2015 I merged your threads, please do not post the same inquiry more than once. Please read the Code Posting Guidelines and edit your post to include the Code in Code Tags. Quote Link to comment Share on other sites More sharing options...
hmsilva Posted February 27, 2015 Share Posted February 27, 2015 Hi mhmnibras, the code works as expected, just export 'TEXT' entities and tries to keep the formatting of a virtual table. In your dwg, you have in the 'tables' 'TEXT' and 'MTEXT' entities, exploding 'MTEXT', selcting one 'table at a time, the code will crate/append a 'dwgname KAL.csv' file with the 'tables'. Open excel, data tab, Get External Data, From Text, select the .csv file, Next, Delimiters, select coma, Next, Finish, excel will ask 'were do you want to put the data?', select a cell, or a new worksheet, and press OK... Henrique Quote Link to comment Share on other sites More sharing options...
barnesd Posted March 2, 2015 Share Posted March 2, 2015 (edited) Are you still having trouble running this program? I noticed that the code that is displayed here is a little different from my original program. I ran the version that I wrote and produced the .xls file that is here. Was this the result you were looking for? Drawing2 BOM.xls Edited March 2, 2015 by barnesd Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.