Data Mining in AutoCAD with Data Extraction

BEN RAND: So as you're getting settled in, I've got a little bit of something for you to do. It's a lab, right? We're here to do something. So if you could just, on your machines, open up AutoCAD. And the path to the handout file is there.

If you could get those open and kind of arrange them so that you can see the PDF and AutoCAD at the same time, that will help you as we get into the exercises. And lab assistants, if you could help anybody struggling with that.

And also just so you know, on paper, this is a full class. But this is also Thursday after the party, so I don't know if everyone's going to make it. But just kind of expect that you may wind up sharing a computer with somebody. That's how these labs are kind of set up.

So anybody that just came in the last couple of minutes, up on the screen are some instructions to open up AutoCAD and open up the handout. And if you can kind of get those arranged side-by-side, that'll help. Apparently in frame, you can't alt-tab between applications, which is kind of a problem. So if you can set them up side-by-side, it'll help you get through the exercises easier.

And we'll get started in four minutes. OK. By my watch, it's 10 o'clock, so let's get started. Welcome to AU Thursday morning, post-party. I'm glad I didn't get the 8 o'clock time slot.

So by raise of hands, how many of you partied in an appropriate amount last night? And how many of you partied an inappropriate amount last night and you're feeling it? Oh, nobody's going to admit it? Come on. Somebody. How many of you got up and went to the gym this morning? Woo! That's why we got the pick of the machines this morning.

All right. So my name is Ben Rand. I am Director of IT and CAD manager and developer at Job Industrial Services. We're an industrial engineering company. Basically, that means if you put a wall and roof and it looks pretty, we don't design it. We do refineries, gas pipeline, mining type facilities.

So this is my course on data mining and AutoCAD with data extraction. Here's a whole bunch of stuff about me that you probably don't care about. I'll just go over the brief highlights. I've been using AutoCAD since release 12, not 2012, release 12. I program. Most of my days is spent programming. And I program in a lot of different languages, mostly C#.

The one thing I want you to remember is, three weeks ago I was down in Florida playing with my USTA men's league team. And we were playing for a national championship. And we wound up coming just a little bit short. And we came in third place in the whole country.

[APPLAUSE]

Thank you very much. So I figured that'll stick a little bit. Right? OK. So objectives for the lab today-- why we're here, what we're hoping to get out of this-- we're going to learn how to create tables and text labels using AutoCAD data. We'll learn how to extract data from multiple drawings. We will learn how to link Excel spreadsheets with AutoCAD data. And we will learn how to customize data extraction using sorting, filtering, formulas, and so on.

The goal-- the examples we're going to go through today are kind of architectural-based. And I realize that not all of you are in the architectural discipline. So I want you to be thinking, beyond the specific exercises that we're doing, about the work that you do and how you can apply what we're learning today to your own work. And so my hope is that you will leave class today with ideas that you can take back to work with you next week and apply to your work.

OK. So let's just talk a little bit about our CAD drawings. So we use CAD drawings, technical documents, to convey information to someone who's eventually going to build that. And we spend a lot of time making those drawings accurate and putting the information on there that we need. But a lot of times, we have additional reports that we have to produce that could also be extracted from our drawings.

So we have all this very precise geometric data and other information in our drawings. And so you really can think of our drawings as a gold mine, something that we can mine and extract data from. So we have objects that can be quantified. We have properties that can be collected. Attribute data that can also be extracted.

So we want to leverage this rich data source and do things like link text fields so that they update as our design changes, create table reports that can also be updated, and extract to or combine data from our AutoCAD drawing with Excel.

All of this is with the express goal of increasing your productivity, your accuracy, and your efficiency. So if we have that information in our drawings and we can pull it out in an automated fashion, that's good for you.

So we're going to go through a series of exercises. As we go through these exercises, the way we'll proceed with this lab is I'm going to present. And has anybody in here been in a JC [? Moletsky ?] lab? A few of you? OK. So you know when JC is presenting, your mouse turns into hot lava, right? And you're not supposed to-- that is so lame. So in here, your mouse is going to turn into a rat, and it'll bite your hand. OK. That's more awesome. Anyway, no.

So what I want to do is just watch as I present. And then the handout's very detailed. There's lots of screen shots and step-by-step instructions. And we have my lovely lab assistants in the back-- I forgot to mention them-- Matt, Frank, and Steve. Wave hello. So once I turn time back over to you to work, if you get stuck or have a question, please raise your hand. And any of them will be more than happy to help. And I will also.

