Generating a Script using a Spreadsheet
From time to time you are going to be presented with data in a non-graphical form (spreadsheet, CSV, TXT, XML etc.) that you want to overlay on to your drawing so you can visualise it. A common example is some type of cross country pipe run, or pipe network (Geothermal steam, oil & gas, storm water). This type of information is usually provided as an export from a GIS, or a survey based CAD application. To initially view, you may use a text editor, or spreadsheet application.
So how do we get what can be an enormous amount of information into BricsCAD/AutoCAD without too much pain? Obviously you could create an application in LISP, or VBA to perform this type of task. However the effort involved may not be merited in the case of a one off, or if the data hasn't been provided in a consistent manner. This is where it can be easier to utilise the features of a spreedsheet to convert the raw data into a script that we can use in our CAD application.
In the following example we have a collection of data points representing the location of some Storm Water manholes. This data was provided in CSV format, which spreadsheet applications can readily view and work with. You will see that the information isn't in a form that we can bring into our CAD application directly. The coordinates aren't in X, Y, Z order, we have information we don't want (Header section, Point No column), and there is also additional information that we may want to bring in (Asset ID, Lid Level, Invert level).
From this raw data we are going to generate two scripts using the spreadsheet application to assist. I'm using Excel for this example, but it will just as readily work in other spreedsheet applications.
Script 1- Draw the Pipeline Route
On a new tab in the spreadsheet start to layout your script to undertake the actions that you want, with each spreadsheet cell representing a line on our script. The first few lines setup the environment we want the script to run in, and the drawing command that we want to use. In the example below (click on to see a bigger image), the script portion has been highlighted in yellow, with comments appearing in the adjacent column. Directly following the 3DPOLY command we need to provide coordinates in x,y,z format derived from our raw data. To do this we need to combine the coordinates from our raw data sheet into a single cell, and in the correct order. In Excel there are two ways you can do this:
Use "&" to join the data from seperate cells e.g.
='Raw Data'!C9&","&'Raw Data'!B9&","&'Raw Data'!D9
or use the function concatenate to combine our data e.g.
=CONCATENATE('Raw Data'!C9,",",'Raw Data'!B9,",",'Raw Data'!D9)
Having correctly formatted the first cell, you simply select and drag down to populate the rest of the coordinates for the polyline, provide a blank line to end the 3DPOLY command, and whatever closing statements that you may want to include.
From here we select and copy the cells representing our script, paste into a text editor and save to a file.
To run your Script simply drag and drop the file from explorer on to your open drawing, or use the SCRIPT command.
Script 2 - Place the Manholes
In our second example we will compile a slightly more involved script to insert blocks (stormwater manhole) on our drawing in the correct location, and populated its attributes with the related data from our spreadsheet. For this to work, we first must have a suitable attributed block in our drawing to use. Then on another tab in our spreadsheet, we build the command up that we want to use in parts, and then similar to our first example we compile this into a script by combining these parts into a single cell. Why do this in parts? Well it makes it a lot easier to bug find if you make a mistake. You will also see that I've made use of commenting in the spreadsheet, which makes it much easier to work through if you come back to it later on.
To work better within the spreadsheet format I'm using a bit of simple LISP, which allows us to run a command per line in our script, rather than trying to create a command that runs across several lines. You will see that we make sure that ATTDIA is turned off, otherwise in BricsCAD (not AutoCAD) the attribute dialogue box will open, which is not what we want. You will also see in both scripts that we make sure that OSMODE is off, this is to make sure that object snaps don't interefere with the coordinates given in our script.
Having compile our script we repeat the process of selecting and copying into a text editor, and saving to a file. Running our new script should produce something that looks like the following
Download the source files if you want to have a go at this yourself.