arcpy.TableToTable_conversion wrong field name and data type conversion

SimoneAgostini1 · ‎07-28-2017

Hello,
I created a script in Python to convert a txt tab-delimited table into a Geodatabase table using the ‘Table to Table’ tool. When I run the script it creates the table, but field names and data types are not following the script parameters. Fields in the output table are generally named ‘field1’ ‘field2’ etc… and the ‘Double’ data type are converted as ‘text’.

This is the script:

arcpy.TableToTable_conversion(TXT, GDB, 'Output_Table', '#', r'IDCODE "Field1" true true false 4 Long 0 0 ,First,#,TXT,Field1,-1,-1;PARAM "Field2" true true false 255 Double 5 10 ,First,#,TXT,Field2,-1,-1;LON "Field3" true true false 255 Double 5 10 ,First,#,TXT,Field3,-1,-1;LAT "Field4" true true false 255 Double 5 10 ,First,#,TXT,Field4,-1,-1;VALUE "Field5" true true false 255 Double 5 10 ,First,#,TXT,Field5,-1,-1;REF_ID "Field6" true true false 4 Long 0 0 ,First,#,TXT,Field6,-1,-1;COMMENT "Field7" true true false 255 Text 0 0 ,First,#,TXT.txt,Field7,-1,-1', '#')

...

TXT and GDB are two variables containing the paths for the .txt file and the output .gdb

The 7 fields should be named: IDCODE, PARAM, LON, LAT, VALUE, REF_ID, COMMENT (I also tried to replace every 'field1,2..etc' in the code above, but I got the same outcome)

Could someone help with this please? is there something wrong in the script os is it a bug in arcpy.TableToTable_conversion?

Thanks

RandyBurton · ‎08-03-2017

Thanks for the image of the table. It was key to the answer. And, the tool acts differently outside ArcMap; it creates its own data map apparently ignoring the parameter (using version 10.2.1). *** EDIT: By inside ArcMap, I mean running the tool from ArcToolbox. By outside, I mean from a Python IDE with ArcMap closed, although running the results snippet in ArcMap's Python window showed similar issues. ***

So, regarding the table (a tab delimited text file): ArcMap sees a column with a 0 (integer) in the first row and 22.32 (double) in the second as "text". My first recommendation is to change the numbers in the first row from this:

100     0     0     0     0     200     !!
101     10     22.32     67.58     5.12     200     !!
102     20     50.55     84.12     3.65     200     !!‍‍‍‍‍‍

To this (so ArcMap will see them as doubles):

100     0     0.0     0.0     0.0     200     !!
101     10     22.32     67.58     5.12     200     !!
102     20     50.55     84.12     3.65     200     !!
‍‍‍‍‍‍‍

Since there is no header row with field names, ArcMap will default to "Field1", "Field2", etc. So my second suggestion is to add a tab delimited header row, such as:

IDCODE     PARAM     LON     LAT     VALUE     REF_ID     COMMENT
100     0     0.0     0.0     0.0     200     !!
101     10     22.32     67.58     5.12     200     !!
102     20     50.55     84.12     3.65     200     !!
‍‍‍‍‍‍‍‍‍

This will name your fields with something meaningful.

When I ran the tool outside ArcMap, I first used the following code with a data file without a header:

import arcpy

TXT = r"C:/Path/To/data.txt"
GDB = r"C:/Path/To/Default.gdb"
TBL = "table2table"

arcpy.TableToTable_conversion(TXT,GDB,TBL,"#","""IDCODE "Field1" true true false 4 Long 0 0 ,First,#,TXT,Field1,-1,-1;PARAM "Field2" true true false 4 Long 0 0 ,First,#,TXT,Field2,-1,-1;LON "Field3" true true false 255 Double 0 0 ,First,#,TXT,Field3,-1,-1;LAT "Field4" true true false 255 Double 0 0 ,First,#,TXT,Field4,-1,-1;VALUE "Field5" true true false 255 Double 0 0 ,First,#,TXT,Field5,-1,-1;REF_ID "Field6" true true false 4 Long 0 0 ,First,#,TXT,Field6,-1,-1;COMMENT "Field7" true true false 255 Text 0 0 ,First,#,TXT,Field7,-1,-1""","#")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Although the map contains the target field names ("IDCODE", etc.), when the table was created, "Field1"..."Field7" were used.

I then used the version of the data table with the header (field names) and removed the field mapping, and it created the expected result. NOTE: This was tested with ArcMap version 10.2.1.

import arcpy

TXT = r"C:/Path/To/data.txt"
GDB = r"C:/Path/To/Default.gdb"
TBL = "table2table"

# tested with ArcMap version 10.2.1
arcpy.TableToTable_conversion(TXT,GDB,TBL,"#","""#""","#")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Hope this helps.

View solution in original post

MicahBabinski · ‎07-28-2017

Does it work ok if you run the tool stand-alone (not scripted)? I have a hard time parsing that string-based field map parameter.

SimoneAgostini1 · ‎07-28-2017

Hi Micah,
yes, the stand-alone tool works correctly. Indeed I create the script simply dropping the result of the stand alone tool in to the Python window, and just replaced the Path with my variables.

JoshuaBixby · ‎07-28-2017

If you could give a line or two example of what you expect the results to be and what you are actually seeing, that would be helpful.

SimoneAgostini1 · ‎07-28-2017

Hi Joshua,
the table properties when I run the stand-alone tool look like this:

while using the arcpy script the result look like this one:

JoshuaBixby · ‎07-28-2017

As an aside, ArcPy questions are typically posted to https://community.esri.com/community/developers/gis-developers/python since ArcPy is a different API than https://community.esri.com/groups/arcgis-python-api?sr=search&searchId=815c50c2-ab20-49fe-b0ea-a45ce...‌. I realize it is a bit confusing. The growth of places/spaces in GeoNet, even by Esri itself, appears to be taking a more organic approach, much to my chagrin.

SimoneAgostini1 · ‎07-28-2017

Shall I post the same question on https://community.esri.com/community/developers/gis-developers/python and post the link here so if someone else is interessed can just be redirected there?

JoshuaBixby · ‎07-28-2017

Since we have both mentioned the Python space, it will show up in the feeds over there. I would say you are good for now with this question, in terms of posting in places/spaces.

MichaelMiller2 · ‎07-28-2017

I think your syntax to invoke field mapping is incorrect. Check the example below.

arcpy.TableToTable_conversion(in_rows="C:/tmp/SelectC01Records.txt", out_path="C:/tmp/Test.gdb", out_name="Junk", where_clause="", field_mapping="""SegCodeNew "SegCode" true true false 6 Text 0 0 ,First,#,C:\tmp\SelectC01Records.txt,SegCode,-1,-1;NM_MPNew "NM_MP" true true false 8 Double 0 0 ,First,#,C:\tmp\SelectC01Records.txt,NM_MP,-1,-1;NM_DesCDNew "NM_DesCD" true true false 4 Long 0 0 ,First,#,C:\tmp\SelectC01Records.txt,NM_DesCD,-1,-1;NM_MPDescNew "NM_MPDesc" true true false 8000 Text 0 0 ,First,#,C:\tmp\SelectC01Records.txt,NM_MPDesc,-1,-1;BrkeyNew "Brkey" true true false 8000 Text 0 0 ,First,#,C:\tmp\SelectC01Records.txt,Brkey,-1,-1""", config_keyword="")‍

SimoneAgostini1 · ‎07-31-2017

I Michael, thanks a lot for your reply.
Unfortunately I am still getting the same issue.