Csv Import Converting Field To Datetime

[villagedb]

I'm trying to import a GTFS feed into a database. The problem I'm having is the Import External Data Wizard seems to be trying to automatically convert two of the fields into Datetime fields. So for example here is my table schema:

Field			 Type		







trip_id		   varchar(100)


arrival_time	  varchar(20)  


departure_time	varchar(20)  


stop_id		   int(11)	  


stop_sequence	 int(11)	  


pickup_type	   int(11)	  


drop_off_type	 int(11)	  


arrival_time_s	int(11)	  


departure_time_s  int(11)

Notice arrival_time and departure_time are of type varchar

I then setup to import a csv file, here is an example snippet

trip_id,arrival_time,departure_time,stop_id,stop_sequence,pickup_type,drop_off_type





2506484-HASTINFO-EAST2012-Weekday-01,23:10:00,23:10:00,85,1,0,0


2506484-HASTINFO-EAST2012-Weekday-01,23:13:00,23:13:00,31,2,0,0


2506484-HASTINFO-EAST2012-Weekday-01,23:17:00,23:17:00,10,3,0,0


2506484-HASTINFO-EAST2012-Weekday-01,23:22:00,23:22:00,11168,4,0,0


2506484-HASTINFO-EAST2012-Weekday-01,23:24:00,23:24:00,19064,5,0,0


2506484-HASTINFO-EAST2012-Weekday-01,23:26:00,23:26:00,19063,6,0,0


2506484-HASTINFO-EAST2012-Weekday-01,23:28:00,23:28:00,10813,7,0,0


2506484-HASTINFO-EAST2012-Weekday-01,23:30:00,23:30:00,3072,8,0,0


2506484-HASTINFO-EAST2012-Weekday-01,23:45:00,23:45:00,6303,9,0,0


2506484-HASTINFO-EAST2012-Weekday-01,23:47:00,23:47:00,10880,10,0,0


2506484-HASTINFO-EAST2012-Weekday-01,23:48:00,23:48:00,314512,11,0,0


2506484-HASTINFO-EAST2012-Weekday-01,23:51:00,23:51:00,10881,12,0,0


2506484-HASTINFO-EAST2012-Weekday-01,23:53:00,23:53:00,14122,13,0,0


2506484-HASTINFO-EAST2012-Weekday-01,23:55:00,23:55:00,11035,14,0,0


2506484-HASTINFO-EAST2012-Weekday-01,23:56:00,23:56:00,11025,15,0,0


2506484-HASTINFO-EAST2012-Weekday-01,23:57:00,23:57:00,11008,16,0,0


2506484-HASTINFO-EAST2012-Weekday-01,23:58:00,23:58:00,11043,17,0,0


2506484-HASTINFO-EAST2012-Weekday-01,24:00:00,24:00:00,318496,18,0,0


2506484-HASTINFO-EAST2012-Weekday-01,24:00:00,24:00:00,10885,19,0,0


2506484-HASTINFO-EAST2012-Weekday-01,24:03:00,24:03:00,310059,20,0,0

Now notice that some of the arrival_time & departure_times go above 23:59:59, that's why I want to store them as varchars and not any date/time format. (here is the google documentation on GTFS showing that these values are indeed valid for the feed: https://developers.g…op_times_fields).

So when I setup the import, in the mapping screen something in the importer seems to be auto-detecting the column as timestamp. I change it manually to the correct varchar type and then proceed with the import. Problem is something in the importer seems to still be converting the field to a timestamp before saving as the table looks like this after import:

trip_id							   arrival_time		 departure_time	   stop_id  stop_sequence  pickup_type  drop_off_type  arrival_time_s  departure_time_s  


2506484-HASTINFO-EAST2012-Weekday-01  1899-12-30 23:10:00  1899-12-30 23:10:00  85	   1			  0			0			  6837000		 6837000		  


2506484-HASTINFO-EAST2012-Weekday-01  1899-12-30 23:13:00  1899-12-30 23:13:00  31	   2			  0			0			  6837180		 6837180		  


2506484-HASTINFO-EAST2012-Weekday-01  1899-12-30 23:17:00  1899-12-30 23:17:00  10	   3			  0			0			  6837420		 6837420		  


2506484-HASTINFO-EAST2012-Weekday-01  1899-12-30 23:22:00  1899-12-30 23:22:00  11168	4			  0			0			  6837720		 6837720		  


2506484-HASTINFO-EAST2012-Weekday-01  1899-12-30 23:24:00  1899-12-30 23:24:00  19064	5			  0			0			  6837840		 6837840		  


2506484-HASTINFO-EAST2012-Weekday-01  1899-12-30 23:26:00  1899-12-30 23:26:00  19063	6			  0			0			  6837960		 6837960		  


2506484-HASTINFO-EAST2012-Weekday-01  1899-12-30 23:28:00  1899-12-30 23:28:00  10813	7			  0			0			  6838080		 6838080		  


2506484-HASTINFO-EAST2012-Weekday-01  1899-12-30 23:30:00  1899-12-30 23:30:00  3072	 8			  0			0			  6838200		 6838200		  


2506484-HASTINFO-EAST2012-Weekday-01  1899-12-30 23:45:00  1899-12-30 23:45:00  6303	 9			  0			0			  6839100		 6839100		  


2506484-HASTINFO-EAST2012-Weekday-01  1899-12-30 23:47:00  1899-12-30 23:47:00  10880	10			 0			0			  6839220		 6839220		  


2506484-HASTINFO-EAST2012-Weekday-01  1899-12-30 23:48:00  1899-12-30 23:48:00  314512   11			 0			0			  6839280		 6839280		  


2506484-HASTINFO-EAST2012-Weekday-01  1899-12-30 23:51:00  1899-12-30 23:51:00  10881	12			 0			0			  6839460		 6839460		  


2506484-HASTINFO-EAST2012-Weekday-01  1899-12-30 23:53:00  1899-12-30 23:53:00  14122	13			 0			0			  6839580		 6839580		  


2506484-HASTINFO-EAST2012-Weekday-01  1899-12-30 23:55:00  1899-12-30 23:55:00  11035	14			 0			0			  6839700		 6839700		  


2506484-HASTINFO-EAST2012-Weekday-01  1899-12-30 23:56:00  1899-12-30 23:56:00  11025	15			 0			0			  6839760		 6839760		  


2506484-HASTINFO-EAST2012-Weekday-01  1899-12-30 23:57:00  1899-12-30 23:57:00  11008	16			 0			0			  6839820		 6839820		  


2506484-HASTINFO-EAST2012-Weekday-01  1899-12-30 23:58:00  1899-12-30 23:58:00  11043	17			 0			0			  6839880		 6839880		  


2506484-HASTINFO-EAST2012-Weekday-01											318496   18			 0			0												


2506484-HASTINFO-EAST2012-Weekday-01											10885	19			 0			0												


2506484-HASTINFO-EAST2012-Weekday-01											310059   20			 0			0												

As you can hopefully see, 'valid' times are turned to a timestamp (with 1899-12-30) and inserted into the varchar field, while values greater than 23:59:59 are NULL. I can see the importer is trying to help and in 99% of cases it would be helpful, but with the GTFS spec this feature is breaking my values. Is there a way to disable it or trick it into not running?

[Jan.S]

You can use LOAD LOCAL to import values from a csv file into your MySQL table if the table structure already exists. The screenshot for the same is attached.

Import external data uses the Microsoft Text ODBC driver, and the driver returns the data as a timestamp, so there isn't anything that can be done from SQLyog to change that.

[Author]

Posts