OK, so our first exercise is going to be linking text labels to object properties. So let me switch over to AutoCAD. You guys can see that.

OK. So in our sample files, these are going to be on the C drive in your computer under Datasets. Ben Rand, that's me. And the lab number is AS119389. So we'll go into that folder and open up floor plan sample. Remember, your mice are rats right now. You're just watching the screen. OK.

So in this building floor plan, we're going to zoom in to the restroom area that is kind of south of the lobby and next to this printer island. And there are these two angled rooms. What we want to do here is we're going to put some hatch in representing the floor tile. And then we're going to link some labels to it to report the area of that hatch.

So the first thing I'm going to do is get these door swings out, the door blocks, out of the way. So I'm going to go to the Home tab. And on the Layers panel, click on Layer Freeze. Come on AutoCAD. And we'll pick that door. And just freeze the door layer so that's out of the way.

I'm going to create a layer for the tile hatch to go on. And I'm going to be really disappointed if AutoCAD goes this slowly. OK so we'll create a new layer called Tile. I'm going to get this-- whoops, I've already got that. OK. Sorry, I used this same file in another class. So we're create a layer called Tile. Colors 101. It's really not important. If you want to pick a different color, you can. And then I'm going to double click on that layer to set it as the active layer.

And then I'm going to go to the Hatch command. More delay. I'm going to have to do something about that. OK. So the pattern that we want to use is ANSI 37. And we're going to set the angle to 120. And then I'm going to pick inside of one of the restroom areas. And we get a hatch. I'm going to hit Enter or Close Hatch Creation. Because I want two separate hatches, one for each restroom. So we'll do another hatch and pick that point. And hit Enter.

Now I want to put in a label that's linked to the area of each one of those hatches. So I'm going to go up to the text annotation. Draw a little box for my MTEXT. I'll zoom in so you can see this. And we're just going to type in tile colon space. And then after that space, I'm either going to hit Control F or right click and choose Insert Field. And that's going to take me into the field dialog box.

Thank you email notification. Go away. OK. So we're going to do-- first, I'm going to pick the field category. It's going to be Objects. We'll then pick under field names, object. And then under Object Type, I'm going to select the hatch.

That gives me the properties of that object that I can use in the field. So we're going to choose area. And then I'm going to choose Architectural for the format. And we'll set the current precision to two decimal places. We'll click OK. And you can see that the hatch area is there. And I'm going to close that text editor.

Sorry one more thing I forgot. So I just double clicked on the text. We're going to also turn on a text mask. So I'm going to click on Mask, Use Background Mask, and Use Drawing Background Color.

So at this point, I've got a label reporting that hatch. If the hatch area were to change, if I were to stretch the dimensions of that room, that label would update automatically and report the new hatch area. OK?

We could repeat the same process for the other room. I'm going to just copy this hatch, or sorry, the text. And so right now, I just copied that, which means that the field that's in the second copy is still pointing to the same hatch. Right? So I just need to edit that field to point to the other object.

So I'm going to double click on the MTEXT and then double click on the field and then I can just do Select Object again and pick the other hatch. I do have to reselect Area. And I have to reselect Architectural. And we'll click OK. And you can see that I've got the text now reporting the hatch for each restroom area. That sounds pretty useful? Something you could use? Yeah? OK. So your turn to go to work.

So we've had several machines that for some reason the hatch pattern isn't loading up. I think in the interest of the lab, we're going to have to kind of keep moving. And we'll try and kind of brainstorm for a little bit and try and figure out why those patterns aren't loading. I apologize for that. This didn't happen in my other lab. Most everyone else that didn't have the hatch pattern problem, are you pretty well ready to go? OK.

OK, so the good news is we're not going to do any more hatching for the rest of this class. And yeah, anyway. So let's move on to the next exercise. We're going to stay in this file. So don't close out of that file yet. And let's talk about the next exercise. Yeah, that's how I feel. Any chance I left my laser pointer around on somebody's desk? Great. Oh, here it is. Phew!

OK, so this next exercise, what we're going to do is create a furniture report, basically counting up all of the blocks of certain types in the drawing. So this is a really powerful thing that we can do to get quantities.

