topic Re: Excel spreadsheet with thousands of circuit ref in AutoCAD Forum

Excel spreadsheet with thousands of circuit ref

Anonymous — Mon, 11 Jun 2018 13:45:12 GMT

Hello All,

I have been looking to solve this problem all over the forum but I'm yet a bit stuck.

I have an Excel spreadsheet with thousands of circuit ref. that need to go on a drawing that I'm making from scratch.

I only have a "Type0" Attribute Block with 7 attribute and a prepared the spreadsheet with the same 7 column code.

and that is where I'm stuck.

Is there a way to generate an attribute block for each of the line of my excel spreadsheet?

Thank you for your Help.

Re: Excel spreadsheet with thousands of circuit ref

JTBWorld — Mon, 11 Jun 2018 14:54:08 GMT

This requires some customization/programming to automate it. If interested with help for it please contact me.

Re: Excel spreadsheet with thousands of circuit ref

john.vellek — Mon, 11 Jun 2018 15:11:38 GMT

Hi @Anonymous,

Theoretically, this should be doable by concatenating the contents of each Excel row into a command that would insert the block and then populate the values. Once you have this created for each row, you could copy this column into a Notepad document and then run it as a script inside of AutoCAD.

There are also some add-in utilities in the Autodesk App Store that might make this a simpler process.

Please select the Accept as Solution button if my post solves your issue or answers your question.

Re: Excel spreadsheet with thousands of circuit ref

Anonymous — Tue, 12 Jun 2018 08:47:26 GMT

Hi John,

thank you for your reply.

what would be the best add-in to use for this matter?

I concatenating each cells into one.

can you advise me how to proceed?

I'm not really familiar with these thing yet!

Thank you!

Re: Excel spreadsheet with thousands of circuit ref

leeminardi — Tue, 12 Jun 2018 17:58:49 GMT

Can you post an Excel file that has a few rows of the data you would like to use to create the blocks in AutoCAD? In addition to the attributes, the Excel file should include a column for the x and y location, rotation (if relevant) and the name of the block. Also post a sample .dwg file that contains the blocks used in the sample excel file.

Re: Excel spreadsheet with thousands of circuit ref

Anonymous — Wed, 13 Jun 2018 10:26:31 GMT

Hi Leeminardi,

I attached you an example of the excel file with a few example rows. I added the posizion x,y. Where should I add the name of block??

I also attached an example of the block attributes.

Thanks!

Re: Excel spreadsheet with thousands of circuit ref

leeminardi — Wed, 13 Jun 2018 13:05:48 GMT

Please post a new version of the Excel file with the block names. Place the block names in any column. I will create the appropriate concatenate formula which should include the punctuation for the AutoCAD commands that will be used in the script.

Re: Excel spreadsheet with thousands of circuit ref

Anonymous — Wed, 13 Jun 2018 13:51:04 GMT

Hi Leeminardi,

I attached you the new excel file. Thanks you very much!

Re: Excel spreadsheet with thousands of circuit ref

leeminardi — Wed, 13 Jun 2018 21:40:54 GMT

It was a little work but I got something I think will work. As you know AutoCAD, and in turn scripts, are sensitive to the use of space characters. Several of the attribute values you use have space characters. In addition, it is somewhat tricky to get single " marks with the Excel concatenate command. I therefore found it easiest to use the VLISP command command to generate a satisfactory command that could handle the quotes and spaces and that can be entered at the command prompt. I used CHAR(34) in Excel to generate a single ". To get around the fact that some attribute values include space characters I placed quotes around them. I hope this does not cause a problem. There may be another way to deal with this probably with VLISP but I didn't want to get into writing a full VLISP program.

To use the Excel file just copy the contents of the K column to the Windows clipboard (Ctrl-C). At this point you can paste to the command line in AutoCAD or paste to Notepad (I use Notepad ++) and save the file as a txt file but with the file extension .scr. You can then run the resulting script in AutoCAD.

Here's the contents of cell K2:

=CONCATENATE("(command ",CHAR(34),"-insert",CHAR(34)," ",CHAR(34),A2,CHAR(34)," ",CHAR(34),I2,",",J2,CHAR(34)," 1 1 0 ",CHAR(34),B2,CHAR(34)," ",CHAR(34),C2,CHAR(34)," ",CHAR(34),D2,CHAR(34)," ",CHAR(34),E2,CHAR(34)," ",CHAR(34),F2,CHAR(34)," ",CHAR(34),G2,CHAR(34)," ",CHAR(34),H2,CHAR(34),")")

