Search code examples
mysqlcsvcshload-data-infile

Problems creating Tables from command line Mysql

When I type in the code into the Terminal it creates the Database but doesn't create the Table. When I type in use "locations" then "Show TABLES" it tells me that no Tables were created. This code should create a Database and the Table then import the csv file into the database "locations"

Solution

  • Here is a full answer, based off of @Honeyboy's comment.

    In addition to needing to USE locations; there are some other things to fix:

    • Shell quoting wasn't really quite right. For one thing, your existing code doesn't account for the line breaks -- it needs line continuation escapes to run as written.
    • The name of the table changed between the CREATE TABLE statement and the LOAD DATA statement
    • The LOAD DATA statement didn't ignore the header row of your tab delimited file.
    mysql -u root --password=password -e \
'CREATE DATABASE locations; \
USE locations; \
CREATE TABLE location_T (number1 INT NOT NULL, \
number2 INT NOT NULL, \
number3 INT NOT NULL, \
names VARCHAR(100) NOT NULL,PRIMARY KEY (number1)); \
LOAD DATA LOCAL INFILE "locations.csv" \
INTO TABLE location_T FIELDS TERMINATED by "\t" \
ENCLOSED BY "\"" LINES TERMINATED BY "\n" IGNORE 1 LINES \
(number1,number2,number3,names)'