I don't know how sophisticated are your QA/QC operations you run on the input
.csv files (what if user has added a row with a string in the column that you expect to be an integer?). If you target ArcGIS 10.4+, I may recommend using
pandas Python package to read the
.csv file and cast the columns into the proper types so you don't have deal with the cast errors yourself. When you are done, you can always export the produced data frame into an output
.csv into a user temp folder using the
If you are only interested in getting your columns right (without actually checking whether all rows would qualify), I suggest converting the
.csv file into an
in_memory layer first.
Say you have a
.csv file with the rows:
ID FieldInt FieldStr FieldDate
1 10 Value1 2018-02-12
2 20 Value2 2018-02-14
3a 20a Value3 2018-02-16
You would like all the fields to be of string types. If you would convert this
.csv into a table using the
arcpy.TableToTable_conversion(, you would get:
As you can see, ArcGIS decided to cast the
FieldInt fields into
Integer fields and values that could not have been casted are now just
You will not be able to restore the
null values, but you can still move the data left into the columns of right type. You create a new table with the fields found in the
.csv file using the data types you need:
- Create an empty geodatabase table.
- Add fields with the necessary types (using
- Convert source
.csv into a temp table
- Append with the
arcpy.Append_management(src, target) moving the data from the temp table into a production one.
Even if you would have a field map in place, the situation I'm describing above would make it impossible to import all the data right. Try yourself to run the
TableToTable tool in ArcMap UI.
arcpy.TableToTable_conversion(in_rows="C:/GIS/Temp/data.csv", out_path="C:/GIS/Temp/ArcGISHomeFolder/sample.gdb", out_name="trick1", where_clause="", field_mapping='ID "ID" true true false 4 Text 0 0 ,First,#,C:\GIS\Temp\data.csv,ID,-1,-1;FieldInt "FieldInt" true true false 4 Text 0 0 ,First,#,C:\GIS\Temp\data.csv,FieldInt,-1,-1;FieldStr "FieldStr" true true false 8000 Text 0 0 ,First,#,C:\GIS\Temp\data.csv,FieldStr,-1,-1;FieldDate "FieldDate" true true false 20 Text 0 0 ,First,#,C:\GIS\Temp\data.csv,FieldDate,-1,-1', config_keyword="")
Even after you've specified all the fields to be of
Text type, the last row is not loaded (only
PS. A dirty workaround I've seen in someone's code was to put a top row in the
.csv file with values of the type one wanted to have and then delete the row after the data import was done. This could be done using Python's
csv module and then using
arcpy.da.UpdateCursor to delete the first row.