1

I'm trying to load GTFS files (comma separated text files) to a MySQL database. MySQL Workbench Import Wizard doesn't work. I copied agency.txt file to agency.csv and select it with the wizard. Went through all the steps; everything was a green checkmark but It said '0 records imported' at the end. No errors, no warnings. I use mysql shell. This is my file:

>>head -2 agency.csv
agency_name,agency_url,agency_timezone,agency_lang,agency_phone,agency_fare_url
Chicago Transit Authority,http://transitchicago.com,America/Chicago,en,1-888-YOURCTA,http://www.transitchicago.com/travel_information/fares/default.aspx

This is my sql script to create a table and load the data:

CREATE TABLE agency (
    agency_name VARCHAR(255)  character set utf8,
    agency_id VARCHAR(255)  character set utf8,
    agency_url VARCHAR(255)  character set utf8,
    agency_timezone VARCHAR(255)  character set utf8
);
LOAD DATA LOCAL INFILE '/cta/agency.csv' INTO TABLE agency
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(agency_name,@vagency_id,agency_url,agency_timezone)
SET agency_id = IF(agency_id IS NULL, agency_name, @vagency_id);

and this is what is loaded to the table:

mysql> select * from agency;
+---------------------------+---------------------------+----------------------------+-----------------+
| agency_name               | agency_id                 | agency_url                 | agency_timezone |
+---------------------------+---------------------------+----------------------------+-----------------+
| Chicago Transit Authority | Chicago Transit Authority | America/Chicago            | en              |

Note that Agency_url in the table is America/Chicago (timezone not a url).

I am not very experienced in sql technologies but I think it shouldn't be so hard to put data to a database. Can someone tell me 1. Why is this happening? 2. How to fix it?

melis
  • 1,145
  • 2
  • 13
  • 30

1 Answers1

0

You are referring to the agency_id column in the SET clause before the actual value has been set. Hence, it is NULL causing the second column to be assigned the value of the first column. Change your code to this:

SET agency_id = IF(@vagency_id IS NULL, agency_name, @vagency_id);
                   ^^^ DON'T use agency_id here

Full code:

LOAD DATA LOCAL INFILE '/cta/agency.csv' INTO TABLE agency
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(agency_name, @vagency_id, agency_url, agency_timezone)
SET agency_id = IF(@vagency_id IS NULL, agency_name, @vagency_id);
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Hey @Tim thanks for the answer. Your suggested query is not doing what I want though. This time agency url is in the agency_id field. – melis Nov 10 '16 at 16:55
  • BTW, agency_id is an optional field in GTFS. I want to use the agency_id field as a foreign key between agency and route tables. That's why I'm trying to set an agency_id. – melis Nov 10 '16 at 16:57
  • `This time agency url is in the agency_id field.` ... I don't see how this is possible. Are you sure you posted the actual query and data? – Tim Biegeleisen Nov 10 '16 at 16:59
  • Yes. I can't paste the select query result here, it is too long. But yes. I'm sure i posted the correct query and correct data. Only thing I changed in my query before I posted is that I deleted the beginning of the file directory. That's it – melis Nov 10 '16 at 17:00