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)




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.txt, demog.csv and demog.asc:


[] 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:





For a file named geochem.txt with a semicolon delimiter, add the following:





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:






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 (


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


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.


Back to Reference Pages