Lab 3 - Part 1 - Preparing Excel Spread Sheets for ArcMap.

the objective of lab 3 is to get the students familiar with the abilities of in word importation of datasets into GIS so different kinds of data sets like Excel or you know in the past we worked with the CSVs and the text files so in this in this web video we're going to see how importing into GIS works out so the first thing that we're going to do is go to our blackboard site and get our our Excel spreadsheets we're going to get the Excel spreadsheets from the lab to files from last week and there's gonna be two spreadsheets in that zip file and so what we're going to do is just click on the lab file choose download file and download the lap to five just go ahead and open the files don't really need it save zip files and once you open it extract all your files to your H Drive so H three we're also going to get the lab 3 files from blackboard and that's just the same process of just clicking opening and extracting to our each drive okay now that we extracted them let's go check out what our library files look like so I'm going to go to my computer and open them up in my H Drive the H Drive is your first one that shows up at your Eid it is H but you just don't see the letter H so I did it lab underscore three and now I can see what I've extracted just going to change this so we're going to look at them and if we look at the different files in here we have places the Excel spreadsheets us concise spreadsheet in this usps state codes we're not going to really need USPS state code so we just go ahead and delete those so if we open up places as our first one and we see this spreadsheet here we'll see that this is all the census places and we have a latitude and longitude here this latitude and longitude is already in decimal degrees and will be easily added into arcmap but the thing is that we're only really interested in working in Texas and as we can see here this is since this places for all the 50 states so what we're going to do is use our filter tool just to filter out Texas so if we click on the state column and choose filter we'll be able to do things that will filter out only Texas the filter tool can be found here on the ribbon for home called sort and filter and a new click filter after you see your your F column with filters go on you'll be had this little drop down menu and right now you can see that we have everything that's shown now the different values that are in this this column will show up as optional things to turn on and off we're going to turn off the select all and have select none and then we're going to scroll down to TX and that's going to be Texas and when we hit OK then we end up with only the Texas stuff what I want to do now is I want to select everything that is being shown right now and copy it so I just click on this right corner here that selects everything and then either I can right-click here and say copy I can do control C or I can click on this copy here once I have a copied these little things will be highlighted and this little dancing kind of line will happen what I'm going to do now is that I'm going to open up a new spreadsheet so I'll use the office button new new blank workbook and I say create it makes a new blank web spreadsheet and then I'm going to paste that into here just click on the first one and then click paste this will give me all my my values of just Texas into one spreadsheets I can spread these out a little bit to see them better and I can see here latitude/longitude if I want I can just go ahead and delete out certain things that I don't need to make things clear or I'll just leave it alone so I'm going to save this app as now as Texas so I'm going to go to H Drive my lab 3 folder and then I'm going to call this Texas places TX places such of the places Excel worksheet a workbook a made it just into Texas save this so basically now I took my tech my places and just found Texas and because in this exercise we're just going to want to look at the Texas insurance so this is going to be fine so we're going to close this now and close this places and doing it says you want to save changes we don't need to save changes on this one that was our raw pasta so now if I look here I should see Texas places which I see here so now we need to do the same thing with our us concise data set so let's just go ahead and open this in Excel now and then we're going to see here instead of having Texas code state codes we're going to have full state names so we do the same thing with the filter tool so we click on the column sort and filter and filter it this will let us again choose just Texas so I choose Texas now and again I end up with just Texas now I can do the same select all I click on the corner copy make a new spreadsheet blank workbook and then paste it now I have the Texas concise one issue though here is that our long and lats that we have in this one will not work in arcmap for our ad XY data because our ad XY data only takes decimal degrees so what we need to do is make a new column for each of these lats and lungs that will add our our decimal degree version of this you can see here we have live degrees Latin minutes lot seconds and then the and then the hemisphere that it's in and now we need just a full latitude and does one okay so the way we're going to do that is that we're going to insert a column here by right-clicking and saying insert so if you right click on any column in Excel and you choose insert it will add a blank column there we want to do that here and call this latitude we're also going to want to do that for our lunch to use our lawn shoes also degrees minutes and seconds so we're going to right click here we have elevation and say insert that's an out of blink and they're going to call this one longitude great so I dragged in now all the way to the bottom I can see that it still continued to only add up the ones next to it perfect so now let's do longitude go back up to the top here spreadsheet and you'll see here in longitude we're going to use the same formulas over here but the only difference is that since we're in the Western Hemisphere we're going to have to add a negative sign to longitude so but the thing is that since this cell here the way we expanded it down we can also copy and paste it here so I'm going to copy this cell and whenever I pasted into longitude is going to paste that formula not the value into that cell so then it starts adding this up editing I went up and I now went up with the conversions because it's in the same relative positions to that cell so if I click on there perfect now the only problem is needs to be negative so I'm going to modify the formula by going and putting parentheses around the whole thing and multiplying it by negative 1 when I do that I get my negative 97 longitude degrees longitude which is in the Western Hemisphere so then I'll go to the corner click on it and drag down again to expand it out and that's going to expand out my Excel spreadsheet for my count all the way through so I dragged it all the way down to the bottom and now I've expanded it out and if you see everything still works perfect great so now I will save this excel spreadsheets as and call it go to my X Drive and then I'm going to go to my lab 3 folder that I'm working in and then I'm going to save it as TX in size so that that's saved so I'm going to close this everything is is working out fine now don't want to save to my us concise no I don't need to because I didn't change it in new ways so perfect now I'd TX place and checks concise and both of them are only Texas so that's good so now these are going to be ready to be imported into arcmap so on the next video we will import these into arcmap

Channel: Moulay Anwar Sounny-Slitine

Views: 37,946

Rating: 4.8620691 out of 5

Keywords: capture, Gis

Id: phtfoa2pv_E

Length: 11min 30sec (690 seconds)

Published: Fri Sep 17 2010