Arc Pro Field Mapping not working in Export Table conversion tool

[FranklinAlexander]

I have done this hundreds of times, but something seems to have changed in the last Arc Pro update. I have a Python script where I am trying to convert a spreadsheet into a point layer.  Below is what I tried first, but the latitude and longitude fields will not convert to Double. 

# Process: Excel To Table (Excel To Table) (conversion)
arcpy.AddMessage("Converting spreadsheet to DB Table")
logging.info("Converting spreadsheet to DB Table")
excelToTable = os.path.join(TempWS, "excelToTable")
arcpy.AddMessage("Excel to table path " + excelToTable)
arcpy.conversion.ExcelToTable(Input_Excel_File=spreadsheet, Output_Table=excelToTable, Sheet="Debris Harvest Data Florida")
fm = ("list_name \"List Name\" true true false 100 Text 0 0,First,#,excelToTable,list_name,0,254;" +
          "item_id \"Item ID\" true true false 10 Text 0 0,First,#,excelToTable,item_id,0,254;" +
          "itemname \"Item Name\" true true false 255 Text 0 0,First,#,excelToTable,itemname,0,254;" +
          "material \"Material\" true true false 255 Text 0 0,First,#,excelToTable,material,0,254;" +
          "quantity \"Quantity\" true true false 255 Text 0 0,First,#,excelToTable,quantity,0,254;" +
          "description \"Description\" true true false 255 Text 0 0,First,#,excelToTable,description,0,254;" +
          "latitude \"Latitude\" true true false 8 Double 0 0,First,#,excelToTable,latitude,0,254;" +
          "longitude \"Longitude\" true true false 8 Double 0 0,First,#,excelToTable,longitude,0,254;" +
          "altitude \"Altitude\" true true false 255 Float 0 0,First,#,excelToTable,altitude,0,254;" +
          "radius \"Radius\" true true false 255 Float 0 0,First,#,excelToTable,radius,0,254;" +
          "location \"Location\" true true false 255 Text 0 0,First,#,excelToTable,location,0,254;" +
          "timestamp \"Timestamp\" true true false 255 Text 0 0,First,#,excelToTable,timestamp,0,254;" +
          "dt \"Datetime\" true true false 255 Date 0 0,First,#,excelToTable,dt,0,254;" +
          "project_name \"Project Name\" true true false 255 Text 0 0,First,#,excelToTable,project_name,0,254;" +
          "project_id \"Project ID\" true true false 255 Long 0 0,First,#,excelToTable,project_id,0,254;" +
          "username \"Username\" true true false 255 Text 0 0,First,#,excelToTable,username,0,254;" +
          "user_index \"User Index\" true true false 255 Long 0 0,First,#,excelToTable,user_index,0,254;" +
          "manual_upload \"Manual Upload\" true true false 255 Text 0 0,First,#,excelToTable,manual_upload,0,254;" +
          "event_name \"Event Name\" true true false 255 Text 0 0,First,#,excelToTable,event_name,0,254;" +
          "id \"ID\" true true false 255 Text 0 0,First,#,excelToTable,id,0,254;" +
          "log_index \"Log Index\" true true false 255 Long 0 0,First,#,excelToTable,log_index,0,254")

# Process: Export Table (Export Table) (conversion)
    arcpy.AddMessage("Exporting table and mapping fields")
    logging.info("Exporting table and mapping fields")
    MDD_exportTable = os.path.join(TempWS, "MDD_exportTable")
    arcpy.conversion.ExportTable(in_table=excelToTable, out_table=MDD_exportTable, field_mapping=fieldMap)

# Process: XY Table To Point (XY Table To Point) (management)
    arcpy.AddMessage("Displaying XY values and creating point feature class")
    logging.info("Displaying XY values and creating point feature class")
    MDD_tableToPoint = os.path.join(TempWS, "MDD_tableToPoint")
    arcpy.management.XYTableToPoint(in_table=MDD_exportTable, out_feature_class=MDD_tableToPoint, x_field="lon", y_field="lat", 
                                    coordinate_system="GEOGCS[\"GCS_WGS_1984\",DATUM[\"D_WGS_1984\",SPHEROID[\"WGS_1984\",6378137.0,298.257223563]],PRIMEM[\"Greenwich\",0.0],UNIT[\"Degree\",0.0174532925199433]];-400 -400 1000000000;-100000 10000;-100000 10000;8.98315284119521E-09;0.001;0.001;IsHighPrecision")