It generates an AutoCAD command that looks like this:

(command "-insert" "Socket_Circtuit_Ref" "0,0" 1 1 0 "1A/1L1" "A" "1" "1" "TYPE A" "Inactive" "A 1")

There is probably some streamlining that could be done but I think the approach is valid.

Note, you should turn off OSNAP before running the program. This can be done manually before you run the program or via the command: (setvar "osmode" 0)

Re: Excel spreadsheet with thousands of circuit ref

john.vellek — Wed, 13 Jun 2018 21:46:47 GMT

Nice job Lee!

I hope this works as expected for the OP.

Re: Excel spreadsheet with thousands of circuit ref

cadffm — Wed, 13 Jun 2018 23:18:43 GMT

Yes, nice job for a stranger

In this case i would pimp the command expression with object snap _non for insertion point for ignoring running osnaps.

=CONCATENATE("(command ","""_.-INSERT"" """,A3,""" ""_non"" """,I3,",",J3,""" and so on on on on ...)")

(command "_.-INSERT" "Socket_Circtuit_Ref" "_non" "0,-150" and so on on on on ...)

Re: Excel spreadsheet with thousands of circuit ref

john.vellek — Thu, 14 Jun 2018 19:48:23 GMT

Hi @Anonymous,

I am checking back to see if my post or others helped you with your problem. Please mark a post or posts as accepted solutions if they resolved the issue or give me a bit more detail on this issue so we can continue to work towards getting this solved.

Please hit the Accept as Solution button if a post or posts solves your issue or answers your question.

Re: Excel spreadsheet with thousands of circuit ref

Anonymous — Fri, 15 Jun 2018 11:20:39 GMT

Hi Guys,

Sorry but yesterday I wasn’t able to come to you.

Anyway this is really great.

I have only one problem left which is this, when I copy the lisp into the command line it doesn’t Auto Fill the attributes of my block. It ask me to place them manually.

I upload a picture to show that.

Do you know why is that and how to fix that?

I’ll be away for a couple of days, as soon I go back in the office I’ll come back to you and mark as accept solution for sure. Thank very much.

Re: Excel spreadsheet with thousands of circuit ref

Anonymous — Fri, 15 Jun 2018 11:28:08 GMT

Hi Guys,

Sorry but yesterday I wasn’t able to come to you.

Anyway this is really great.

I have only one problem left which is this, when I copy the lisp into the command line it doesn’t Auto Fill the attributes of my block. It ask me to place them manually.

I upload a picture to show that.

Do you know why is that and how to fix that?

I’ll be away for a couple of days, as soon I go back in the office I’ll come back to you and mark as accept solution for sure. Thank very much.

Re: Excel spreadsheet with thousands of circuit ref

Anonymous — Fri, 15 Jun 2018 11:29:36 GMT

Hi Guys,

Sorry but yesterday I wasn’t able to come to you.

Anyway this is really great.

I have only one problem left which is this, when I copy the lisp into the command line it doesn’t Auto Fill the attributes of my block. It ask me to place them manually.

I upload a picture to show that.

Do you know why is that and how to fix that?

I’ll be away for a couple of days, as soon I go back in the office I’ll come back to you and mark as accept solution for sure. Thank very much.

Re: Excel spreadsheet with thousands of circuit ref

leeminardi — Fri, 15 Jun 2018 12:33:34 GMT

The hyphen in front of insert should disable the presentation of a dialog box and require direct command line entry. Here's what I get entering the same command line string in AutoCAD 2018. No dialog box appears and all the attributes are correctly assigned.

Make sure ATTDIA is set to 0. It controls whether or not the hyphen will affect the display of a dialog box.

Re: Excel spreadsheet with thousands of circuit ref

Anonymous — Fri, 15 Jun 2018 12:54:23 GMT

Thank you very much Leeminardi , that’s work wonderfully.

You’ve been a legend!! Thanks guys again.

Re: Excel spreadsheet with thousands of circuit ref

leeminardi — Fri, 15 Jun 2018 13:08:08 GMT

Thanks Rick for the compliment. I am glad I could help but would you mind noting my post as the solution (your post is identified as the solution)?

Thank you,

Lee