Updating Block Attributes from an Excel Database

[Newby Bern]

Hello Community

 

After many hours looking for a solution to my 'problem' I have been forced to sign up to a website in which there are far more skilled people than I in their programming skills. My issue isn't new but I am unable to find a straightforward solution. It is this...I wish to update individual blocks with different attributes using their handles from an excel spreadsheet on loading any applicable drawing.

 

As I have it I have

 

Handle | Blockname | Attribute 1 | Attribute 2 | Attribute 3 | etc.

 

I have found Lee Mac's Update Titleblock Attributes and (as I see it) this is similar to what I am after in a different form however, I could be wrong...!

 

Could someone please advise me?

 

Many thanks in advance.

[Tharwat]

Welcome to CADTutor .

 

I have wrote this routine and hope that it would meet your needs , and be informed that the Excel file must be with the extension .csv to allow the code to select it .

 

Try it and let me know .

 

(defun c:Test (/ Deconstruct_Strings f o st bks ss l)
 ;;    Author : Tharwat Al Shoufi        ;;
 ;;    Date   : 15. April. 2014        ;;
 (defun Deconstruct_Strings (string delimiter / pos lst)
   (while (setq pos (vl-string-search delimiter string 0))
     (progn (setq lst (cons (substr string 1 pos) lst))
            (setq string (substr string (+ pos 2) (strlen string)))
     )
   )
   (if string
     (setq lst (cons string lst))
   )
   (setq lst (reverse lst))
 )
 (if (and (setq f (getfiled "Select Excel file to Update Attributes ..."
                            (getvar 'DWGPREFIX)
                            "csv"
                            16
                  )
          )
          (setq o (open f "r"))
     )
   (progn (while (setq st (read-line o))
            (setq l (cons (Deconstruct_Strings st ";") l))
          )
          (setq bks
                 (apply 'strcat
                        (mapcar '(lambda (u) (strcat u ",")) (mapcar 'cadr l))
                 )
          )
          (close o)
   )
 )
 (if (and bks
          (setq
            ss (ssget "_X" (list '(0 . "INSERT") '(66 . 1) (cons 2 bks)))
          )
     )
   ((lambda (i / sn h get e n)
      (while (setq sn (ssname ss (setq i (1+ i))))
        (setq h (cdr (assoc 5 (entget sn))))
        (if (and l
                 (vl-some '(lambda (x)
                             (if (vl-some '(lambda (u) (eq u h)) x)
                               (setq get x)
                             )
                           )
                          l
                 )
            )
          (progn
            (setq n 1
                  l (vl-remove get l)
            )
            (while
              (/=
                (cdr (assoc 0 (setq e (entget (setq sn (entnext sn)))))
                )
                "SEQEND"
              )
               (if (eq (cdr (assoc 0 e)) "ATTRIB")
                 (entmod (subst (cons 1 (nth (setq n (1+ n)) get))
                                (assoc 1 e)
                                e
                         )
                 )
               )
            )
          )
        )
      )
    )
     -1
   )
 )
 (princ)
)(vl-load-com)

[stevesfr]

Tharwat, I'm probably doing something wrong. My block is key-item having two attributes.

My csv file looks like this: handle,key-item,4-DI,100

the existing blocks attributes are 6-PVC and 400

Executing the lisp, nothing gets changed in the drawing.

The "handle" of course is obtained by "listing" the block before completing the csv file in order to obtain the handle name.

thanks in advance for help.

Steve

[Lee Mac]

Tharwat, note that CSV files most commonly use a comma as a cell delimiter character (hence the name 'Comma Separated Value'); for full compatibility you will need to account for both comma & semi-colon CSV delimiters (also consider if these delimiter characters or double-quote characters appear as literals in the CSV cell).

 

On a side note, rather than iterating over all attributed blocks of the given name found in the drawing database, it would be more efficient to use the handent function to convert the handle to the block reference entity directly, hence processing the minimum number of blocks necessary.

 

Lee

[Tharwat]

Tharwat, I'm probably doing something wrong. My block is key-item having two attributes.

Steve

Hi Steve .

 

I don't know what are the contents of your Excel file besides that the block attributes' handles and Block names as well , so try to follow what the OP request in the post according to their sequence of properties in the Excel file .

Otherwise you may need to upload a sample drawing with a sample Excel file that you are using to check them out for you .

 

Guess : try to replace the semi-colon from the routine to comma and try again .

 

Thanks .

 

 

Tharwat, note that CSV files most commonly use a comma as a cell delimiter character (hence the name 'Comma Separated Value'); for full compatibility you will need to account for both comma & semi-colon CSV delimiters (also consider if these delimiter characters or double-quote characters appear as literals in the CSV cell).

 

I have noticed that delimiter changes from user to another but I still do not know when we in need of accounting for that delimiter if it is comma or semi-colon although I tried the code on two OS 32 and 64 and the outcome is the same in the two systems with successfully done .

 

On a side note, rather than iterating over all attributed blocks of the given name found in the drawing database, it would be more efficient to use the handent function to convert the handle to the block reference entity directly, hence processing the minimum number of blocks necessary.

 