In the exported table, the latitude and longitude fields are still Text fields and I cannot display XY values using these fields. I also tried adding new fields in the field map and then calculating them, but iy seems as though the Table Export tool is ignoring the field mapping altogether because there are no new fields created to run the Calculate Field tool on. I noticed that in the last ArcPro update, the field mapping tool interface has changed, so I am wandering if something got messed up in the last update. I really don't want to have to write an extra 100 lines of code to do this, so if anyone has any insight please share. 🙂

[LaurencePerry]

ArcGIS 3.2.2

I believe I'm experiencing a related issue, except its presenting itself in the Export Features tool in model builder.

Export Features is the final tool in my chain, and I was using  it with field mappings after a join between two very similar feature classes to remove a large chunk of the extraneous fields. This was working up until our org updated to 3.2.2 yesterday.

Now I get an error 161044  and when I try to trouble shoot by removing fields from the field map (IE just exporting one field), the field map seemingly resets when I clear my intermediate data. The tool when run tries to export all fields and I continue to get the error.

[Robert_LeClair]

There is a related BUG-000162878 - "Export Features in ArcGIS Pro does not support editing field properties when exporting from ArcGIS Online hosted feature service" that I wonder is also an issue in this workflow too.  It shows it is fixed for the ArcGIS Pro 3.3 upcoming release. 

If you're able to uninstall the 3.2.2 update (workflow is Windows Start Button, go to Control Panel->Programs->Programs and features, then top right click "View installed updates" and then uninstall ArcGIS Pro 3.2 Patch 2 (3.2.2).  Does the error persist?

[FranklinAlexander]

The issue only exists for me when I try and execute a script from a script tool. I have been able to run the Export Table conversion tool successfully in model builder, Arc Pro, and from the Python IDLE. After further investigation, it appears that if I leave the field mapping in one continuous line and don't wrap each field to the next line like I did in my code sample, it works as expected and my fields are updated to the new types. 

I don't understand why this is only an issue when I execute my script from a script tool, when I can execute the exact same script from the IDLE and it works just fine. I just find the long continuous never ending line of code to be a little annoying because it's messy and I have to scroll, scroll, and scroll some more to see all of the mapping parameters!! It also makes the parameters difficult to edit if needed. 

To Robert,

Yes, I did get that update a couple of days ago, but OIT would have to uninstall it. I will just wait for the next update and see if it makes any difference.

[FranklinAlexander]

I was wrong about wrapping the fields, that is not the issue. The problem occurs when I use a variable in place of the hard file path for the input table. This is a huge problem, because the path comes from a parameter and must variable. Otherwise it defeats the whole purpose of the script tool, which is the portability! 

This:

"longitude \"Longitude\" true true false 255 Text 0 0,First,#,C:\\GIS_projects\\...\\...\\...\\Temp.gdb\\excelToTable,longitude,0,254;

Needs to be this:

"longitude \"Longitude\" true true false 255 Text 0 0,First,#,excelToTable,longitude,0,254;

If I print the variable excelToTable to the processing details window, they are exactly the same. Any ideas about how I can address this?

[IanRatcliffe]

I'm having the same problem. I need to explicitly state the file path in the field mapping to get the ExportTable script to work. Started having issues when I updated to 3.2. My scripts worked fine before that.

