topic Re: ArcGIS Pro incorrectly converting Time values to Dates during table import in ArcGIS Pro Questions

ArcGIS Pro incorrectly converting Time values to Dates during table import

ToyotaT18 — Fri, 27 Jan 2023 00:36:28 GMT

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

Re: ArcGIS Pro incorrectly converting Time values to Dates during table import

DanPatterson — Fri, 27 Jan 2023 01:37:00 GMT

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

Re: ArcGIS Pro incorrectly converting Time values to Dates during table import

ToyotaT18 — Fri, 27 Jan 2023 02:08:36 GMT

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?)

Re: ArcGIS Pro incorrectly converting Time values to Dates during table import

curtvprice — Fri, 27 Jan 2023 05:06:09 GMT

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

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!

Re: ArcGIS Pro incorrectly converting Time values to Dates during table import

jcarlson — Fri, 27 Jan 2023 13:46:53 GMT

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.

Re: ArcGIS Pro incorrectly converting Time values to Dates during table import

ToyotaT18 — Sun, 29 Jan 2023 21:32:21 GMT

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

Re: ArcGIS Pro incorrectly converting Time values to Dates during table import

curtvprice — Tue, 31 Jan 2023 00:16:16 GMT

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