arcpy.TableToTable_conversion wrong field name and data type conversion

1676
15
Jump to solution
07-28-2017 07:08 AM
SimoneAgostini1
New Contributor II

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



0 Kudos
1 Solution

Accepted Solutions
RandyBurton
MVP Regular Contributor

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,"#","""#""","#")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Final Table

Hope this helps.

View solution in original post

15 Replies
MicahBabinski
Regular Contributor

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.

0 Kudos
SimoneAgostini1
New Contributor II

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.  

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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. 

0 Kudos
SimoneAgostini1
New Contributor II

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:


0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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.

0 Kudos
SimoneAgostini1
New Contributor II

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?

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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
Regular Contributor

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
New Contributor II

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

0 Kudos