ArcGIS Pro incorrectly converting Time values to Dates during table import

[ToyotaT18]

I often import data from .csv files which contain time values stored in 24 hour format, e.g. 08:05:23. Using Table To Table, these fields usually import as Text type fields, no problem.

However I've just noticed - if it can, ArcGIS Pro will actually convert these fields to Date type fields, and convert the time to a date. For example, 08:05:23 becomes 08/05/2023, which clearly isn't correct.

It seems that this process is only prevented if at least one time value in the field cannot be interpreted as a date, e.g. 08:45:23 will prevent the conversion because there is no month '45'.

Does anyone know a way to prevent this? I would have thought text with format ##:##:## is better interpreted as a time value rather than a date value.

Thanks, Tim

[DanPatterson]

With TableToTable, you don't have to let the software do its best guess, you can control the output type, order etc under fields and properties

 

[ToyotaT18]

Hi Dan, thanks for the reply.

If I change the field type to Text, it still converts the time to a date, and then places it into the Text field.

(Also I then end up with only 4 of my 20+ fields? i.e. it only lists a few of the fields in that Field Map section?)

[curtvprice]

The way to override Microsoft's guesses is to edit a Schema.ini file

• Add an ASCII or text file table (ArcGIS Pro Help)
• Loading Numeric Data As Text in ArcGIS Pro (Temple Univ)

Example code in Schema.ini file (assuming the time is in the fourth column):

[MyExcellentFile.csv]
Col4="TimeValue" Text

This will force the fields you want to text.

Or -- you may want to try adding three dummy rows of data to the top of the field with "aaaaaaa" for the time fields you want read in as text, to basically fool Microsoft parser to read that field as text, then you can select and delete those rows after import with the Delete Rows tool.  Believe it or not, this is sometimes the easiest solution if it is a one-off file import!

[ToyotaT18]

I had tried adding a dummy row, changing the time value, importing, then deleting the row. It works but is a lot of steps.

I just tried the schema.ini option and it seems to work, the field remains as text and the time value isn't converted to a date. I'll try to incorporate this into my code, i.e. create the schema file, import the data, delete the schema file...

Thanks, Tim

[curtvprice]

If this works for you, please do mark my answer as solution to help others.

[jcarlson]

Peronally, if I have to import a dataset from elsewhere in Pro, I will create an empty table and define the schema ahead of time, then use append. I seem to have fewer with that approach.

More often, however, I will use Python for data loading, but I get that's not really what you're asking about.