Unsupported Screen Size: The viewport size is too small for the theme to render properly.

Csv Import Converting Field To Datetime

forums forums SQLyog SQLyog: Bugs / Feature Requests Csv Import Converting Field To Datetime

Tagged: , ,

This topic contains 0 replies, has 0 voices, and was last updated by  Jan.S 6 years, 1 month ago.

  • Author
    Posts
  • #12964

    villagedb
    Member

    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?

  • #34272

    Jan.S
    Member

    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.

You must be logged in to reply to this topic.