Tag Archives: ArcMap
Working with Microsoft Excel in ArcGIS Desktop
There are several questions that people often ask me about working with Excel data in ArcGIS: Can I export a table directly to Excel? Why are the attributes in my Excel table all null after creating a join? Why can’t I see my .xlsx file in the Catalog window? For those of you who have used Microsoft Excel data in ArcGIS, you know that there are some special conditions to do so. Those conditions include understanding the limitations and guidelines for working with Excel data. By the way, the answer to the first question is yes, of course you can export to Excel, and I will tell you how.
Working with Excel Spreadsheets
Excel acts like containers for worksheets and named ranges, which can be added to ArcMap like other tabular datasets. For those of you who don’t already know, a named range is when you select a subset of cells in an Excel worksheet and label it. In ArcMap, worksheets will have a $ at the end of their name, while named ranges will not. For example, the QuadrantOne range of cells can be added to ArcMap as a table:
Field naming guidelines for Excel worksheets
You can see from the named range graphic that I’ve made my column headings (field names) one-word names, like PercentContaminated and RemediationDate. There is a very good reason for this. Using a space in your field names in Excel can cause you problems when working with the data in ArcMap. What usually happens when a space is left in a field name is that the values for these fields can show up as Null in ArcMap (this will happen when you’re trying to join to an Excel table). There are several other field naming guidelines that everyone needs to know:
-Field names need to start with a letter.
-Field names should only include alphanumeric characters or underscores.
– None of these: `~@#$%^&*()-+=|\,<>?/{}.!’[]:;
– No spaces (That includes before the field name, in the middle, or after it.
-Field names will be cut off after 64 characters
-Do not used reserved words as field names.
If you follow these guidelines, you will have a lot more success when using Excel data in ArcMap.
Supporting XLS and XLSX files
Have you ever shared your Excel data with colleagues and had them come back to you saying they can’t see it in ArcCatalog or the Catalog window? ArcGIS can read both XLS and XLSX files, but you may need to install an additional driver on your machine, depending on what version of Microsoft Excel you are using.
- If you have Office 2003 installed, you can read XLS files, but you will need to install 2007 Office System Driver to read XLSX files.
- If you have Office 2007 installed, you can read XLS and XLSX files.
- If you have Office 2010 installed, you can read XLS files, but you will need to install 2007 Office System Driver to read XLSX files.
Exporting directly to Excel
I know what you are thinking: it can’t be done. Let me further clarify. Exporting to Excel is not a core feature of the software; it is not something you can do out of the box. However, you can use Python to write to Excel, and there is a tool for you to do just that available from the Geoprocessing Script & Tool Gallery in the ArcGIS Resource Center. All you have to do is download the tool and unzip the toolbox, and just like that, you will be able to export directly to Excel using the Table to Excel script tool.
Kent Marten
Catalog Window
Excellent post by Cassandra on the Support Services blog about the new Catalog windows in ArcMap version 10.
Business Analyst Online Reports Add-In for ArcGIS Desktop
Some great news to share with ArcGIS Desktop users… we’ve just released a new Add-In for ArcGIS Desktop called the ESRI Business Analyst Online Reports Add-In. This add-in gives you access to high quality data from ESRI’s Updated Demographic and Business Data products right inside ArcGIS Desktop. GIS professionals in public and private sectors can use this data to assist with decisions such as evaluating areas for community needs, planning for future growth, targeting potential sites for expansion or consolidation, etc…
The Business Analyst Online Reports Add-In connects ArcGIS Desktop to reports and data from Business Analyst Online, a web application hosted by ESRI. Using any polygon layer in your ArcMap table of contents, you can select one (or more) of the 35 reports available, choose a report format (PDF or XLS), and within a few seconds, get a presentation quality report that summarizes the demographics for the area. You can even choose to append the data back to the attributes of the map layer so that you can use it in ArcMap.
Getting started is simple…
-
Install it
-
Activate your free 7 day trial
In addition, you also have access to the Business Analyst Online web application allowing you to search for businesses, thematically map areas, create trade areas, compare sites, create customized reports, and more.
The Business Analyst Online Reports Add-In is a free download from the Resource Center. A 7-day trial of basic reports is available to all users and annual subscriptions can be purchased online.
The Business Analyst Online Add-In is available for ArcGIS Desktop 9.3.1 and ArcGIS Desktop 10 now.
Content provided by Lucy Guerra
Working with Reports in ArcMap, Part III-Using Templates
Through this blog series, you have been introduced to the new reporting tools and learned a little bit about how to work with fields in a report. At this point, you may want to know how you can leverage the reporting tools to improve your reporting workflows. In ArcGIS 10, you don’t have to start from scratch every time you create a report. You can design a report template, often referred to as a report layout file, that you can use with different datasets.
When you create a report, it’s important to think about how you want to use the report. Is the report part of a repetitive task where analysis results are presented to decision makers? Are you designing a template that should be used for any report created by your department? Let’s examine what you could do if the answer to these questions is yes.
Running Reports Based on Dataset Options
On the first page of the Report Wizard, there is a Dataset Options button that allows you to decide how to filter your input data for the report. The default option is to use all records from the chosen layer. You can also choose to base the report on the selected records of the layer or define a specific query to filter your data. Here is an example workflow:
-
Create a report, using only the selected set from the layer
-
Once you’ve finished designing the report layout, save the .rtf (you have to do this in the Report Designer)
-
Now you can use that.rlf to re-run the report
You can easily run a report at any time interval. If you have a monthly meeting or are creating a map series, you can reuse the .rlf to quickly regenerate a report. However, in the above example, you will always need a selection set to have data in your report. This is great because you can run a model or perform some analysis, at the end of which you are left with a selected set of features, and you’ll be able to use the .rlf to create a report based on your results.
Using a Report Template with Different Datasets
One important feature of report layout files is that they are bound to the dataset that was used to originally create the report. This means you can rerun a report as long as the original dataset is in the map document (it does not need to be in the active data frame). You can also run or load a report if the original dataset is not in the map document. If you try to do this, you will be prompted to pick your dataset. You can choose from any layer or table that is already in the map document, or you can browse to the location of the data you want to use.
If you want to create a new report using a different dataset with an .rlf template, follow these steps:
- Click Load Report from the View menu > Reports pull-right menu
- choose the .rlf template that you want to use
- Select the data that you want to use for your report
- On the Report Source Data dialog box, map the fields in the order you want them to be displayed. Note:You can only map fields to the same type of field. If you don’t want to map a certain field, you can remove the field from the report.
- Click OK, and the report will load in the Report Designer.
Working with Reports in ArcMap, Part II-Fields in the Layout
One of the most important aspects of designing a report layout is controlling how fields are displayed. The Report Designer is the tool for customizing your report layout and defining how fields look in your final report. Below are tips and tricks for aligning, positioning, formatting, and calculating fields in a report.
Aligning and Positioning Fields in a Report
The Report Designer makes it easy to reposition fields in the report. You can grab the text elements for field headings or values and position them wherever you like. A more effective way to place text elements is to use the grid on the report or the alignment toolbar.
Checking the Snap to Grid option constrains where elements can be placed in the report, but it makes designing a report layout much easier. It becomes easier to evenly space elements apart from each other or align new elements with existing elements.
Use the buttons on the Alignment toolbar to align more than one element at a time. These buttons are particularly helpful because you can align multiple elements from different sections in the report.
Formatting Text in a Report
All the elements that use text in a report have appearance properties for formatting the way the text is displayed. Most appearance properties are common for text over a variety of applications; if you’ve used a word processing application like Microsoft Word, then you are probably familiar with the font, color, character spacing, and alignment properties of text.
The Report Designer allows you to control the behavior of text in the report using a series of properties. Are the values in a field going to be too big to display on one line? Are the field values all different lengths? Use the Can Grow, Can Shrink, MultiLine, and Word Wrap text element properties to allow the elements to resize themselves when the report is generated.
Using Expressions to Calculate Fields
One question that I’ve often been asked is, Can I use a field expression to calculate the value of a field? The answer is yes. With a text element, you can generate a calculated field value by entering an expression in the Data Field property. One of the benefits of using an expression to calculate a field in the report is that you do not have to add a new field to your dataset; the field only exists in the report.
Within expressions, you can use existing fields and common functions from the Microsoft system libraries. Here is a table of expression examples:
|
Expression Type |
Examples |
|
Mathematical |
=Quantity + 5 =Quantity – 3 =Diameter * 3.14 =Population / Area =(double)System.Math.Round(CostPerUnit*Inventory)
|
|
Conditional |
=(ValueField == 1)?”One”:”Not One” =(Price < 10)?”Clearance”:”Price-Change” =(SoilType == System.DBNull.Value) ? “No soil type specified” : SoilType
|
|
Date/Time |
=System.DateTime.Now.ToString() =System.DateTime.UtcNow.ToString()
|
|
Concatenated |
=”Average Cost: ” + avgCostField =ZipCode + ZipCode4Digit
|
|
String |
=Name.Substring(0,4) =Name.Replace(“Test”, “Final”) =Name.ToUpper()
|
Setting Up Field Properties of Numeric Fields
If you are creating a report using numeric fields, remember to properly define your field properties. The reporting tools will honor the formatting options that you set in ArcMap. To set field properties
1. Open the Layer Properties dialog box and click the Fields tab.
2. Choose the field you want to format.
3. Under Appearance properties, click Number Format.
4. Click the ellipsis button to display the Number Format dialog box.
5. Choose from the list of categories and adjust the numeric options as you like.
One question that comes up often is, Why do my numeric field values take two lines to display in the report when they should only take one line? The reason for that is determined by the numeric options for a field. By default, numeric fields are aligned to the right and set to include 12 characters. If a number is smaller or larger than 12 characters, the number will display in the Table window or the Identify window as expected; however, this character setting has an effect on how symbology labels and report fields are displayed. This character property allows you to space out labels for range classes used with the graduated symbology renderers. The reporting engine respects all field properties as well and treats this character value as a buffer that is placed before the field value. If you don’t want your numeric fields to have any blank characters before the value, change the number of characters on the Numeric Options dialog box to 0.
Content Provided by Kent Marten
Working with Reports in ArcMap, Part I-Getting Started
ArcGIS 10 introduces a new set of reporting tools for the desktop. You will now be able to create, load, and run reports using the new Report Wizard, Report Viewer, and Report Designer. Using these tools, it is easy to create and design reports in ArcMap.
Accessing the Report Commands
From the View menu in ArcMap, use the Reports pull-right menu to access the report commands. The
Create Report command launches the Report Wizard, which will walk you through creating a report. The Load Report command allows you to open a saved report in ArcMap or load a report layout file (.rlf) as a template in the Report Designer. The Run Report command allows you to regenerate a report based on an .rlf template.
Using the Wizard to Create a Report
The Report Wizard includes options for setting up the design of your report. The only requirement for creating a report is to choose a set of fields to display in the report. However, the wizard allows you to configure many properties that are common with a well-designed report. These properties include grouping, summary statistics, field sorting, and orientation of the report. One of the new features in the wizard in ArcGIS 10 is the ability to choose from a list of preset styles for your report. If you want to create your own style, you can do that as well using the Style Manager in the Report Designer.
Using the Viewer to Preview a Report
The Report Viewer lets you preview your report. The preview allows you to see how report elements are spaced, aligned, and formatted. If you want to alter any of the report properties, you can click Edit on the viewer’s toolbar, which will open the Report Designer. The toolbar also includes commands that allow you to save, export, and add the report to the layout. Export formats include PDF, HTM, TIFF, XLS, TXT, and RTF.
Using the Designer to Customize a Report
The Report Designer allows you to customize your report. Click elements in the report if you want to change their properties. The properties for any selected element are displayed in a properties grid to the right of the report view. To reposition elements on the report, simply click and drag them to the desired position. You can also resize an element by clicking it and using the handles (the little white squares) to adjust its height or width. Click Save on the main Report Designer toolbar to save a report layout file, which can be used as a template or to rerun a report.
Content Provided by Kent Marten
Validate Join – New for ArcGIS 10
New for ArcGIS 10, you can analyze a join before creating it by using the Validate Join button on the Join Data dialog box. Join validation allows you to assess any potential problems that you might encounter when creating a join. Join validation analyzes the two participating datasets to determine if there are any common problems with the data. The following is a list of what is checked in the data:
- Check for field names that start with an invalid character.
- Check for field names that contain an invalid character.
- Check for field names that match reserved words.
- Check for non-geodatabase MS Access tables
Free Live Training on Editing with ArcGIS Desktop 10
Don’t miss a free live training seminar on Thursday, June 24 about editing in ArcGIS Desktop 10.
For more upcoming free live training see http://training.esri.com/campus/seminars/index.cfm
- July 1st Getting the Most Out of the User Conference
- August 12th Exploiting Imagery in an Enterprise GIS
If you have missed any of the seminars on ArcGIS 10 make sure and view the recordings online at any time.
What’s new for map templates in ArcGIS 10
Beginning with ArcGIS 10, ESRI no longer supports map templates (.mxt) files for use with the Select Template and Getting Started dialog boxes. These dialog boxes have been updated to work with map document (.mxd) files in place of .mxt files.
The Getting Started dialog box
When the application is first opened, you can open a recently used document, a blank document, or create a new document from a template. To create a new, blank map, select New Maps, and select the Blank Map item listed on the right. Select your default geodatabase for the map and click OK.
Alternatively, create a new map from a map template. A map template is a map document (.mxd) from which new documents can be created. Templates may contain basemap layers or be a variety of commonly used page layouts. When you create a new document from a template, an untitled map is opened based on the selected template. These templates are listed under the My Templates node under New Maps. You can create new templates by saving map documents in your user profile that only you will see. Add user-defined categories (folders) and map document templates to the following location:
C:Users<user>AppDataRoamingESRIDesktop10.0ArcMapTemplates
Alternatively, you can create templates for all users of the system listed under the Templates node under New Maps. By default, system templates are located in the ArcGIS installation directory; however, the ArcMapAdvancedSettings.exe can be used to change the default location. This application is located in the Utilities folder of your ArcGIS installation directory. System templates are initially located in the following location by default:
C:Program FilesArcGISDesktop10.0binTemplates
New categories for templates are made available by creating folders beneath the templates folder locations. Categories will only appear in the Getting Started, New Map, and Select Template dialog boxes if a map document is present in the folder under the Templates or My Templates locations.
Tip - To create multiple System template locations, you can modify the following registry key, separating folders with a semicolon (;).
HKEY_LOCAL_MACHINESOFTWAREESRIDesktop10.0ArcMapSettingsTemplateDir
C:Program Files (x86)ArcGISDesktop10.0BinTemplates;E:More System Templates
Customizing the user interface
With ArcGIS 10, map documents referencing map templates (.mxt) files for configuring user interfaces is no longer supported. ArcGIS 9.3.1 map documents referencing .mxt will continue to work at ArcGIS 10; however, you cannot create new .mxt files or modify existing .mxt files for saving user interface customizations.
Converting existing map templates (.mxts) to map documents
To convert your .mxt files, open the file from the Catalog window as an untitled map document and save it to the file system.
Map Books
With ArcGIS 10 we’ve included support for map books with a combination of new data driven pages functionality and the arcpy.mapping Python scripting module. Data driven pages use the same concepts as the map book developer sample with an easy to use implementation at the ArcView license level. A supported map book application within ArcMap will be a change for those of you who have used the developer sample DSMapBook to create your multi-page map products in the past.
Data driven pages use an index layer to define page extents, just like the index layer used in the map book developer sample. We have made many enhancements throughout ArcMap to support map books. For example, both the print and export dialogs include options for multiple pages when data driven pages are enabled, and we’ve added dynamic text including page name, page number, date/time, and many of the map and data frame properties. Additional functionality also includes the ability to set up locator maps specific to your pages with new options for data frame extent, page definition queries and clip to shape. Geoprocessing tools are provided to help set up an index layer in a new Data Driven Pages toolset. They include tools for generating a standard grid index or a strip map index that follows a linear feature.
In addition to data driven pages we are providing a new Python module for working with your map documents and map books called arcpy.mapping. This module provides a means to automate more complex map book scenarios. For example, most complete map book products require title pages, overview maps, and sometimes pages of text or report information. In the past, putting these types of map books together could be time consuming, usually requiring manual assembly using a third party software package. At ArcGIS 10 putting together this type of map book product can be fully automated with a simple Python script. Many other workflows can be supported using arcpy.mapping, for example, you can cycle through all your data driven pages, update a layer’s symbology, update some layout text, and export or print each map. Arcpy.mapping opens many possibilities for the types of map books you can create, for example, a thematic atlas with multiple pages specifying a different theme on each page, or a map book with a separate layout for the left and right page, exported and assembled into a single multi-page PDF file.