Lee

 

Yes that is a very good idea

 

Thank you .

[MSasu]

Please note that, despite its name, the CSV format will use as delimiter the List Separator character set in your Windows Regional Settings.

[Tharwat]

That's correct Mircea .

 

Here is the one that wrote by Lee in one his routines .

 

(vl-registry-read "HKEY_CURRENT_USER\\Control Panel\\International" "sList")

 

Since that we have discussed about registry , what are the other information that we might in need of in AutoLISP and how to get them ?

 

Thanks .

[MSasu]

Just don't forget that what you will retrive from registry is current workstation's environment and will not neccesary resemble the one used where CSV file was created.

[Newby Bern]

Gentlemen

 

Thank you for all your interest (in Lee Mac's case 'honoured'!) and, in particular, Tharwat for your effort. Unfortunately, as Stevesfr says, on loading the lisp routine, the line which says "Select Excel file to Update Attributes ..." does not appear on the command line to allow its operation. As to the intricacies of the whys' and 'wherefores' I can only throw my hat back into the ring and bow to your greater knowledge. I didn't think that it would raise such an interesting conversation!

[Tharwat]

Upload a sample drawing with the excel file .

[Newby Bern]

Tharwat, I am unable to do this as my drawing is nearly 1Mb! (all information is valid.) However, I attach a CSV file for one of my blocks created from block attribute extraction. What I am after is to alter any attribute (excluding the handle) so that this information is automatically updated through a routine on opening my drawing.

 

Correction, due to company restrictions it appears that I cannot upload my CSV file to I set it out below;

 

HANDLE,BLOCKNAME,TYPE,COLOUR,NUMBER

E7B3,PA SPEAKER,WALL,Green,AB002/111

C7FC,PA SPEAKER,WALL,Grey,AB002/112

C7F4,PA SPEAKER,WALL,Green,AB002/113

C1DF,PA SPEAKER,WALL,Grey,AB002/114

C1D7,PA SPEAKER,WALL,Green,AB002/115

C1C1,PA SPEAKER,WALL,Green,AB002/116

C1B9,PA SPEAKER,WALL,Grey,AB002/117

5A78,PA SPEAKER,WALL,Green,AB002/118

50AD,PA SPEAKER,WALL,Green,AB002/119

4E02,PA SPEAKER,WALL,Green,AB002/120

4DD0,PA SPEAKER,WALL,Grey,AB002/121

4DC6,PA SPEAKER,WALL,Grey,AB002/122

4DBC,PA SPEAKER,WALL,Green,AB002/123

4D8A,PA SPEAKER,BEAM,Grey,AB002/124

4D80,PA SPEAKER,WALL,Green,AB002/125

4D6C,PA SPEAKER,WALL,Grey,AB002/126

4D62,PA SPEAKER,WALL,Green,AB002/127

4D58,PA SPEAKER,WALL,Grey,AB002/128

4D4E,PA SPEAKER,WALL,Green,AB002/129

4D44,PA SPEAKER,WALL,Grey,AB002/130

 

Thanks in advance.

[Tharwat]

Are they in one column ?

[Newby Bern]

Yes, column delineated - I have created this CSV from an .xlsx file

[Tharwat]

Just replace in my previous codes the semi-colon to comma as the following and try it gain .

 

 

(Deconstruct_Strings st[color=blue][b] ","[/b][/color])

[Newby Bern]

Tharwat, humble apologies!! I realised that I need to type 'test' to start the routine, then I get "Select Excel file to Update Attributes ...". Although, from what you are now saying should be "Select CSV file to Update Attributes ..." (minor point!). I see that by 'converting' the first 'test' line to a statement (or removing it), on running the routine asks straight away for the file to load and it works a treat. So very well done and I appreciate your efforts, thank you.

Edited April 16, 2014 by Newby Bern

[abra-CAD-abra]

Can this not be achieved by simply using Express Tools?

 

Express Tools > Blocks > Export Attribute Information > Specify Output File Name > Select Blocks

 

Open the resultant text file in Excel, edit your attributes and hit save.

 

Back to AutoCAD..

 

Express Tools > Blocks > Import Attribute Information > Select Saved Text File

[Tharwat]

Tharwat, as keen as you are, I refer you back to Stevesfr and Lee Macs responses.

 

Their replies talking about something else my dear .

 

The routine doesn't call for me to "Select Excel file to Update Attributes ..." which (from what you are now saying) should be "Select CSV file to Update Attributes ..." .

 

Put the following codes in the command line then press Enter and let me know what message or action may take a place .

 

(getfiled "Select Excel file to Update Attributes ..." (getvar 'DWGPREFIX) "csv" 16)

[Newby Bern]

Tharwat, you beat me to it and I have changed my earlier response - you have cracked it and I am most grateful.

[Tharwat]

Tharwat, you beat me to it and I have changed my earlier response - you have cracked it and I am most grateful.

 

 

You 're very welcome and I am very glad to hear that .

 

You got it working as expected ?

[Newby Bern]

Absolutely my dear fellow!