Writing AutoCAD Scripts With Excel
Comments(29) 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.
I cannot get it to work for some reason. I followed your directions but I get this…
Command: insert Enter BACKSPACE to interrupt script.
Command: sb Unknown command "SB". Press F1 for help.
sb is my block, any ideas?
GLad,
Can you copy/paste more of the command lines when this error occurs? This way I can get a better idea of what is causing the problem.
Instead of going through all the work to get comma’s in the file, why not save in Excel as a CSV? then open the CSV with notepad, it will seperate your values with comma’s! other than the fact that you don’t want comma’s between all the values… good thing to keep in mind though!:Cheers:Andrew
Instead of saving this a a script (.scr) file, just copy the data from the notepad and paste it into the command line. Saves eve more time.
The process of merging the columns of data in Excel can be made much easier by employing the concatenate function in Excel. Just write the function for one line of data in an empty cell, then highlight and drag the cell down to concatenate all lines of data. Highlight the result, copy, then Paste Values to complete the operation.
A neatly explained procedure for creating script using excel data.I have also created similar script files for creating specific graphs used by geologists
if you want to show text at various points you can use the -text command instead of the text command, which asks for the text on the command line
This is the best explanation of ANYTHING to do with drafting I have ever read. You should be writing the software manuals.
Thanks Phoebe.
Sir,I want to draw Cross section of Highway in Auto cad , I have Ground Level and Formation Level in Excel format. I have 7 KM data and want to Draw every 20m Cross section , so solve my problem .
Regards
Naresh
CAD Engineer
this is great post and did u know about norting and easting i mean how to draw S39degreesE.
You are pretty good, you must be an excel god.. jajajaja.
I need your help, I’m building a macro in Excel that gives me back a string that I copy and paste into command line in Autocad. is there any way to send directly that string to command line without having to copy and paste.
Thanks in advance.
hi every one around the world
this is tremendous tips i like the above tips very much i m beginer unfortunataly our cheif and other senior surveyor do not give us any useful tips i personaly very fond of my job no one teach us any tipss thanks
Thanks this is really good stuff!