So in the drawing, I'm going to zoom in up here just so we can kind of take a look at what we're dealing with. So if we look and select some of these blocks, I've got a chair that's called chair seven. I've got a desk called desk three. Computer called computer and phone called FN phone. And we've got some filing cabinets, and on and on. There's a lot of it. And if we zoom back out, there's quite a bit of furniture in the design.

So we want to create a report of some of this, not all of it. And we're going to use a tool called Data Extraction to do this. But before we start the data extraction command, we kind of need to ask ourselves an important question. Where do we want the results of this data extraction process to go?

We're going to be creating a table. And we just need to kind of decide is it going to go in model space or is it going to go on paper space? If it goes in model space, we have to take into account the size of the model and how big we want the text to be. And there's factors that we have to multiply, and it gets really complicated.

My preference is to generally put annotations in paper space or on the layout. It just makes a lot of that stuff, all those calculations and messy things that you have to think about, go away because we can just say I want text size that's an eighth of an inch. And that's the size we put in.

So we're going to start the data extraction command from Layout One. So on the Layout One tab, you're going to see it's just a default. Nobody saw that, right? OK. So we've just got a default layout. This would be what would happen if you click on the little Add Layout button here. Eight and 1/2 by 11.

There's a viewport that's put on there by default. It's not set to scale at this point. Obviously in the real world, I would go ahead and set up the page size. So it's 24 by 36 or whatever my paper size standard is instead of viewport scale. We're not really worried about the viewport. We're worried about getting this report. So going to go to the Insert tab. And then Linking and Extraction. And choose the Data Extraction command.

This is going to start a wizard. I'm going to talk about this in kind of excruciating detail the first time through. And then other exercises, just the important bits. So the very first step in the data extraction process is we need to decide if we're creating a new data extraction file or editing an existing one.

So in this case, we're creating a new one. And the end result of this wizard-- when we get all the way done and clicked finish-- is that it's going to create a file with a dxe extension that becomes an important part of your drawing set. So it should probably reside in the same folder with your drawings.

And you need to maintain it as something just as important as your drawings. Because if you want to come back and be able to update the table later on, that dxe file needs to be there. OK? In theory, it could live anywhere you want it to, in any folder. But it's probably a good idea to keep it with your project set.

So I'll click Next. It's going to ask where do I want that file to go. So I'll go to my datasets folder. And it will just replace what was already in there. So I'm going to call this furniture take off. And replace that one. And then, it's going to start me through a set of additional choices.

So the second, third, and fourth step of this process are going to be a filtering process. We're going to be asked to identify which drawing or drawings are going to participate in this extraction. Then we're going to filter down from potentially a lot of drawings to which objects within those drawings. And then from those objects, which properties do we want a report on. So it's just kind of a filtering of what we want to show in this data extraction.

So by default, the second step, what drawings do we want, is always going to check Include Current Drawing. OK? We have some other options, and we'll be using the Add Folder option in the next exercise. We can also use Add Drawings to go through and cherry pick exactly which drawings we want.

In this case, Include current drawing is adequate for what we want to do. So we'll click Next. And now we get to this next step of the process. Which objects within that drawing or drawings do we want to include? We're really only after blocks in this case. So you can see at the top, that we've got arcs, attribute definition. If we scroll down, we'd see lines and a bunch of other AutoCAD geometry. We're not interested in any of that stuff.

So I'm going to quickly, under Display Options, just uncheck Display All Object Types. That's going to filter me down, by default, to Display Blocks Only. Great. I'm only looking at blocks. And then you'll notice that the other checkbox here, Display Objects Currently In Use, is also checked. And that's important because if you're familiar with AutoCAD, we can have blocks defined in a drawing but not actually inserted. So there's not really any point in quantifying blocks that aren't inserted. So that box is a little bit helpful.

So the next thing I want [? to ?] do-- I've got a lot of blocks here. I don't want a report on these. I don't want a report on all of them, so I'm going to right click anywhere in the grid and choose Uncheck All. And then I'm going to go through and check chair seven, computer, desk two, desk three, both of the FCs, and FN phone. FCs are for filing cabinet. And if you don't get these selections exact, that's OK.

OK. So we've now selected which objects we want a report on. Now, the next thing we need to do is decide which properties of those blocks do we want to include. In this case, we're really only after the name of the block and a count of how many of those there are. So I'm going to right-click again in the grid. And I'm going to choose Uncheck all.

