Analysis of GIS Data by Simple Examination - Introducing Geographic Information Systems with ArcGIS - page 389

Our next steps will move us towards converting the spreadsheet into a dBASE4 table. When we make this
conversion, the text in the top row becomes the headings and the remainder of the table goes into the
cells of the attribute table. Therefore, row number 2 of the spreadsheet is in the way. So we will delete it.
9. Click on the “2” of row 2. Right-click. Pick Delete.
The next step is to get a key field—a column we can use to join the spreadsheet information with the Esri
blocks shapefile table that we will obtain shortly.
10. Working with Census2.xls, put your cursor on the divider that separates the A column heading
from the B column heading and drag the separator to the right. (Clever of them to hide all those
other digits in GEO_ID!) What we want are just the last four digits (L4D) of GEO_ID. So we will
make “column F”, currently blank, our working GEO_ID and put the desired digits there. Click in
cell F1. Type the name GEO_ID_L4D and press Enter. Widen the column as necessary to get the
heading in the box.
11. Click in cell F2 and type
=RIGHT(A2,4)
which will invoke a spreadsheet function that grabs off the last four characters of the A2 cell
and puts them in cell F2. Press Enter. The number 1000 should appear in cell F2. The software
has evaluated the formula and placed the result in cell F2. Check that cell F2 now contains
“1000”. Click on F2 again and observe that the formula bar at the top of the window contains
“=RIGHT(A2,4)”.
12. Press Ctrl-C to copy the formula that underlies the number in F2 to the clipboard. Cell F2 should
be activated. Click cell F3. With the vertical slider, scroll down the rows, so you see the last one
(6022). Hold down Shift and click cell F6022, so the entire column is highlighted. Press Ctrl-V
(paste) to evaluate the formula “=RIGHT(cell number,4)” for each of the selected cells of column
F. Check the results. Each cell F value in a given row should equal the last four characters of the
GEO_ID in the A column of that row. Press Esc to clear the activation of cell F2.
13. Click in the A1 cell. Save the file with File > Save As, this time using the name Census3.xls.
Our spreadsheet is now ready to be converted to an ArcGIS attribute table. We do this with an
ArcGIS tool.
14. Close the spreadsheet. Start ArcCatalog. Start ArcToolbox. Start the tool “Table to Table” found in
ArcToolbox > Conversion Tools > To Geodatabase
For Input Rows navigate to Day_Care_Data. Click on Census3. Click Add. Instead of what you
might expect, Sheet0$ appears. Click on that to bring it into the Name field. Click Add. For
output location you want Day_Care_Data in the Name field. Name the Output Table Census4.
(The six column headings will appear in the Field Map.) Click OK. After a bit, Census4.dbf
will appear in the Catalog Tree. Preview it to see that it is all there. See Figure 6-19. (If it is
not there, or not correct, you can try again. We kept Census1 as a pristine version of the
spreadsheet. That is especially important for those who downloaded the census data. I'm sure
you don't want to go through that again!)