2

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

nbk
  • 45,398
  • 8
  • 30
  • 47
sofrustrated
  • 1,051
  • 1
  • 6
  • 4
  • 1
    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 Jan 30 '20 at 17:34
  • Thank you Bill Karwin and nbk! I had no idea there was any difference in these two characters – sofrustrated Jan 30 '20 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: https://stackoverflow.com/questions/262238/are-there-disadvantages-to-using-a-generic-varchar255-for-all-text-based-field. 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 Jan 30 '20 at 18:52

1 Answers1

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)
;
nbk
  • 45,398
  • 8
  • 30
  • 47
  • Sorry do you mean quotations? I have been using single quotations only. No double quotations anywhere, unless I'm mistaken – sofrustrated Jan 30 '20 at 17:38
  • 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 – nbk Jan 30 '20 at 17:40
  • 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 Jan 30 '20 at 17:44
  • 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? – sofrustrated Jan 30 '20 at 17:45
  • Yes i think so see https://stackoverflow.com/a/18935786/5193536 and please accept the answer – nbk Jan 30 '20 at 17:51
  • I think this answer is more specific to the error loading local data: https://stackoverflow.com/q/10762239/20860 – Bill Karwin Jan 30 '20 at 17:53
  • And I agree, you should accept @nbk's answer to this question, since the issue about quotes was resolved. – Bill Karwin Jan 30 '20 at 17:54
  • Thank you so much to the both of you! Wasn't aware that an answer could be "accepted"; will do so now. – sofrustrated Jan 30 '20 at 18:34
  • Bill, I read through the upvoted comments from your link, but I don't know how to interpret that information such that I can do anything with it. I've tried https://stackoverflow.com/a/51532736/12810393 answer because it's a simple copy and text, but the instructions in my main answer go above my head. Could you please help out once more? – sofrustrated Jan 30 '20 at 18:50