But when I do that, I want you to pay attention to the Next button. What happened? Disabled. Dang it. So I want the name property, and if I scroll down through the list, you're going to see that it's not there. It's kind of interesting. So for blocks, the name of the block is always included in the data extraction. So that's automatic.

But still, we can't proceed. So we have to pick something in order to proceed with this. I'm going to choose layer just because it's convenient. We don't really care about the layer in the data extraction this time.

So I'm going to click Next and get to this next step. So we've now gone through the filtering process, drawings, objects, properties. And now we're in. Here's the data. What do you want to do with it? And we're going to refine that data and decide how we're going to output that.

So I don't need the layer column. We already decided that. I had to to get through the wizard, but I don't need to display it. So I'm going to right click and choose Hide column. So even though it's part of the data extraction, we don't have to show it.

The next thing I want to do is reorganize this so the name column shows up first. So I'm going to click on the column and drag it to the left. And it's hard to see on my screen. It's probably hard to see up there. If I drag it far enough to the left, there's a little blue bar that shows up right to the left of the Count column. Can you see that? Barely? It's hard to see. I need to drag it that far before I let go. And once I let go, then the column will drop in the right place.

The next thing I want to do is sort the name column in alphabetical order. So if I click on the name column once, it will sort in ascending order. If I click again, descending order. And back to ascending order.

OK. So that's basically the data that I want. We'll click Next. And then it gives me an option for where do I want to output to. I can actually output to both things at the same time, both to a table and to an Excel spreadsheet or a text file or CSV.

We're going to just choose the top option. So Insert Data Extraction Table. I'm going to click Next. And because I chose table, it's asking me about some table style parameters. What table style do I want?

In this particular drawing, I haven't set up a table style. We'll see one in the next example. But I do want to input a title. So this is going to be Furniture Takeoff. OK. So I'm going to click Next.

And the last step here is basically just a, did you really get everything that you wanted? This is your last chance. You can go back and make some changes if you need to. One note about this is I could go all the way through this wizard, get to this step, and if I hit Cancel, I'm starting over the next time. If you click Finish, then your dxe file gets saved to disk. And you can come back and use the Edit option. And then it's all saved. OK? So it's important if you get this far, you probably want to click Finish.

OK. So I click Finish. I'm prompted for the table location. We'll put that in. And you can see that I've got the quantities. That's nice, right? Accurate because it's based on what's in my drawing.

Now let's look at what happens when there's a design change. So I'm going to go to the Model tab. And our design team has been told we really need to think outside of the box. So they decided that it would be ultra productive if we had a set of cubicles outside the box, outside the building. How about that? Outside workers. No? OK.

All right. The point was we made a design change. We have more stuff that we need to quantify. So let's look at what that does with the table. So I'm going to go back to layout one. It does not update automatically. It's semi automatic. So if I click on the edge of the table to pick it, right click, and choose Update Table Data Links-- watch those counts-- they update. OK?

So you've got a little bit of work to do to get it up to date, but at least we didn't have to go through and count it. So that's where we want to get to for exercise two. Let's go to work.

OK so how many of you, by raise of hands, have a good idea of how you could use this in your own work? A lot? OK, good. OK. Let's move on to the next exercise.

With the next exercise, if you get done a little bit ahead of time, there's a couple of bonus activities at the end of the exercise that don't take you through all the steps. They just kind of give you some hints about how to do it. But that will give you some additional things to work on if you're moving a little quicker.

OK. So our next exercise, we're going to create a sheet index using multiple files. How many in here use Sheet Set Manager? And you create indexes with Sheet Set Manager? OK. So if you're already doing that, and you're happy with what Sheet Set Manager is doing for you, that's great.

I mostly put this in there. I mean, there's lots of applications for this beyond sheet index. But I know that not all of you are using she set manager. And so this might be a good way for you to start creating automatic sheet indexes. OK.

So let's go back to AutoCAD. And I'm going to do this little trick. If you don't know about this, it's really awesome. It came into AutoCAD a couple of releases ago. If you right click on the drawing tab and go to Open File Location, it takes you right into the folder where that file sits. Who did not know about that trick? Is that awesome? That's a good trick. I love that one.

