Jump to content
Newby Bern

Updating Block Attributes from an Excel Database

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 :thumbsup:

 

Thank you .

Share this post


Link to post
Share on other sites
MSasu

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

Share this post


Link to post
Share on other sites
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 .

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
Tharwat

Upload a sample drawing with the excel file .

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Tharwat

Are they in one column ?

Share this post


Link to post
Share on other sites
Newby Bern

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

Share this post


Link to post
Share on other sites
Tharwat

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

 

atts.gif

 

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

Share this post


Link to post
Share on other sites
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 by Newby Bern

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites
Newby Bern

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

Share this post


Link to post
Share on other sites
Tharwat
Tharwat, you beat me to it and I have changed my earlier response - you have cracked it and I am most grateful.

 

:D

 

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

 

You got it working as expected ?

Share this post


Link to post
Share on other sites
Newby Bern

Absolutely my dear fellow! :)

Share this post


Link to post
Share on other sites

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.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...