Table to Table from CSV - inconsistent data type

[AshleyDePottey]

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

[curtvprice · Accepted solution]

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

[AshleyDePottey]

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.