OK. So I'm in that folder. We're going to go into the Multiple folder, just to take a look at what's in there. In the Multiple folder, I have a whole bunch of drawings that start with a prefix for the discipline-- like A for architectural, S for structural, T for title-- followed by a dash followed by two digits that's an index number for the drawing.

In the Res folder-- that stands for resources-- there are a bunch of extra files that do not follow that pattern. And we don't really care about those. They're just the source xrefs that feed into the sheet files that are in the Multiple folder.

I mostly just wanted to show you the structure of the folders so you can understand what we're trying to do. I'm going to open up the T01 drawing. And you'll see that there's already a sheet index in there.

And this was just a table that was created by hand. So somebody had to go through, look at the title block on each drawing, type in the title. Very painstaking. And every time a new drawing gets added to the set, somebody has got to come in and update the table again.

So we would like to look at a way to automate that process. Now we're going to try and make this as close as possible to the table that's there. But I'm going to kind of upfront tell you that there are some limitations to what we can do with data extraction. So it's not going to be exact. Some of you might not like that. And if you really hate it, then I guess you can manually type your table in, your sheet index. Yeah. Anyway. OK.

So let's go back to the Insert tab. We're going to do data extraction yet again. We're going to create a new data extraction file. We'll call this Sheet Index. And I'll replace the one I have. I am going to uncheck Include Current Drawing this time because we want to focus on the Add Folder option.

So in here, I'm going to select the folder where my drawings live. It should default to the multiple folder because that's where the current drawing sits. And I'm going to choose Automatically Include New Drawings Added In This Folder. I'm going to uncheck Include Sub Folders because I don't want the drawings in the Res folder to appear. OK?

There could be plenty of opportunities where you do want the drawings in sub folders to be included. We just don't want them here. I am going to check this Utilize Wild Card Characters. Because this is kind of the brunt of the discussion.

So AutoCAD has some card characters that can help us filter out what drawings we're going to include in this drawing set. You get two of them. One is the asterisk, which means zero or more characters. And one is the question mark, which means one and only one character. And then whatever else you type in, it's just going to be taken literally.

So if we have star dot dwg that just means any file name, if it's a dwg, it's going to be included. We want to be a little more selective about it because we could have some other drawings in the Multiple folder, like floor one, floor two. And we don't want include those. We just want the ones that fit that pattern of discipline dash index number.

This kind of got pointed out to me in the lab I did yesterday on this. We can't be exactly precise with just those two options for wildcard characters. So we're going to get pretty close, but there could be some things that fall through the cracks. We've got an easy example here.

So one thing we could do is just star dash star dot dwg, which would get us anything that has a dash somewhere in it. But that could be floor dash one. That doesn't really fit the pattern.

So let's try to make it a little more precise. We can do star dash question mark question mark. And that at least gets us to, OK, you have something in front of the dash. Actually, it could start with dash, right? But we have to have a dash and then two characters. Again, you could come up with a way that it doesn't fit the exact pattern because the question mark is any character, not any number. Right? But that's pretty close to what we need to do. Should be fairly restrictive.

So we'll click OK. And obviously, this only really works if you have a pretty good naming convention for your sheets. But you can see here, it picks all the drawings. So that works for this.

We'll click Next. And the next couple steps are pretty familiar. So I'll go quickly through them. OK.

This one warning, I do want to talk about this one. So if we were extracting geometric data and we get this warning that's saying that we have drawings in our set with different units-- like we might have one drawing that has millimeters and the rest of them are inches-- this could be a problem. Because you're going to be mixing units in that extraction, geometric units that don't really correspond to one another. So if you get this message, you probably need to think about how you're extracting data and whether you can get your drawings in the same unit format. We're extracting attributes from blocks. So this doesn't really apply to what we're doing.

AUDIENCE: So it says that if we use the current drawings [INAUDIBLE] will it convert the other ones or will it just [INAUDIBLE]

BEN RAND: That's a good question. It won't convert the drawing units themselves. But it might do some factor on it and multiply. I haven't actually tried that, but thanks for pointing that out.

OK. So we'll click Yes, we can continue. The blocks we're after. There's two blocks in here. And I want blocks with attributes. So I'll use that check. And then we're going to right click and uncheck all. And the blocks that we want are title and Title. OK? For some reason, well, AutoCAD's seeing title with lowercase and Title with a uppercase T as two different blocks because they're spelled differently. They're really the same block in this set of examples.

