Syntax Error - Trying to import csv file into mysql shell

Question

Full disclosure: I'm a complete beginner to MySQL.

I've spent over 8 hours looking at mysql documentation, stackoverflow responses, and youtube trying to figure out how to import a simple .csv file into mysql shell for the purposes of practicing SQL queries. I know there is an import button that I can use in the MySQL workbench, but this took my 2015 12-inch mac roughly 1 HR to upload a 33.65 mb file. I've read on stackoverflow that a command line prompt in the mysql shell will be considerably faster.

The .csv file in question is in this link: https://www.kaggle.com/PromptCloudHQ/toy-products-on-amazon

I've input the following queries in my mySQL shell:

create database amazon;

use amazon;

CREATE TABLE toys (
uniq_id VARCHAR(1000),
product_name VARCHAR(1000),
manufacturer VARCHAR(1000),
price VARCHAR(1000),
number_available_in_stock VARCHAR (1000),
number_of_reviews INT,
number_of_answered_questions INT,
average_review_rating VARCHAR(1000),
amazon_category_and_sub_category VARCHAR(1000),
customers_who_bought_this_item_also_bought VARCHAR(1000),
description VARCHAR(1000),
product_information VARCHAR(1000),
product_description VARCHAR(1000),
items_customers_buy_after_viewing_this_item VARCHAR(1000),
customer_questions_and_answers VARCHAR(1000),
customer_reviews VARCHAR(1000),
sellers VARCHAR(1000)
);

LOAD DATA LOCAL INFILE ‘/Users/Dave/Desktop/amazonsample.csv’ INTO TABLE toys
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
IGNORE 1 LINES
(uniq_id, product_name, manufacturer, price, number_available_in_stock, number_of_reviews, number_of_answered_questions, average_review_rating, amazon_category_and_sub_category, customers_who_bought_this_item_also_bought, description, product_information, product_description, items_customers_buy_after_viewing_this_item, customer_questions_and_answers, customer_reviews, sellers)
;

ERROR 1064 (42000): 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 '‘/Users/Dave/Desktop/amazonsample.csv’ INTO TABLE toys FIELDS TERMINAT' at line 1

I sincerely ask that you use simple language if you choose to respond! Thank you so much

You will find that "smart quotes" in your text editor is not a good idea if you're a programmer. ‘ is not the same character as '. — Bill Karwin
– Bill Karwin, Commented Jan 30, 2020 at 17:34
Thank you Bill Karwin and nbk! I had no idea there was any difference in these two characters — sofrustrated
– sofrustrated, Commented Jan 30, 2020 at 17:46
On a side note and since you are complaining about slowness, the use of VARCHAR(1000) seems terribly wasteful and inefficient. See for example this discussion: stackoverflow.com/questions/262238/…. In general bad design, lack of proper indexing are primary cause of poor DB performance. Reviewing your design and the data types used could make a huge difference. You might even save on your electricity bill ! — Kate
– Kate, Commented Jan 30, 2020 at 18:52

nbk · Accepted Answer · 2020-01-30 17:32:12Z

1

Your apostrophes are all wrong use only single

Like

LOAD DATA LOCAL INFILE '/Users/Dave/Desktop/amazonsample.csv' INTO TABLE toys
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(uniq_id, product_name, manufacturer, price, number_available_in_stock, number_of_reviews, number_of_answered_questions, average_review_rating, amazon_category_and_sub_category, customers_who_bought_this_item_also_bought, description, product_information, product_description, items_customers_buy_after_viewing_this_item, customer_questions_and_answers, customer_reviews, sellers)
;

answered Jan 30, 2020 at 17:32

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

9 Comments

sofrustrated Over a year ago

Sorry do you mean quotations? I have been using single quotations only. No double quotations anywhere, unless I'm mistaken

nbk Over a year ago

As Bill Karwin said ’ is not equal to '. They are different characters and the first causes problems. copy my code and you see the difference

sofrustrated Over a year ago

Yikes... I am very, very embarrassed. I've been using the wrong quote the whole time! The " ' " character would always work in Codecademy, so I didn't think to change its usage otherwise. Thank you so very much.

sofrustrated Over a year ago

The error is now ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides Is this a simple fix?

nbk Over a year ago

Yes i think so see stackoverflow.com/a/18935786/5193536 and please accept the answer

|

Collectives™ on Stack Overflow

Syntax Error - Trying to import csv file into mysql shell

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related