I have a variable (yesterday's date) in the filename that runs daily. I agree, this is a huge problem.

[FranklinAlexander · Accepted solution]

@IanRatcliffe 

Yes, that is the exact issue I was having, but only occurring when I tried to run my script from a script tool. I could run it from the Python IDLE without issue. Apparently, at least from my experience dealing with this, for some reason, the Table Export conversion tool field mappings could not see my file path to the input layer whenever I used a variable for the path. It doesn't make sense because I used the same variable in other geoprocessing tools and they ran just fine. Just some quirky behavior with this tool. I was able to get it working by setting my workspace environment to the gdb that contained the input layer for the field map. 

This works:

outPath = arcpy.GetParameterAsText(0) 
TempDB_name = "Temp.gdb"
TempWS = os.path.join(outPath, TempDB_name)

with arcpy.EnvManager(scratchWorkspace=TempWS, workspace=TempWS): # setting the env made the difference
  excelToTable = "excelToTable"  # This is my input table
  fm = ("list_name \"List Name\" true true false 100 Text 0 0,First,#,excelToTable,list_name,0,254;" +
            "item_id \"Item ID\" true true false 10 Text 0 0,First,#,excelToTable,item_id,0,254;" +
            "itemname \"Item Name\" true true false 100 Text 0 0,First,#,excelToTable,itemname,0,254;" +
            "material \"Material\" true true false 50 Text 0 0,First,#,excelToTable,material,0,254;" +
            "quantity \"Quantity\" true true false 255 Long 0 0,First,#,excelToTable,quantity,0,254;" +
            "description \"Description\" true true false 255 Text 0 0,First,#,excelToTable,description,0,254;" +
            "latitude \"Latitude\" true true false 8 Double 14 16,First,#,excelToTable,latitude,0,254;" +
            "longitude \"Longitude\" true true false 8 Double 14 16,First,#,excelToTable,longitude,0,254;" +
            "altitude \"Altitude\" true true false 255 Float 0 0,First,#,excelToTable,altitude,0,254;" +
            "radius \"Radius\" true true false 255 Float 0 0,First,#,excelToTable,radius,0,254;" +
            "location \"Location\" true true false 100 Text 0 0,First,#,excelToTable,location,0,254;" +
            "timestamp \"Timestamp\" true true false 20 Text 0 0,First,#,excelToTable,timestamp,0,254;" +
            "dt \"Datetime\" true true false 255 Date 0 0,First,#,excelToTable,dt,0,254;" +
            "project_name \"Project Name\" true true false 50 Text 0 0,First,#,excelToTable,project_name,0,254")

  MDD_exportTable = "MDD_exportTable"
  arcpy.conversion.ExportTable(in_table=excelToTable, out_table=MDD_exportTable, field_mapping=fm)  

[Dankotaru]

Same. I have a python toolbox script that exported an input Excel table to a geodatabase table and used the field mapping parameter to modify 14 input fields to have the desired output table schema. It worked great. After the last Pro update, the field mapping parameter no longer functions, and therefore none of the follow-up functions in the script run correctly because the field names and types weren't changed by the field map parameter.

Using EnvManager to set the workspace environment didn't fix the issue.

[DarylHochhalter]

Yes, there is a bug but don't think they have classified it as such yet. I have trouble with ExportFeatures in a script that ran fine in 3.1 and still runs from our enterprise 11.0 server machine. I tried all kinds of reformatting for the string in the fieldmappings but nothing worked, then discovered that even using a single line string for fieldmappings in the ExportFeatures funtion still didn't work, I found another post where the issue seems to be with layers that have a joined feature class or table.

https://community.esri.com/t5/arcgis-pro-questions/export-features-with-table-join-field-mapping-v3-...

[Dankotaru]

Update: I found a working solution

I had the same problem for multiple python toolbox tools that ran perfectly fine until the latest update, after which the field mapping parameter for both the Export Table and Export Feature functions were ignored in their outputs.

My solution has been to pass an f-string of the input table or layer variable into the field map for each field's source.

For example, this little portion of the original script exporting from a table called 'CDOT_Table' used to work fine (fyi: the input in this example is always called 'CDOT_Table', hence the surprise that field mapping stopped working). Now the field mapping fails to map the 'LocID' field to 'Source_ID' field in the output:

arcpy.conversion.ExportTable(InputTable, OutputTable, field_mapping="Source_ID \"Source_ID\" true true false 255 Text 0 0,First,#,CDOT_Table,LocID,0,255")

The output table had the data, but it was still in a field called 'LocID', which caused issues for other operations later in the script. The field mapping also failed to re-order the output fields. And fields that were converted types, like from integers to strings, would get added on rather than changed in the output. For input tables or features with multiple field mapping changes, this was a big problem. I fixed it by passing in the input variable for each field source as an f-string like this:

arcpy.conversion.ExportTable(InputTable, OutputLayer, field_mapping="Source_ID \"Source_ID\" true true false 255 Text 0 0,First,#,"f"{InputTable}"",LocID,0,255")

So far, this solution has worked in multiple scripts.

For tables or features that have joins, I have found this to work by simply simply exporting it first to memory and then export it again from memory to your output. That eliminates the join when exporting to your output, and having to define the source as part of each input field's name.

Hope this helps others who stumble across this issue.