So we'll check both of them. We'll click Next. And I'm after only attributes this time. So I'm going to right click or I'm going to unselect Attribute over here in the category filter, which seems really unintuitive until you right click and choose Invert Selection. And it just flip-flops all check marks. So that's another good way, if you only have a few things to select, you can unselect them, right click, and invert selection.

So now I'm listing only attributes. I'm going to uncheck all and choose x Sheet Content One, Sheet Content Two. So those are the three attributes from the title blocks that I want to extract. We'll click on Next. And we get a list of the data.

I do not want to show the Count column. I do not want to show the Name column. I don't want to combine identical rows either. Each drawing should have its own row. I don't like the name x. So I'm going to rename that to sheet number. I don't like Sheet Content One, so let's rename that to Title One. And Sheet Content Two, we'll rename to Title Two.

I'm going to drag sheet number one to the left and sort on that column. And then we'll click Next, Insert Date Extraction Table, Next. Here, in the table style, I did set something up. So we're going to choose Sheet Index. And we'll call the title Sheet Index. Click Next and Finish. And place our table.

OK, so you can see, based on the style, that it looks pretty close to the same. OK? I warned you that there's a couple of things that we can't really duplicate. So one of the things is we can't append one column's values to the other. Can't concatenate them. So we can't just smash that into a nice, concise sheet description. So Title One and Title Two are separated by columns.

Also you'll notice that in this table, they've actually got a row separating each group of drawings by discipline. And we can't do that exactly with data extra-- we can't do that in a single data extraction. But there's a bonus exercise in there for you to kind of figure out how you might be able to do that. OK. So this is where we want to get to. If you get done with that, then I encourage you to take a look at the bonus exercises and see if you can figure out that other part of the problem.

We need to move on. Is that all right? OK. I didn't get too many panicked looks, so I think we're ready to move on.

OK, so our next exercise is kind of a two-parter. I'm going to go through the whole sequence because we're getting a little bit short on time. 25 minutes will give me enough time to get through it and give you guys time to work on it.

So what we're going to do is we're going to take a look at a site plan that has some landscaping hatches on it on different layers that we want to extract the areas out to. And then after that, we're going to-- I don't know why I don't carry this clicker around with me-- we're going to combine that data with some cost information that we have in Excel. So we can get kind of a cost report.

So let's take a look at how to do that. So we're going to go into our dataset, landscaping. And you can see I've got this sheet set up with a border and a site plan. We can see that we've got a bunch of hatch areas representing a pond and some grass and some mulching areas in the building. And that's what we're going to extract.

So the first part of this should be very easy. We'll just do a data extraction. This is all stuff you guys have seen by now. So we'll call this Landscaping. This is the drawing that we want. So we'll click Next. And this time, we only want hatch. So I'll unselect it, right click, and Invert Selection. Click Next. We only want area. I'll do Invert Selection again. And so we get to this point.

So whoops, I forgot one thing. So let's go back. I also need the layer. That's important. OK, so we've got layer. We've got area. We've got the name. We don't need the name column. We don't need the count column. We do want to combine identical rows.

But you'll notice that I've got two ponds. I've got a couple of mulches. We can't really combine them. They're not identical because the area value is different for each one. We still want that Combine Identical Rows option checked because we're going to force it to combine these.

So I'm going to right click in the area column and choose Combine Record mode. And choose some values. So everywhere there's a match on the layer now, it will sum the values. And now I get just one line per layer of material. So that's good.

Let's move layer to the left. We'll rename that to material. And let's get the area in something that is not square inches. Because that's not really nice. OK. We'll right click on there. We're going to choose Set Column Data Format. And we're going to choose decimal. We'll set the precision to-- let's just round it off. And we're going to go into additional format.

So we need to convert from inches to square feet for this example. So I'm going to type in a conversion factor of 0.0069. And I can't remember why. It's the conversion anyway. Thousandths place is going to be-- we're going to set that as comma. We'll click OK. OK again. And we can see that we've got the area in square feet. Let's make that obvious to someone reading our table. And we'll put area square feet. in there.

We'd also like to have a total of square footage there. So I'm going to right click in the area column and go to Insert Totals Footer and Add Sum. So we're reporting landscaping material so it doesn't really make sense for me to have Building on there. It probably doesn't make sense to have Parking or Sidewalk but bear with me. We'll exclude Building. So I'm going to right click in the Material column and choose Filter Options. And in here, it gives me a list of the stuff that it extracted, the layers. And I can uncheck Building to just exclude that from our output. OK?

