November 5th, 2010

ArcGIS: Joining a Text File

Joining data in ArcGIS is a fundamental task for analysis. It allows database type joining operations in a versatile and simple manner. This process can however fail inexplicably, or so it seems, causing great frustration and anger.

An Optimal Example

Let’s assume I have country spatial data, countries.shp, containing world national land borders and the sole attribute being [countryname]. I wish to join this to my UNdata.txt containing the fields: [country] [population] [language] [etc…]

In the Table of Contents (ToC) I right click on the feature class countries. Select “Joins and Relates” >> “Joins…”. You can alternatively reach the same menu through the feature class “Properties…” and the “Join & Relates” tab. The Join Data window pops up containing three fields (there are other options beyond the scope of this topic):

  1. The field of the spatial data you wish to use to join your attribute data to
  2. The data set, in this case a text file, which has the attribute data
  3. Finally the matching field of of point 1 above

In the 3 fields of the Join Data window above I would enter:

  1. The [countryname] field
  2. The UNdata.txt filename/path
  3. The [country]* field of UNdata.txt

*If it shows up.

Once I click “OK” the UNdata.txt attribute data should be joined to the countries feature class allowing me to display the attributes of the UNdata.txt spatially.

Theoretically, and much of the time this is how it works.

Often enough though a problem erupts in steps 2 & 3 above, the selecting of the file and [field] of the attribute file. Something seems to go batsh*t crazy deep in the belly of the beast that is ArcGIS. Here are some steps that may help maintain your sanity. If these fail it’s best to take your PC out back and … I actually will show why the problems occur after some common workarounds that don’t address the problem directly.

Solving Attempts

#1: Acceptable Inputs
Only Comma Separated Value (CSV) and Tab Separated Value (TSV) text files are acceptable. ArcGIS Help is not quite accurate regarding *.txt files always being interpreted as tab separated files. ArcGIS will happily accept a *.txt file and parse it for tabs.

#2: User Errors
If you are importing files from a source other than your own perhaps there is meta data embedded at the top or bottom of your file that does not follow the same CSV or TSV format as the main data. This ‘junk’ confuses ArcGIS (rightly so) and will not even allow the file to show up in the Join Data window field #2. Perhaps your field headings start with numbers, contain invalid character or are too long. A data formatting error is also possibly causing some problems (a tab/comma missing between two fields for certain rows).

While this is simple to fix, this failed attempt at joining a text file has incited ArcGIS to take insidious steps to drive you crazy.

Solution Attempt #1: Rename File
Try changing the extension of your file to *.txt, *.csv or *.tab. Simply changing the actual name before the extension can solve the problem too.

Solution Attempt #2: Manual Editing
Get a good text editor such as Notepad++ and using the replace functionality change the format from TSV to CSV, or vice versa (replace the commas with tabs).

The cultprit: schema.ini

The actual cause of all these pains is the schema.ini file located in the same directory as your ArcMap file. In Solving Attempt #2, when you tried joining a ‘dirty’ file, ArcMap created a schema.ini file with incorrect formatting settings of your input file. Assuming I had forgotten to clean meta data from the TSV UNdata.txt file, the failed join will generate the following in the schema.ini file:

[UNdata.txt]
Format=CSVDelimited

I could simply delete the schema.ini file and try the join again or change CSVDelimited to TabDelimited in the schema.ini file. Manipulating this file is much easier and faster than either of the 2 solution steps above. To create joins in the future just be aware of what shema.ini contains and future operations will be smoother. You can read other tidbits about joining text file data at esri. It shows how to configure the schema.ini file for alternative separator values such as the semi-colon:

[customers.txt]
Format=Delimited(;)

Tags: ,

Leave a Reply