Combining AutoCAD Data Extraction Tables with Excel Tables

The CAD Geek, Donnie Gladfelter, discusses how to create a dynamic AutoCAD table using block attributes, Extract Data tool, and an Excel spreadsheet. Using this procedure a comprehensive table including block attribute data, data extraction data, and Excel table data will be combined into a single AutoCAD table. Be sure to visit Donnie at http://thecadgeek.com for more tips and tricks just like this one.

---

Closed Caption:

hello folks Donnie gladfelter from the
cad geek today I'm going to show you how
you can combine data extraction tables
inside of autocad with a Microsoft Excel
spreadsheet to create a dynamic parts
list so let me kind of introduce what
I'm talking about here
what I've got on the screen right now is
just a very simple Microsoft Excel
spreadsheet what I've got is a column
that has ID for identification number
i'm going to use this number inside of
autocad now by using that number i'm
going to be able to create a data
extraction table that will be able to
tell me how many of each part i have in
my drawing and display these other three
columns of the part number description
and unit cost all into a single table so
in order to get started with this we
need to go ahead and get into autocad so
here i have autocad first things first
I'm just going to start up a blank
drawing using the a CAD dwt file and
since we're talking about blocks and
attributes and all that one of the first
things i need to do is create a new
attribute i'm going to keep it simple
just name this guy ID for identification
real changes justification the middle
center and I think that's all the
changes that we're going to do here so
go ahead and click OK insert this guy
into my drawing the next thing i want to
do is just go ahead and make this guy
look a little bit more like a bubble and
draw a circle around it like some let's
go ahead and define this as a block now
again I'm just gonna name this ID keep
things consistent here and select these
objects like so so now I have a new
block as soon as i hit OK named ID and I
can of course type a number in like so
number one number two number three so
and online now to make things a little
bit more practical a little bit more
useful we're not actually going to use
this as a block attribute by itself so
we're going to go ahead and delete that
we're going to do those come over here
to the annotate tab and create a brand
new multi leader style i'm going to
click new names ID click continue
so one of the neat things were able to
do with multi leaders inside of autocad
and is not only have it as a multi text
or mtext leader type we can also select
a block i'm going to change the type to
block and for source block i'm going to
say user block this will give me a list
of all of the blocks in my current
drawing of course there's only one named
ID so we'll go ahead and select that guy
of course good old preview over here on
the right side of the dialog box will go
ahead and click OK and close to go ahead
and make that multimeter style part of
our drawing
let's go ahead and create a new multi
leader again I'm just going to create
some random ones real quick like so
maybe do something like this
so here i have just a few multi leaders
inside of my drawing with that block
attribute containing the number that
matches my excel spreadsheet so the next
thing we need to do is create a data
extraction table and the way we'll do
that is by coming over here on the
insert tab under linking and extraction
panel will click on the extract data
tool now it will require me to save by
drawing before i continue so go ahead
and save this guy and I'm just going to
call this part list again the name will
dictate or you'll come up with a name
for yourself
so the next thing we have to do our
first step really in this data
extraction wizard is to create a name
for our data extraction so going to
click Next i'm going to call this part
list and click Save so that will stop me
to the second step of our data
extraction wizard now we're only going
to use a single drawing in this
particular example but no you could add
individual drawings by clicking the add
drawings or if you have a bunch of
drawings that have you know this part
data contained within them and all of
those drawings are saved in a single
folder you can actually add all the
drawings in a single folder by using
this guy as well so again the
versatility of this method that i'm
showing you expands quite a bit when
we're able to pull data not only from
two sources autocad in excel but also
multiple drawings in combination with
excel were able to do that here in step
2 again we're going to keep things
simple just use a single drawing and
click Next
it's going to scan through my drawing
and what I want to do is find all the
blocks with attributes in them so i have
this multi leader one right here if I
click Next
I should have an ID tag so I'm going to
uncheck that one and might seem a little
counterintuitive but since I've got a
lot of fields here and uncheck that guy
right right click and choose invert
selection that will uncheck everything
but ID just a quick little tip there for
you
so with ID selected i'll click next and
as you can see it's gone through my
drawing and is found that with a
identification number of 1 i've got two
instances of that and with the ID number
of 2 i've only got a single instance of
that so let's go ahead and sort by the
ID column there and we're gonna come
down here and click go and Link external
data so we need to go ahead and create a
new link to our Excel spreadsheet so
i'll click on this button right here to
launch the data link manager and of
course click on the create a new data
link so i'm going to call this part list
as well like so click OK and browse out
to the excel file that I created so this
is that excel file that i was showing
you at the beginning of this
demonstration so click OK and we'll go
ahead and accept all the defaults here
we're going to link the entire
spreadsheet of the entire file and click
OK when i do that you'll notice that the
Attic that Excel spreadsheet is now
listed in the data link manager so i can
select on it and click ok and it will
show it here in the drop-down list
likewise I could also pick it if I
multiple in multiple external data links
from this drop-down list of course i
don't so we're good to go there now this
is really where the magic happens
so you'll notice these two drop-down
lists I've got one that says drawing
data column and I have another one that
says external data column so what I'm
going to do is select ID because that's
the name of my block attribute inside of
this
autocad drawing and for external data
column i'm going to say ID because that
if we switch over to excel here is the
column that I've got right here
so basically we're just matching those
two fields we can verify that this works
by clicking the check match and it will
tell us that the pairing was successful
and click ok so now everything looks
pretty good will go and click OK want to
do that you'll notice that all of that
data is now listed in this little
preview that I've got here so the one
thing I'm not really all that interested
in is the name multi-layer so just
right-click choose hide column and now
I've got a pretty good composition of
what i want my table to look like so i
get to this point we'll go ahead and
click next we're going to tell it to
insert this is a table inside of my
drawing
although you could output to an external
file such as an xls excel spreadsheet
csb access database etc so click Next
and this will start kind of the table
command i'll call this part list to give
it a good heading and we can basically
accept the defaults here if I wanted to
I could select a different table style
but we won't
next we'll go ahead and click the next
button takes us to the final step of the
data extraction wizard and we can click
finish this will have us insert that
table and there you can see I have a
table with the data link from Excel also
have the count that's live inside of
this dry all paired to this
identification table or this
identification number so if i wanted to
i could addicts additional multi leaders
or additional tags in here so maybe I
wanted to add another number two you
will notice however that this table is
not dynamic in the way that the moment
that I inserted into the drawing is not
going to update what I can do though is
selecting this table right click say
update table data links when i do that
AutoCAD will rescan my drawing or
drawings by selecting multiple and
update the count forming there you have
it a look at how we can merge block
attributes with data extraction tables
with Excel spreadsheets all into one
comprehensive
table using AutoCAD once again this is
donnie gladfelter from the cad geek
visit me at www.ge.com for more tips and
tricks just like this one