Exporting multiple tables from a geodatabase using Modelbuilder in ArcGIS | burdGIS

[Music] hi so we're going to look at how you can extract tables from a geo database in ArcGIS out to excel and if you're viewing this on the bird GIS website then this video is slightly different to the text that's below the step by step as I've embellished it a little bit if you're watching it on YouTube then you can head over to the site if you want to have a look at the textual version as well so first things first I've got an empty MXD open here I've already saved it as best practice I'm just going to change some map document properties here we've got my file name yes that's fine I'm going to change my default geodatabase which is good practice and we're going to be in My Documents and it's called triple-a demo this this folder so I'm going to set my default geodatabase as triple-a demo gdb which I made earlier and we'll okay that so if we open up our catalog we can see here that I have triple-a demo set I've got an EM XD called the same and then a triple a demo gdb now in this gdb I've got a raster demo polygon and three tables obviously this is a bit of overkill for three tables you could just explore them normally but if you've got hundreds of tables this could be really useful so that's our MXD all set up and we're ready to roll if you want to see what these guys look like I'll just add them to our table of contents and as an example table you can see here that I do not have a lot in here it's literally just one line with some sample data in it that's all so the demo poly is also nothing specifically useful it's just a polygon in there so now we're all set in terms of our MXD and our GDB we can get started with some model builder work and one thing I like to do if I'm working with model builder is certainly to use a toolbox so I'll make my own custom toolbox and then I can store my model in there so again this is slightly overkill but it's a useful tip is how to do this so you can see if we right click on our arc toolbox up here we've got the add tool box function so we don't actually have a toolbox to create yet but if I go back to my home folder and I right-click on that I can add a new toolbox and I'm going to call this toolbox you guessed it triple-a demo also so once I've created that toolbox I can then actually go to arc toolbox and add a toolbox and I'll be adding the triple a demo toolbox I'll call it triple-a because it means that it comes up nicely at the top here and there you can see my triple a demo toolbox so I don't actually have anything in it yet but that's about to change and one thing we're going to need in order to complete this is an Excel and CSV conversion tool so this is a whole toolbox that enables a conversion between ArcGIS tables and Microsoft Office Excel or CSV files so I'm going to download this we've got four tools in here table to CSV table to Excel CSV to table exhales table etc so a pretty useful thing and the link for this is going to be below the video if you're watching again on the bird GIS website it's in the text below so just follow that link so I'll download this and I'm oh and I'm just going to save the zip file right here so now I've got the zip file saved I'll just right-click and extract all it's going to extract it to excel in CSV conversion tools that works for me so extract bets and here we've got a new folder just click through this and you should see that we have another toolbox that we can add we've also got some scripts in here as well so various different things you might be able to use more of these as well for other things it's pretty useful toolbox to have in your kit so we're going to go back to ArcGIS and we're going to add that toolbox as well so I'll add that in now we have our Excel and CSV conversion tools that has just popped up and let's have a look at that you can see this scrolling thing here means that these are scripts so these are Python scripts that are working to alter our data to do some geo processing and I'll just show you one cool thing that you can do once you've got your own toolbox my triple a demo toolbox up here at the moment is empty I'm going to right click on table two Excel I'm going to copy it then I'm going to dump it in my Triple A demo so if I paste that in there you should see in a little while once it's refreshed we get this plus sign and now I've got my table to excel in there so if you are working on a project and you've got lots of different tools that you are using frequently you can just make your own toolbox and copy and paste the tools that you need in there all right let's get started with model builder so you should be able to open up the model builder window it should be up here mine is a little bit cramped because of geo referencing being open but there you can see the model builder window so I'm going to make a new model and the model builder window will open excellent then I am going to open up our catalog and I'm going to grab my demo database and plunk it in here so you can just pull a whole geo database across into model builder and one thing I would like to say about model builder is that you have to be extremely careful when you're using it because it will alter your data so if you are just testing this out if it's your first time using model builder make sure that you are pretty careful use a demo gdb like I've done here or a copy of your actual data so that you're not going to break anything and so our demo gdb is in here it's got all our data in here the rest of the polygons and our three tables next thing that we want to do is we want to iterate over what is inside our gdb so you can see here we've got at the insert menu an option to use iterators and i'm going to iterate tables and this is comes with its own set of outputs things that are going to happen and what we're going to do here is connect our gdb to iterate tables function so we just click on this little magic connect thing and we get a little wand I'll click on my demo gdb and connect that up to my iterate tables set that as the workspace and you can see it changes color to say that everything is looking good so now we've got this set up we've got our gdb here we are going to iterate all the tables in our gdb this will be the output of each table that the iterator comes across and then what we'd like to do is convert those tables to excel so I'm going to drag across the table to excel function here and again we're going to use a connector tool to connect our demo poly up to the the table to excel and we're going to put that as the input table I'm just going to rearrange my elements so that I can see what's happening there we go and fit them to the screen lovely now you might be wondering where our output is going to be saved to it's always a very good question with ArcGIS and if we hang over this we can see that currently the output excel file is going to be demo poly XLS but where will it be so if we right click on table to excel and open this particular tool up we can see the input table is demo poly that's fine the output excel file is going to go to arcgis in our documents which i never use that folder personally i'd prefer to make a new one so if i go to the Browse and I go to my home folder which is where ever my mxd is saved I'll make a new folder and I'm going to call this outputs then we'll click on that and for the file name I'm going to use variable so to signify that we're using a variable you use a percentage sign I'm going to use a variable name close a percentage sign off give it an underscore with a T and I'll save it as that so up here you can see we've now got triple a demo that's my home folder outputs and then percentage sign name percentage sign underscore T dot XLS and what that will do this signifies the percentage signs signify that we're using a variable the variable that we're going to use is the name and if we hang over the name you can see that the name is currently demo poly that's the first thing that's been found in our geo database so that means for each table this name will be replaced by the actual name of the table followed by an underscore of T and then dot XLS so let's validate our model and make sure that everything's reset and then I'm going to hit run so again we're going to take our gdb iterate over all the tables that are in there we're going to feed those into the table to excel tool and that's going to output XLS files that we can then use let's run it and off it goes done looks like everything has gone smoothly if you do get any errors they will come up in red so be sure to have a good read of what the error message is it's there for a reason it will tell you what's gone wrong and if you do have any problems please get in touch via the comments and I'll be able to help you out so if we close that off if we then go back to our I'll just move this out of the way back to catalog if we look in our outputs folder you can see that we've now got table 1 underscore T XLS table 2 table 3 and that's because the names of our tables with table 1 table 2 table 3 so that's how that variable of name actually works in practice so that's pretty cool if you've got a gdb with hundreds of tables you can quickly export them to XLS format final thing to say is that with our model we can save this to use it again obviously so we will go to model we'll save it as and if we go to our home folder we can actually save it in our to play demo toolbox and I'm going to call this batch table to excel and there it appears in our triple-a demo tool box so I'll just close that down now you will notice one thing which is if I double click on this to open it up comes up with this message tool has no parameters and that is very true that's all does not have any parameters but what we can do is right click and if we go to edit we could then run it again from here like so and our model runs again so you can see this is just one application of model builder and it's one of my favorite parts about ArcGIS the ability to build little models like this I'm just going to validate and I'll reset everything so it enables you to record your workflows and you can use these models again and again with little tweaks for depending on which project you're working on now to set the model parameters so that we don't get this popping up is probably a topic for another tutorial but if you're interested to know please let me know and I'll make a video about model parameters and making your own kind of tools within toolboxes so thanks very much for watching and please if you've enjoyed this video please hit the like button and don't forget to subscribe to the channel to get the latest videos direct to you as and when they come out so thanks a lot for watching and happy mapping good bye

Channel: burdGIS

Views: 13,143

Rating: 4.9316239 out of 5

Keywords: burdGIS, GIS, modelbuilder, arcgis, excel, tables, table, geodatabase, geodatabase to excel, .gdb

Id: uaxO-IGx6Ow

Length: 14min 20sec (860 seconds)

Published: Sat Jan 07 2017