Loading a .csv file into a MySQL database using a script

Question

I am using the Ubuntu 10.04 LTS command line to run a mySQL 5.1.61 script that ought to take a .csv file and use its contents to fill a table I created earlier in the script.

Here's what's in the script (the csv has three columns, first name, last name, and club):

-- Import club roster from csv.
-- Create a table to store club data.
CREATE TABLE club_roster (
    first_name VARCHAR(32),
    last_name VARCHAR(32),
    club VARCHAR(32)
)

followed by:

-- Put information from csv into table.
LOAD DATA LOCAL INFILE '/complete/path/to/file.csv'
    INTO TABLE club_roster
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES;

When I attempt to run it, I get this error:

ERROR 1064 (42000) at line 17: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LOAD DATA LOCAL INFILE '/complete/path/to/file.csv' INTO TABL' at line 8

What about my syntax is incorrect? I have tried every combination of paths to the file, including with single quotes, without single quotes, with the entire path, with just the file, with the file ending, without the file ending, and combinations of these.

Any ideas? Thank you for the help!

EDIT: I am using the command

mysql < 'scriptFileName' -u root -p

and then entering my password to run the script. Would the command matter?

Can you show me what is in the first 16 lines of your script? Your syntax looks correct, but whatever is being executed before that seems to be failing. — Miguel Coder
– Miguel Coder, Commented Jun 12, 2017 at 16:39

Miguel Coder · Accepted Answer · 2017-06-12 16:40:42Z

1

You should terminate your create table command with a semi-colon. Like so:

CREATE TABLE club_roster (
first_name VARCHAR(32),
last_name VARCHAR(32),
club VARCHAR(32)
);

answered Jun 12, 2017 at 16:40

Miguel Coder

1,95721 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Loading a .csv file into a MySQL database using a script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related