topic Re: Table to Table from CSV - inconsistent data type in Python Questions

Table to Table from CSV - inconsistent data type

AshleyDePottey — Wed, 05 Nov 2014 17:35:05 GMT

The first time I do a TableToTable conversion from a CSV into memory, it creates my PIN field as a LONG, but the second time, it creates it as TEXT. This happens consistently whenever I move my input CSV into a new folder and causes my second TableToTable (which uses a where clause) to fail. Any ideas why this is happening?

csv = r'C:\temp\PostalAddress.csv'
out_workspace = r'C:\temp\temp.gdb'

# add OID field
print 'creating a copy of table with OID field'
arcpy.TableToTable_conversion(csv, 'in_memory', 'unfiltered')
addr_csv = os.path.join('in_memory', 'unfiltered')

# select only status=active and PIN <> 99*
print 'filtering table for active rows'
where = "UPPER(status) = 'ACTIVE' and PIN NOT LIKE '99%'"
arcpy.TableToTable_conversion(addr_csv, out_workspace, 'addr', where)

Thanks,
Ashley

Re: Table to Table from CSV - inconsistent data type

curtvprice — Fri, 07 Nov 2014 06:15:53 GMT

The only way you can have full control over data types of fields from text tables is to write field control records to the schema.ini file in the folder. The schema.ini file is generated the first time you do a table read in that workspace - the data types are guessed based on the input data. It's nasty, but futzing with the schema.ini is the only way (unless you read the data some other way than directly as an ArcGIS table. Fortunately Python is really great at manipulating text so you can do this using python text file operations.

One workaround you may want to try is to delete the schema.ini file after the first TableToTable conversion - don't know if that will help though. Probably better to write a schema.ini file before you run Table To Table.

Desktop 10.2 Help: Adding an ASCII or text file table > Overriding how text files are formatted

Re: Table to Table from CSV - inconsistent data type

AshleyDePottey — Fri, 07 Nov 2014 14:44:42 GMT

Thanks Curtis! I hadn't noticed that the schema.ini file was getting created after my first run, and creating my own solved my problem.