Writing AutoCAD Scripts With Excel
A while back, I received some survey data in the form of a comma-delimited text file containing rows of Northings, Eastings, & elevations. I needed to take that information and place blocks at every location in my drawing. At the time I only had plain AutoCAD (I wasn’t using Civil 3D yet). Being lazy, I wasn’t about to sit there and type the coordinates by hand. So I wrote a script! Here is how I did it.
I opened the text file in Excel and did a Save-As so I wouldn’t ruin the original file. Then I re-arranged the data so it would make sense in a script. Remember, a script will run as if you were typing the commands in the command line.
Here is what I wanted my script to do: insert a specific block at each location (Northing-Easting). Simple enough.
The way the survey data was arranged was Northing first, then Easting, then elevation.
AutoCAD Fact: When you insert a block, AutoCAD wants the X coordinate before the Y coordinate.
Since the Northing is the Y coordinate and the Easting is the X coordinate, I needed to put the Easting column before the Northing column.
In Excel I clicked on the “A” column, right-clicked, and selected “Insert” to insert a blank column in front of the Northings.
Then I rearranged the Northing-Easting data so it was in the correct order with the Eastings before the Northings. To do this I would select an entire column, right-click, and select Cut. Then select another blank column, right-click, and select Paste. Do this until you have the columns arranged with the Eastings before the Northings.
AutoCAD Fact: When the insertion command asks you where to place a block, you have to type coordinates with commas inbetween.
To get the commas in Excel, add columns between the Northing, Easting, & elevation columns and paste commas in them. To do this, type a comma in the first cell, then select the entire column, right-click, and select Paste. This will put commas into each cell of the column.
In cell “1A” I started writing the block insertion macro - a series of commands seperated by spaces. Remember, in AutoCAD a space is the same as hitting Enter on the keyboard.
In this case I typed:
insert epp
“Epp” is the name of the block that I have in my drawing. Note that I added a space after “epp” (because after the block name is typed you need to hit enter).
Then, just like the commas, copy & paste the macro into the rest of the cells of column A.
AutoCAD Fact: The INSERT command also requires you to specify a scale and rotation angle for each block inserted.
Thus in cells G, H, & I, I typed ~1, ~1, ~0 respectively.
If you want to specify a scale-factor other than 1 for your block, you could change this. For example, if you wanted the blocks to be 20-scale, you would type ~20, ~20, ~0. The same goes for the rotation angle. If you wanted all your blocks to be at a 45-degree angle, instead of typing ~0 in the last column, type ~45.
You may be wondering why I added a ~ in front of each number. Hang on. I’ll show you why later.
Copy all the cells…
Open Microsoft Notepad (or similar text editor) and paste the data into it…
You will notice tabbed spaces exist where the cell divisions were in Excel. To remove those tabs, highlight one of the tabbed spaces, right-click, and select copy.
Then select Edit –> Replace…
In the “Find what” field, right-click and select Paste…
Leave the “Replace with” field as-is and click the “Replace All” button.
Notice the tilde (~) symbols on the right side of the text file? You will need to replace those with spaces. If we hadn’t put tildes in, there would be no way to put spaces between the numbers. To replace the tildes with spaces select Edit –> Replace…
In the “Find what” field, type ~. In the “Replace with” field simply hit the space bar once. Then click the “Replace All” button.
When saving the text file as a script, you need to set the “Save as type” to “All files” (see below) so that it will accept the .scr extension.
The script is now complete! Once you have saved it with a .scr extension, type SCRIPT on the command line in AutoCAD and browse to open your script file. It will run immediately inserting blocks at every coordinate & elevation.
This method is much easier than typing each coordinate by hand and it saves you from making costly mistakes.
One of the reasons I love to use Excel. ;-) I love these kind of tricks.
Great posting.
Thanks Jimmy! It’s tricks like this that make “drafting” a lot more fun.
Writing AutoCAD Scripts With Excel…
You’ve been kicked (a good thing) - Trackback from CadKicks.com…
i like this very much.
I really like this except I don’t have a block already in my drawing like epp. I am looking to put in various survey control points into AutoCAD.
Help?
I have added my own pt.dwg to be inserted, used your script directions and looks good. But I would like to see more on the points, like the names of the locations, so how would I add attribute definitions to all the points at the same time with another script? I have 276 points now in my drawing but no details.
Good job Zena. I’m glad you figured it out to work with your own blocks. Is your pt.dwg block attributed?
I haven’t tried it yet, but I imagine that if it was attributed you could modify the script to add the Northing, Easting, & Elevation in each block as attributes.
write another script inserting a text at different points with all options like alignment .you have to write two scripts one for blocks and another for inserting the description of point.script does not work for the command which requires doalogue box.
If you have a block with attributes for Northing, Easting, & elevation, you could add that data to each block in the same script. Set ATTDIA to 0 and AutoCAD will prompt you for the attributes on the command line.
while doing the survey instrument will read northing and easting and elevation but the location of target where it is placed has to be entered by surveyer.it can’t be applied to all surveyes.each area will have it’s own name like description which surveyor enters in the instrument.
this data when appears on acad drawing the shape of a building can be cinstructed or a road can be marked or a drainage line can be identified.othewise it will be only points.
so for every survey data can not be processed with only northing and esting.
[...] my previous post on writing AutoCAD scripts with Excel for example. If you had a block that had Northing, Easting, & Elevation attributes in it, you [...]
sir,
I want to write owen script for find distance or length between two line on every 10m or any offset get data in Excel pl.guide me
Naresh,
Can you clarify your request?
-Josh
Mr/Ms zena
each survey will have its own data.while doing survey itself we have to enter the description of a point,the northing and easting are location of a point where a target is place.in survey data a point ordinates are easting ang and northing.i.e., x,y ordinates.why do you want to place write the ordinates of a point at that point.instead you place a text at that point indicating that it is a boundary ,drainage ,building ,tree,pole etc.,
that will serve the basic purpose of survey.
scripts contain only one command in a line.you can place a block which contain attributes of north and east then you have to enter manually each and every one .because you can not give same value for all points.Mr jones can we do this.
i am sorry for the spellimg mistakes in my previous post.i am not good at typing.