Title: MySQL-MySQL skips some rows when import .csv file
Tags: mysql
Question: I wanted to import a .csv file with ~14k rows to MySQL. However I only got a table in MySQL with about 13k rows. I checked and found out that MySQL skips some rows in the middle of my .csv file.
I used LOAD DATA INFILE and I really cannot understand why MySQL skips those rows. Really appreciate if someone could help me with this.
Here is my query:
LOAD DATA LOCAL INFILE 'd:/Data/Tanbinh - gui Tuan/hm2.csv'
INTO TABLE q.hmm
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES;
There is no warning message at all
Comment: Could you show full query to load data? What was printed by `show warnings;`?

Comment: Here is my query: LOAD DATA LOCAL INFILE 'd:/Data/Tanbinh - gui Tuan/hm2.csv' INTO TABLE q.hmm FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES; There is no warning seancastaneda@example.org.


Here is another answer: I assume, that the difference in numbers of rows caused by unique key duplicates.
From the reference:
With LOAD DATA LOCAL INFILE, data-interpretation and duplicate-key
errors become warnings and the operation continues
Actually there are no warnings produced on duplicates. To check wich rows were skipped, you can load data into another similar table without unique keys and compare those tables.
Comment for this answer: I can't reproduce such behaviour. If there are duplicates on unique keys, then there are no warnings, but it may be checked by loading into table without uniques. If there are incorrect lines (including empty lines), then there are warnings, which can be checked by `show warnings;` after `load data` execution.
Comment for this answer: Hi Aleksandr, I already found out which rows were skipped. About unique key duplicates, I check the unique-key column in Excel and there is no duplicate value

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 2, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2015:00:04/29/15', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<mysql>', 'Title': 'MySQL-MySQL skips some rows when import .csv file', 'ViewCount': 1690, 'id': 29934910, 'user_AboutMe': None, 'user_AccountId': 4503342.0, 'user_DisplayName': 'TDo', 'user_Reputation': 666.0, 'user_Views': 54.0, 'user_id': 3661376.0}}