|
Tables
How to: Work with tabular data
resources About tabular data sources The ArcGIS desktop applications
allow you to work with data from a variety of sources. You can view tabular
information in ArcMap and in ArcCatalog. The data is accessed using one of two
methods. The method used depends on whether or not the data has an ObjectID
field. The source of the data may
determine the level of functionality that is available. In addition, the
behavior of some commands, such as Select by Attributes and Add field in
ArcMap, are data source dependent. OLE DB data Delimited text files and Excel
spreadsheets can also be loaded as Object Link Embedded datasets through
ArcCatalog. For details on performing these procedures, go to "Connect to Excel Spreadsheet" Select by Attributes. You cannot select records in the
table by attribute values if the table is loaded directly into the Table of
Contents (TOC) Relates. The table can only be related to
in one direction. For example, you can add a relate on a shapefile to an OLE DB
table, but you can't add a relate or an OLE DB table to a shapefile. However,
you will not be able to propagate a selection from the shapefile to the table.
Instead, you can use the identify tool to access the related data. Modifying Data. You cannot add or delete fields or
edit the values in the table from the user interface in ArcMap or ArcCatalog.
However, connections to some databases using OLE DB allow you to edit the data programmatically.
If you are unsure what type of
table you're working with and whether it has an ObjectID field, you can quickly
find out. In ArcCatalog, open the properties dialog box for the data in
question and click the fields tab. If a field of type ObjectID is not listed,
then the data does not have an ObjectID field. Accessing delimited
text file data ArcCatalog and ArcMap allow you to
directly access data in delimited text files and work with them as tables.
ArcCatalog, and the 'add data' browser in ArcMap, lists files with a .txt,
.asc, .csv, or .tab extension and assigns them a file type of Text File. Files with a .txt, .asc or .csv
extension are interpreted as comma delimited, while files with a .tab extension
are interpreted as tab delimited by default. Any file with one of these
extensions will be interpreted as a text file table in ArcCatalog and ArcMap
even if it doesn't contain tabular data. If you attempt to display a text file
that doesn't contain tabular data, ArcMap and ArcCatalog will either produce an
error or attempt to display the data as a table. To avoid this problem, give
your delimited text files a .csv or a .tab extension. This will help
differentiate text files with delimited data from unformatted text files. Setting your own text
delimiter ArcGIS uses the Microsoft OLE DB
provider for ODBC drivers and the Microsoft ODBC Text Driver for text files to
access tabular data in text files. The driver stores data description (schema)
information about each text file in a file named schema.ini so the data can be
accessed properly. This file refers only to the text data files in the
directory where it resides. Every directory with text files
and for which you have write access will contain a schema.ini file once you
have browsed or opened that directory with ArcCatalog or ArcMap. An entry
defining the delimiter is added to this file for each text file in the
directory. Information is also written to the schema.ini file when data is
exported to a text file. If the schema.ini file does not already exist in the
directory it is created for you automatically. ArcGIS uses the file extension to
determine how to set the delimiter for each text file. Files with a .txt, .asc
or .csv extension are set as comma-delimited files, while files with a .tab
extension are set as tab-delimited files. The following is an example of what
will appear in the schema.ini file for a directory with files named
demog_tab.tab, demog.txt, demog.csv and demog.asc: [geol_unit.tab]
Format=TabDelimited [geol_unit.txt]
Format=CSVDelimited [geol_unit.csv]
Format=CSVDelimited [geol_unit.asc]
Format=CSVDelimited If you have text files with a
.txt, .csv, or .asc extension and they use a delimiter other than a comma, the
data will not be interpreted properly by default. You can modify the schema.ini
file to specify the proper delimiter. Once set in the schema.ini file, ArcMap
and ArcCatalog will interpret the data correctly. The following examples show
how: For a file named geol_unit.txt
with a tab delimiter, the following can be set. As an alternative, you can
rename the file so that it has a .tab extension: [geol_unit.txt] Format=TabDelimited For a file named geochem.txt with
a semicolon delimiter, add the following: [geochem.txt] Format=Delimited(;) If the schema.ini file can't be
created in the appropriate directory (for example, a read-only directory), you
should still be able to access comma delimited text files. This is because the
ODBC text file driver tries to interpret the text file as comma delimited by
default when the schema.ini file does not exist. This means, however, that you
will not be able to access tab delimited data properly in read only
directories. If you encounter this situation, you can manually create the
schema.ini file and set the format to Tab Delimited for the appropriate files. Field names Some characters in field names are
not supported, such as dashes (for example: x-coord), spaces and brackets. You
should edit the field names in delimited text files to remove these characters
before using them. For example, you can use the underscore character instead of
the dash character in field names. Exporting text files When you export tabular data to a
text file, a comma delimiter is always used in the output file. Additionally,
information is added to the schema.ini file to ensure that the fields get
exported to the correct data type and to set the format to comma delimited. If
you look in the schema.ini file after exporting to a text file table, you will
see general information as well as the data type and field width for each field
in the output file. A typical example for a file named Export_Output.txt is
shown below: [Export_Output.txt] ColNameHeader=True CharacterSet=1252 Format=CSVDelimited Col1=OID Integer Col2=NAME Char Width 25 For more information on the
schema.ini file, do a search for schema.ini on the Microsoft MSDN Web site (http://msdn.microsoft.com/). Accessing OLE DB data ArcGIS incorporates Microsoft Data
Access Components (MDAC), which includes OLE DB providers and ODBC drivers.
These components provide easy access to information maintained in a variety of
external data sources, both relational and nonrelational. OLE DB is a new database access
API, building on ODBC technology, and is based on Microsoft's Component Object
Model (COM) development strategy. OLE DB providers handle the exchange of
information between the data provider (data source) and the data consumer (any
system or application code, for example, ArcCatalog or ArcMap) that requires
access to data. ArcGIS users have access to all
the OLE DB providers that are installed as part of the MDAC package via the
ArcCatalog and ArcMap applications. These include Microsoft's OLE DB providers
for Jet, SQLServer, Oracle, and an OLE DB provider for ODBC drivers to access
ODBC data sources. Users can create and manage their OLE DB connections in
ArcCatalog using the Microsoft Data Link Properties utility. The following section explains how
to make OLE DB connections using some of the more frequently used OLE DB
providers. For general information on how to
make an OLE DB connection to an external data source using the Microsoft OLE DB
provider for ODBC drivers and an existing file-based ODBC DSN (Data Source
Name), please see OLE DB connections to a file-based ODBC DSN. For connections to an Oracle
database using an existing ODBC DSN, please see Using the Microsoft OLE DB
provider for ODBC drivers to connect to an Oracle database. To create a connection to a
Microsoft Excel file, again using an ODBC DSN, please see Connecting to an
Excel file via OLE DB. For connections to a SQL Server
database, use the MS OLE DB provider for SQL Server. Further information on
what is required to complete the connection is provided in the online Help in
the Data Link Properties dialog box; click Next to advance to the Connection
tab and then click Help. For other frequently asked
questions, please refer to ESRI Online Support Center and search on ‘OLE DB’.
The Knowledge Base contains many documents that describe some the issues you
may encounter when working with OLE DB providers in ArcGIS. For general information on
Microsoft's Universal Data Access Strategy and OLE DB, please see http://www.microsoft.com/data. Please note that OLE DB data
cannot be modified via the user interface. However, it is possible to edit the
data programmatically. See the book, Exploring ArcObjects for more information.
Adding fields When adding a field using the
table window in ArcMap or the Table view from the Preview tab in ArcCatalog, an
Add Field dialog is displayed. This dialog lists the same field types
regardless of the data source. Each data source automatically maps these field
types to a field type that is native to that data source. The data mapping
section in How data is converted has a number of tables that describe how data
types are mapped for several data sources. To add a field, you need to
specify the field type, precision, and scale if the field is a numeric type.
For string types, you need to set the field type and the length. The Add Field dialog also provides
a default field precision or length for each field type. For numeric fields,
such as the double field type, there is no one appropriate field precision for
all data sources so a default of zero is used. Data in number fields can be
stored as either binary values or as numeric values with one byte per digit.
Most data sources support both binary field types and numeric field types. For
example, INFO allows you to use either B fields, which store binary values, or
I fields, which store data with one byte per digit, for integers. Specifying a
precision and scale of zeros means a native binary numeric type is requested. A
binary numeric type will be created only if it is supported by the underlying
data sources. If the binary type is not supported, a numeric field with the
maximum possible precision for the data source will be used. An important factor to consider
when adding a field is the precision used with the field type you specify.
ArcGIS will always respect the precision, but may adjust how it interprets the
field type. For example, if you add a field of type long integer to a dBASE
table and assign it a width of 12, the field will be interpreted as a double by
ArcMap and ArcCatalog. When the field is added to the dBASE file, it is given a
dBASE field type of N and a width of 12. As you can see from the dBASE field
mapping table described above, ArcGIS interprets dBASE number fields with a
width of 12 and 0 decimals as doubles. Some field types listed may not be
supported by the native data source. For example, fields of type BLOB are not
supported by INFO. If you try to add a field of type BLOB to an INFO table, it
will fail, and the field won't be added. |