Author Topic: Read csv file and append text to drawing  (Read 3435 times)

0 Members and 1 Guest are viewing this topic.

jaydee

  • Guest
Read csv file and append text to drawing
« on: November 10, 2011, 06:39:21 AM »
Hi.
It would be a bit easier if my question is doable, some advice please.
Im working on a confidential job.
Plans with lots of rooms but only showing room numbers. ie text  "1.01"
Given room data sheet, i could create a new CSV files consisted of 2 columns

room number      room name
1.01                    store room
1.02                    meeting room
etc                      etc.                     

Question is how to read the csv file and append the room name to drawing if the room number is found, so the drawing room number become "1.01 store room"
Im only doing this for myself temporarily to allow having the info upfront.

Thankyou


CAB

  • Global Moderator
  • Seagull
  • Posts: 10401
Re: Read csv file and append text to drawing
« Reply #1 on: November 10, 2011, 08:22:22 AM »
Yes it can be done.
In fact there are parts of routines found here at the Swamp that will do it for your.
I've reached the age where the happy hour is a nap. (°¿°)
Windows 10 core i7 4790k 4Ghz 32GB GTX 970
Please support this web site.

Lee Mac

  • Seagull
  • Posts: 12905
  • London, England
Re: Read csv file and append text to drawing
« Reply #2 on: November 10, 2011, 08:27:38 AM »
Here is a quick sub to read a CSV file and return a list of lists of the cell contents:

Code: [Select]
(defun _ReadCSV ( filename / _csv->lst file line lst )

    (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)
        )
    )

    (if (setq file (open filename "r"))
        (progn
            (while (setq line (read-line file))
                (setq lst (cons (_csv->lst line) lst))
            )
            (setq file (close file))
        )
    )
    (reverse lst)
)

That should get you started.  8-)

myloveflyer

  • Newt
  • Posts: 152
Re: Read csv file and append text to drawing
« Reply #3 on: November 10, 2011, 08:44:03 PM »
I hope that this CODE can help you solve the problem
Code: [Select]
;;;Remove the Excel file in the first worksheet cell specifies data
(defun c:ttt ()
  (Excel-Get-data)
)