So now we're down to the stuff that we really want to report on. I'm going to click Next. Insert Data Extraction Into Table. I don't have a style for this one. And we're going to say Landscaping. There's Landscaping. Quick on Next and Finish. And insert our table. Move that over a little bit. Whoops.

OK. So we can see our square footage of landscaping generated by the hatch areas, separated out by layers. OK? That's pretty helpful. Now what we want to do is, we've got some costs. So let's take a look at this. I'm going to go into the open file location, and I've got a spreadsheet with landscaping costs in it. If any of you are in the landscaping design industry, I just made these numbers up.

OK. So I've got a list on the left in the column A under ID. I have a list of materials. That list of materials corresponds, funny enough, to the layer names that I put my drawing. OK?

And this is kind of a key thing to making this work, is that we need to have some data in our Excel spreadsheet that matches up with some data that we're extracting from our AutoCAD drawing. There has to be some way to correlate those two.

So in this case, I just did it with layer names. If it was block attributes, if you were doing part numbers, part numbers are great. You know, it's a unique field for each part. So that would work. The column name ID is not particularly important. I just wanted to point out that it's kind of the key, the ID field. I have my costs and the cost per square foot column.

If you open that file up to take a look at it, just be sure you close it before you start doing this next step that we're going to do. Because it doesn't like it when it's open. OK. So I already have the table inserted into my drawing. And now I've decided that I want to have costs added to it. So I want to edit the data extraction that makes up this table.

This next step is a little-- OK, extremely-- non-intuitive. If I select the edge of the table, like we've done before, I get this update table data links and write data links to external source. If I pick any cell inside of the table and right click, I get a different set of menu options under Data Extraction. So you need to pick inside of a cell in the table then right click. So Data Extraction, Edit Data Extraction Settings.

The table knows what dxe file it's associated with. So it skips that first step all together, and it takes me right into the wizard. Now the wizard remembers everything we've already done. So I can just Next through almost all of these steps. Remembers that we had hatch. Remembers that we selected area. And we get to the refined data stage.

So it's at this point that we now want to link to external data, the Excel data. So I'm going to hit Link External Data. And it takes me into the Link External Data box. I don't have any links set up yet, so I'm going to click on Data Link Manager. And then we're going to create a new Excel data link. The data link name is just for my reference purposes inside of this CAD drawing. It doesn't necessarily have to correspond to the name of the file. So I'm going to just call it Costs. Click OK.

And now we need to specify where that file is. So I'm going to click on Browse. Pick Landscaping Costs. And you're going to see, it'll take a second, but it's going to scan that file and give us a preview of the data. Sheet One is where we're getting the data from. The preview looks fine, so we'll click OK. We've got the data link set up but we're not done yet. We'll click OK again.

Now this is kind of the key point of the exercise, is we need to match up something coming from the drawing, the layer name, with something in the Excel file, the ID column. So we're going to pick in the select, the drawing data column, Material. Remember that I renamed the layer column to Material. That shows up here. So I'm just going to pick on Material. And then the external data column is ID. That's what I named it in Excel. We have this Check Match feature so we can check that there's a valid key pairing. That looks good.

And then finally down here, it's asking us which external data columns from the spreadsheet do we want to include. I could have a spreadsheet that has lots more stuff in it. Maybe I have a listing of door types and it has fire ratings and a bunch of other things. And I might want to include all of that data in here.

In this case, I only have one column or two columns, but I don't need the ID. I already have the material via the layer. So that would just be redundant. So I'm going to uncheck ID. We'll click OK, and you can see that that column gets added in now. So that's awesome because we're getting data from Excel, marrying it up with data that we're extracting from our drawing, and now what would be really cool is if we could insert a formula column to do some math for us.

So we're going to right click and choose Insert Formula Column. We'll call this Totals. And we're going to do a formula of area and square feet times cost per square feet. I'm laughing because yesterday I was pointed out to me that I had a glaring mathematical error. Let's do the math.

So this is wrong in your handout, and we're going to correct it in a second. So we let it calculate, and it comes out to 117, 000 square feet of grass. $42 million. OK, that's expensive grass, folks.