Joining text file to layer using ArcPy?

Question

I have a .txt file like this:

OID,reg,OW1980,OW1990,OW2000,OW2010
0,cea08_02,15.06,13.85,17.76,21.14
1,cea09_01,22.27,23.29,27.49,29.34
2,cea09_02,31.48,31.83,35.48,35.36
3,cea11_00,7.5,8.79,10.82,11.78
4,cea12_01,38.06,37.09,47.0,46.48
5,cea12_02,28.47,28.93,32.45,33.41
...

which I would like to join to a certain shapefile using arcpy like this:

indata = r'...path...\shapefile.shp'
infield = 'SHORT_NAME'
jointable = r...path...\OWByDecade.txt'
joinfield = 'reg'
arcpy.JoinField_management(indata, infield, jointable, joinfield)

But I get the following error message saying that there is no OID field:

ExecuteError: ERROR 000339: Input C:\Users\coug1812\Desktop\CISDA-
Statistics\Arcmap\OWByDecade.txt does not have OIDs
Failed to execute (JoinField).

The join works fine in arcmap using the 'join and relate' option on the shapefile I'm using. So is there a way to join a text file to a shapefile using arcpy or should I use another method?

Answer 1

You need to create a table view of the txt file using Make Table View and then join the view to the shapefile:

arcpy.MakeTableView_management(in_table=jointable, out_view='tempview')
arcpy.JoinField_management(indata, infield, 'tempview', joinfield)

When you add a txt file to ArcMap the table view is created automatically, this is why the join is working in ArcMap.

(You might also need to create a feature layer of the shapefile using Make Feature Layer and then join the table view to the feature layer )

• Michael Stimson: You are correct but I would like to add that joining directly to a txt (csv) or Excel table can be problematic, these files don't have rigid types. Personally I'd import the txt into a personal or file geodatabase to add some stability, this will highlight some errors in the input data that would cause problems later.

Answer 2

I tried using the MakeTableView_management() as suggested above but was getting this error:

ExecuteError: ERROR 000358: Invalid expression
Failed to execute (MakeTableView). 

So I created a GDB to which I converted my txt file like so (as suggested by Michael Stimson above):

gdb = r'...path...\OW.gdb'
arcpy.TableToTable_conversion(jointable, gdb, 'OWGDBTable')

Then the JoinField_management worked:

arcpy.JoinField_management(indata, infield, gdb+'\OWGDBTable', joinfield)