(defun Excel-Get-data ( / xfile cell ADOCONNECT ADORECORDSET ConnectionString lst Sheet-name source cell-tmp cell-value)
;;;Specify the cell data
  (defun Excel-Get-CellValue (Sheet-name cell)
    (setq source (strcat "SELECT * FROM [" Sheet-name cell":" cell "]"))
    (vlax-invoke-method  ADORecordset "Open" source ADOConnect 1 3 nil)
    (setq cell-tmp (vlax-safearray->list (vlax-variant-value (vlax-invoke-method ADORecordset "GetRows" 1))))
    (vlax-variant-value (car (car cell-tmp))) 
  )

  (setq xfile (getfiled "open file" "" "xls" 8))
  (setq ADOConnect (vlax-get-or-create-object "ADODB.Connection"))
  (setq ADORecordset (vlax-get-or-create-object "ADODB.Recordset"))
  (setq ConnectionString (strcat "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" xfile ";Extended Properties=;Excel 8.0;HDR=No" ))
  (if (not
 (vl-catch-all-error-p
   (vl-catch-all-apply
     (function vlax-invoke-method)
     (list ADOConnect "Open" ConnectionString  "admin" "" nil)
   )
 )
      )
    (progn
      (setq lst
      (vlax-safearray->list
        (vlax-variant-value
   (vlax-invoke-method (vlax-invoke-method ADOConnect "OpenSchema" 4 ) "GetRows" 1)
        )
     )
      )
      (setq Sheet-name (vlax-variant-value (car (caddr lst))));Determine the" first" working table name
      (setq cell-value (Excel-Get-CellValue Sheet-name "H16"));H16cell
      (vlax-invoke-method ADORecordset "Close")
      (vlax-invoke-method ADOConnect "Close")
    )
    (progn
      (princ "\\nOpen the Excel data file error")
      (vl-catch-all-apply \'vlax-invoke-method (list ADOConnect "Close"))
      (setq cell-value nil)
    )
  )
  (vlax-release-object ADORecordset)
  (vlax-release-object ADOConnect)
  cell-value
)

Never give up !

jaydee

  • Guest
Re: Read csv file and append text to drawing
« Reply #4 on: November 12, 2011, 08:47:53 AM »
Thankyou guys for your help.
Yes it can be done.
In fact there are parts of routines found here at the Swamp that will do it for your.
Firstly Cab is right if i do a search "CSV" in this forum prior to posting my question, I would get plenty of answers.

Hi Flyer. I need more time to figure how your routine works for me.

Here my general concept that uses LM _ReadCSV would return a list of dotted pairs
Code: [Select]
(setq lst '(("1.01" "STORE ROOM") ("1.02" "MEETING ROOM") ("G.100" "PLANTROOM")
("GF.02" "COMMS") ("1.45" "TRAINING") ("2.23" "CONFERENCE")
("3.21" "KITCHEN"))
)
Code: [Select]
(defun c:test (/ lst ss ssentity index ent oldtext newtext)

;(setq csvfile (getfiled "Select File Name" "" "csv" 1))
;(setq lst (_ReadCSV csvfile))

(setq ss (ssget (list '(0 . "TEXT"))))

(if (= ss nil)
 (setq ssentity 0)
 (setq ssentity (sslength ss))
)

(setq index 0)

(while (< index ssentity)
 (setq ent (vlax-ename->vla-object (ssname ss index)))
 (setq oldtext (vla-get-textstring ent))

  (if (setq newtext (car (vl-remove-if '(lambda (l) (not (wcmatch (car l) oldtext))) lst)))
   (progn
    (setq newtext (List->String newtext "  "))
    (vla-put-TextString ent newtext)
   )
  )
(setq index (1+ index))
)
(princ))

Here is a quick sub to read a CSV file and return a list of lists of the cell contents:

Code: [Select]
(defun _ReadCSV ( filename / _csv->lst file line lst )

    (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)
        )
    )

    (if (setq file (open filename "r"))
        (progn
            (while (setq line (read-line file))
                (setq lst (cons (_csv->lst line) lst))
            )
            (setq file (close file))
        )
    )
    (reverse lst)
)

That should get you started.  8-)

Hi Lee.
How to get the (_ReadCSV) to read user specify columns,
Ie. All columns or user enter ie. 1,3,5 for reading columns 1,3,5 etc.
The reason i ask if i could read of existing file rather then copy and paste the needed columns into a new csv file.
Thankyou
« Last Edit: November 12, 2011, 09:59:24 AM by jaydee »

hmspe

  • Bull Frog
  • Posts: 362
Re: Read csv file and append text to drawing
« Reply #5 on: November 12, 2011, 12:27:47 PM »
Here is a lisp routine I use to read in cvs data that for fire alarm calculations.  It draws a table [as line work, not as table objects] with the data.  Also attached is a typical data file.  The data should be saved as a .txt file to match the lisp routine.  This is written to work in model space and to scale the blocks that make up the table based on the value of DIMSCALE.

Code: [Select]
(defun c:nac (/ counter1 current drop ds file_name insert_point insert_x insert_y OK old_ortho
              old_osmode opened string_list text_line temp_point temp_y
             )

  (defun *error* (msg)
    (prompt (strcat "\n Error--> " msg))
    (setvar "orthomode" old_ortho)
    (setvar "osmode" old_osmode)
    (princ)
  )

  (defun parse_string
                      (string_in delimiter / position_1 position_2 list_out)
    (setq position_2 1)
    (while (setq position_1
                  (vl-string-search delimiter string_in position_1)
           )
      (if (= position_2 1)
        (setq
          list_out (cons (substr string_in position_2 position_1)
                         list_out
                   )
        )
        (setq list_out (cons (substr string_in
                                     position_2
                                     (- (1+ position_1) position_2)
                             )
                             list_out
                       )
        )
      )
      (setq position_2 (1+ (+ (strlen delimiter) position_1)))
      (setq position_1 (+ position_1 (strlen delimiter)))
    )
    (reverse
      (setq list_out (cons (substr string_in position_2) list_out))
    )
  )

  (defun pr_ss (a_point a_distance)                                             ; gets ss for box ((x-apnt),(y-apnt)) to ((x+apnt),(y+apnt))
    (if (vl-string-search "BRICSCAD" (strcase (getvar "acadver")))              ; Bricscad fails for small apertures
      (if (< a_distance 0.001) (setq a_distance 0.001))
    )
    (ssget "c"
           (list (- (car a_point) a_distance)
                 (- (cadr a_point) a_distance)
           )
           (list (+ (car a_point) a_distance)
                 (+ (cadr a_point) a_distance)
           )
    )
  )

  (defun process (pr_point pr_text / pr_offset pr_selset pr_counter pr_entity
                 )
    (setq pr_offset (* ds 0.06))                                                ; set the search area
    (setq pr_selset (pr_ss pr_point pr_offset))
                                                                                ; get selection set in search area
    (if pr_selset
      (progn
        (setq pr_counter (sslength pr_selset))
                                                                                ; get selection set length
        (while (> pr_counter 0)                                                 ; loop through items
          (setq pr_counter (1- pr_counter))                                     ; decrement counter
          (setq pr_entity (entget (ssname pr_selset pr_counter)))
                                                                                ; get the entity
          (if (= (cdr (assoc 0 pr_entity)) "TEXT")                              ;if it's text...
            (progn
              (setq pr_entity (subst (cons 1 pr_text)
                                     (assoc 1 pr_entity)
                                     pr_entity
                              )
              )
              (entmod pr_entity)                                                ; else update entity color and value
            )
          )
        )
      )
    )
  )

  (setvar "cmdecho" 0)
  (setq old_ortho (getvar "orthomode"))
  (setvar "orthomode" 0)
  (setq old_osmode (getvar "osmode"))
  (setvar "osmode" 0)
  (setq ds (getvar "dimscale"))
  (setq text_height (* ds 0.09375))
  (if (setq
        file_name (getfiled "Select NAC configuration file to read..."
                            (getvar "dwgprefix")
                            "txt"
                            128
                  )
      )
    (progn
      (setq insert_point (getpoint "\n Select point for data box: "))
      (setq insert_x (car insert_point))
      (setq insert_y (cadr insert_point))
      (setq opened (open file_name "r"))
      (setq OK 1)
      (setq current 0.0)
      (setq counter1 0)
      (while (and (setq text_line (read-line opened))
                  OK
             )
        (setq counter1 (1+ counter1))
        (if (= counter1 1)
          (progn
            (if (= (substr text_line 1 8) "Device #")
              (progn
                (command "insert" "E_NAC_block_1" "PS" ds insert_point ds ds 0.0
                        )
                (command "_explode" "last")
              )
              (progn
                (setq OK nil)
              )
            )
          )
          (progn
            (setq string_list (parse_string text_line ","))
            (setq temp_y (- insert_y
                            (+ (* 3.0 text_height)
                               (* 2.0 text_height counter1)
                            )
                         )
            )
            (command "insert"
                     "E_NAC_block_2"
                     "PS"
                     ds
                     (list insert_x temp_y)
                     ds
                     ds
                     0.0
            )
            (command "_explode" "last")
            (setq temp_y (+ temp_y (* ds -0.09375000)))
            (setq temp_point
                   (list (+ insert_x (* ds 0.22812839))
                         temp_y
                         0.0
                   )
            )
            (process temp_point (nth 0 string_list))
            (setq temp_point
                   (list (+ insert_x (* ds 1.11951417))
                         temp_y
                         0.0
                   )
            )
            (process temp_point (nth 1 string_list))
            (setq temp_point
                   (list (+ insert_x (* ds 2.17027974))
                         temp_y
                         0.0
                   )
            )
            (process temp_point (nth 2 string_list))
            (setq current (+ current (atof (nth 2 string_list))))
            (setq temp_point
                   (list (+ insert_x (* ds 2.94529610))
                         temp_y
                         0.0
                   )
            )
            (process temp_point (rtos current 2 3))
            (setq temp_point
                   (list (+ insert_x (* ds 3.72031246))
                         temp_y
                         0.0
                   )
            )
            (process temp_point (strcat (nth 3 string_list) "'"))
            (setq temp_point
                   (list (+ insert_x (* ds 4.49532882))
                         temp_y
                         0.0
                   )
            )
            (process temp_point (strcat (nth 4 string_list) "'"))
            (setq temp_point
                   (list (+ insert_x (* ds 5.27034519))
                         temp_y
                         0.0
                   )
            )
            (process temp_point (nth 6 string_list))
            (setq temp_point
                   (list (+ insert_x (* ds 6.04536155))
                         temp_y
                         0.0
                   )
            )
            (process temp_point (nth 7 string_list))
          )
        )
      )
      (close opened)
      (if OK
        (progn
          (setq temp_y (- insert_y
                          (+ (* 5.0 text_height)
                             (* 2.0 text_height counter1)
                          )
                       )
          )
          (command "insert"
                   "E_NAC_block_3"
                   "PS"
                   ds
                   (list insert_x temp_y)
                   ds
                   ds
                   0.0
          )
          (command "_explode" "last")
          (setq temp_y (+ temp_y (* ds -0.09375000)))
          (setq drop (/ (- 20.4 (atof (nth 6 string_list))) 0.2040))
          (setq temp_point
                 (list (+ insert_x (* ds 5.27034519))
                       temp_y
                       0.0
                 )
          )
          (process temp_point (strcat (rtos drop 2 2) "%"))
          (setq drop (/ (- 20.4 (atof (nth 7 string_list))) 0.2040))
          (setq temp_point
                 (list (+ insert_x (* ds 6.04536155))
                       temp_y
                       0.0
                 )
          )
          (process temp_point (strcat (rtos drop 2 2) "%"))
          (setq min_point (list (- insert_x (* 0.01 ds))
                                (+ insert_y (* 0.01 ds))
                          )
          )
          (setq
            max_point (list (+ insert_x (* (+ 6.43286973 0.01) ds))
                            (- temp_y (+ text_height (* 0.01 ds)))
                      )
          )
        )
        (alert "Invalid text file")
      )
    )
  )
  (setvar "orthomode" old_ortho)
  (setvar "osmode" old_osmode)
  (princ)
)

Code: [Select]
Device #,Device Name,Current Draw,Distance Between,Distance From Source,AWG10,AWG12,AWG14,AWG16,AWG18
1,P2R75,0.176,31,31,20.33,20.29,20.23,20.12,19.96
2,P2R75,0.176,23,54,20.29,20.22,20.12,19.96,19.69
3,SR15,0.066,13,67,20.27,20.20,20.08,19.88,19.58
4,P2R1575,0.1,27,94,20.24,20.14,19.99,19.75,19.37
5,SR15,0.066,5,99,20.23,20.14,19.98,19.73,19.34
6,SR15,0.066,17,116,20.22,20.12,19.95,19.68,19.25
7,P2R1575,0.1,25,141,20.21,20.09,19.91,19.61,19.15
8,P2R1575,0.1,25,166,20.20,20.07,19.88,19.57,19.08
9,SR15,0.066,21,187,20.19,20.07,19.87,19.56,19.06
"Science is the belief in the ignorance of experts." - Richard Feynman

Lee Mac

  • Seagull
  • Posts: 12905
  • London, England
Re: Read csv file and append text to drawing
« Reply #6 on: November 13, 2011, 12:30:42 PM »
Here my general concept that uses LM _ReadCSV would return a list of dotted pairs

Note that my function does not return a list of dotted pairs.

Dotted pairs are created by supplying the cons function with two atoms:

Code: [Select]
(cons 1 2)  ==>  (1 . 2)
My function will return a list of lists of each row in the CSV file:

Code: [Select]
(
    ("CellA1" "CellB1" "CellC1"  ...  "CellN1"  ...  )
    ("CellA2" "CellB2" "CellC2"  ...  "CellN2"  ...  )
    ...
    ("CellA9" "CellB9" "CellC9"  ...  "CellN9"  ...  )
    ...
)

How to get the (_ReadCSV) to read user specify columns,
Ie. All columns or user enter ie. 1,3,5 for reading columns 1,3,5 etc.

Using the current function to return the contents of the CSV file, you could use manipulate the list using such functions as car / cadr / caddr ... / nth to access the values of the relevant columns.

Or, consider the following function requiring a list of columns to read (column numbers start at one, but this can easily be altered to be zero-based):

Code: [Select]
(defun _ReadCSVColumns ( filename columns / _readcolumns file line lst )

    (defun _readcolumns ( str n lst / pos )
        (if (setq pos (vl-string-position 44 str))
            (if (member n lst)
                (cons (substr str 1 pos) (_readcolumns (substr str (+ pos 2)) (1+ n) lst))
                (_readcolumns (substr str (+ pos 2)) (1+ n) lst)
            )
            (if (member n lst)
                (list str)
            )
        )
    )

    (if (setq file (open filename "r"))
        (progn
            (while (setq line (read-line file))
                (setq lst (cons (_readcolumns line 1 columns) lst))
            )
            (setq file (close file))
        )
    )
    (reverse lst)
)

Code: [Select]
(_ReadCSVColumns <filename> '(1 3 5))
[ Will read columns 1, 3 & 5 ]

jaydee

  • Guest
Re: Read csv file and append text to drawing
« Reply #7 on: November 13, 2011, 04:58:41 PM »
Code: [Select]
(setq lst '(("1.01" "STORE ROOM") ("1.02" "MEETING ROOM") ("G.100" "PLANTROOM")
("GF.02" "COMMS") ("1.45" "TRAINING") ("2.23" "CONFERENCE")
("3.21" "KITCHEN"))
)
Sorry Lee, i did quoted on previous post the (_readcsv) return the list above. I should NOT have said "dotted pair" at all., maybe due to im reading 2 columns and playing with attribute dotted pair alot lately. Once again thankyou for your help, will let you know how the (_ReadCSVColumns) go once i have a chance to test it.

I test it and its perfect. Cheers
« Last Edit: November 13, 2011, 08:32:18 PM by jaydee »

Lee Mac

  • Seagull
  • Posts: 12905
  • London, England
Re: Read csv file and append text to drawing
« Reply #8 on: November 14, 2011, 06:42:53 AM »
Sorry Lee, i did quoted on previous post the (_readcsv) return the list above. I should NOT have said "dotted pair" at all., maybe due to im reading 2 columns and playing with attribute dotted pair alot lately.

No need to apologise - I just wanted to clarify things  :-)

I test it and its perfect. Cheers

Good stuff  :-)