Formatting an Excel table for use in ArcGIS | ArcGIS Blog - Esri Blogs [PDF]

Oct 6, 2015 - This blog entry contains links to a table with guidelines for how to format your Excel table, as well as a

3 downloads 19 Views 162KB Size

Recommend Stories


ArcGIS Runtime SDK for .NET: Building Apps - Esri [PDF]
Getting started with ArcGIS Runtime SDK for .NET. - SDK tools and resources. - Build your first mapping app. • What is Universal Windows Platform (UWP)?. • What is ... Application Programming Interface. - .NET, Android, Java, etc … - No need to

Python Scripting for ArcGIS Pdf
What you seek is seeking you. Rumi

[PDF] The ArcGIS Book
Love only grows by sharing. You can only have more for yourself by giving it away to others. Brian

l'application Explorer for ArcGIS
Everything in the universe is within you. Ask all from yourself. Rumi

Collector for ArcGIS
Before you speak, let your words pass through three gates: Is it true? Is it necessary? Is it kind?

ArcGIS TUTORIAL
Be like the sun for grace and mercy. Be like the night to cover others' faults. Be like running water

Arcgis Kitap2
Before you speak, let your words pass through three gates: Is it true? Is it necessary? Is it kind?

ArcGIS Ex12007.pdf
Where there is ruin, there is hope for a treasure. Rumi

Web AppBuilder for ArcGIS
When you talk, you are only repeating what you already know. But if you listen, you may learn something

ArcGIS 9.3.1
When you do things from your soul, you feel a river moving in you, a joy. Rumi

Idea Transcript


Search

by abuckley on October 6, 2015

By Aileen Buckley, Esri Cartographer To use an Excel table in ArcGIS, it should first be formatted correctly so that ArcGIS can read the table without errors. This blog entry contains links to a table with guidelines for how to format your Excel table, as well as a couple of other useful related resources. If you don’t format the Excel table correctly, you might run into problems when using tables from Excel when you: Add an Excel table to ArcMap as data Convert an excel table to a geodatabase table using the Excel to Table geoprocessing tool. Create a Join or Relate with an Excel table or a table converted from Excel. The first two of these three cases were tested and are reported on here. View the table with the guidelines. The guidelines include references to special characters you cannot use when naming fields in ArcGIS and reserved words you cannot use when naming fields in Excel. The guidelines are for the use of geodatabases and feature classes, not shapefiles. When using shapefiles, additional dbase-based restrictions will apply, such as limiting field names to 10 characters. View the list of Special Characters in ArcGIS. View the list of Excel Reserved Words. Note that the Excel reserved words were extracted from the driver via ArcObjects. We have a function that returns the Keywords for a data source. In the case of Excel, it gets the list from the driver itself. Excel Tables and ArcGIS Tables This is an overview of the relationship between Excel tables and ArcGIS tables: A column in an Excel table is a field in ArcGIS. All rows in a table have the same columns. In a spatial table, columns store categories of information about the features, such as names, ID numbers, and area or length. A row in an Excel table is a record in ArcGIS. In a spatial table, each row represents one feature. A cell in an Excel table is a value in ArcGIS. These are a few things to note about using Excel tables in ArcGIS: ArcGIS supports both Excel 2003 and earlier .xls files and Excel 2007 .xlsx files. One advantage of Excel 2007 is that it allows much larger worksheets (1,048,576 rows by 16,384 columns) than you can have in Excel 2003 (65,536 rows by 256 columns.) If you have an .xlsx file you want to use in ArcGIS but do not have Excel 2007 installed, you will need to install the 2007 Office System Driver. It can be downloaded from the Microsoft Download Center[...1] . If you have Microsoft Excel 2010 or no version of Microsoft Excel installed, you must install the 2007 driver before you can use either .xls or .xlsx files. Excel tables are read-only in ArcGIS as well as in Excel when you have a workbook open in ArcGIS. Excel workbook and worksheet names should not contain spaces. Field names are derived from the first row in each column of the worksheet. You can view the properties of fields, set aliases for the field names, and set field visibility on the Fields tab of the table’s Properties dialog box. To learn more: Online Help: Understanding how to use Microsoft Excel files in ArcGIS http://desktop.arcgis.com/en/desktop/latest/manage-data/tables/understanding-how-to-use-microsoftexcel-files-in-arcgis.htm Online Help: Formatting a table in Microsoft Excel for use in ArcGIS http://desktop.arcgis.com/en/desktop/latest/manage-data/tables/formatting-a-table-in-microsoft-excelfor-use-in-arcgis.htm Online Help: Excel to Table Tool http://desktop.arcgis.com/en/desktop/latest/tools/conversion-toolbox/excel-to-table.htm Defining fields in tables http://desktop.arcgis.com/en/desktop/latest/manage-data/tables/design-defining-fields-in-tables.htm What are the reserved words for Esri’s file geodatabase? http://support.esri.com/en/knowledgebase/techarticles/detail/37763 Thanks to Veronica Rojas for helping me to test the various issues. Thanks to Lance Shipman and Colin Zwicker, Projects Engineers on the ArcMap Team, who helped in the review of this article. Any errors, however, are solely the responsibility of the author. This entry was posted in Analysis & Geoprocessing, ArcMap, Mapping and tagged arcgis, ArcMap, Excel, table. Bookmark the permalink.

hornbydd says: A very useful blog! I’m always telling people who “beautify” their spreadsheets that ain’t gonna work! October 6, 2015 at 12:15 pm

Log in to Reply

abuckley says: LOL! You are quite right! October 6, 2015 at 1:55 pm

Log in to Reply

esri4591 says: DIRECTION is also reserved word or not? October 7, 2015 at 6:43 pm

Log in to Reply

abuckley says: My database gurus confirm that the list I provided is a list of keywords in File Geodatabase. Each dataset has its own (RDBMS based) keyword list. DIRECTION is not a keyword in the File Geodatabase. DIRECTION is not a keyword in Excel. Hope this helps! October 9, 2015 at 10:57 am

Log in to Reply

ymaher says: Thanks for this article. Very helpful and useful for future issues. October 7, 2015 at 9:14 pm

Log in to Reply

abuckley says: Glad you found this useful! October 8, 2015 at 10:10 am

Log in to Reply

sjaretzki says: I downloaded and installed 2007 Office System Driver/AccessDatabaseEngine.exe as per the above link (http://www.microsoft.com/enus/download/details.aspx?id=23734), and it appeared to install correctly. However, I’m still unable to import an XLSX file into a FGDB (using ArcCatalog). Error message: Failed to connect to database. General function failure External table is not in the expected format. Are additional steps necessary? I’m using Windows 8.1, ArcGIS 10.2.2, Office 365 [2016] (BTW, I’m running Parallels on a Mac–but the PC side/environment is stable.) December 29, 2015 at 8:10 am

Log in to Reply

abuckley says: Seems as though you have done all the right things and you should be able to import the table. Can you please contact our Technical Support staff to help you with this issue. Thanks! December 29, 2015 at 9:33 am

Log in to Reply

Smile Life

When life gives you a hundred reasons to cry, show life that you have a thousand reasons to smile

Get in touch

© Copyright 2015 - 2024 PDFFOX.COM - All rights reserved.