MySQL workbench not importing all rows from csv

Question

I am trying to import a CSV file to a new table in MySql. The file has 1 million rows but MySql is only importing 847 rows.

1. I tried saving the CSV file and importing various formats, utf-8, windows-1205, etc.
2. The CSV file has an INDEX column with sequential numbers that can be used as a primary key.
3. There are no invalid characters, such as commas.
4. I copied the CSV file and deleted the first 847 and imported it again, and it imported the next 26 rows. This shows that there is nothing wrong with the data and it could have imported it originally.

Why won't the MySql Workbench import all million rows?

Answer 1

Instead of using PhpMyAdmin or MySQL Workbench, I recommend you to use command-line to import csv to MySQL, especially if it's a large file. It allows you to read data from text file or import data into database very fast, read this.

Use this command:

LOAD DATA LOCAL INFILE '/path/to/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';
IGNORE 1 ROWS;

• Add LOCAL if the file is located on local machine. Reference: link.
• Change table_name to destination table name.
• FIELDS TERMINATED BY By default, CSV uses comma to identify individual data value.
• ENCLOSED BY means double quote mark " " surround values.
• LINES TERMINATED BY specifies line-break.
• IGNORE 1 ROWS This command tells MySQL to skip first row. We want to use this if CSV contain column-header and we want to ignore it being imported to our table.

For further details, you can check the manual.

Answer 2

UPDATE:

I tried importing with MSSQL (Using SSMS) and that not only gave me an error but told me what the problem was! I wasn't allocating enough space for the char fields as some values had long strings of text. All I did in SSMS was change it to VARCHAR(max) and SSMS imported all million rows. This might have been a solution for MySql but since MySQL Workbench didn't tell me what the exact problem was, I already uninstalled it and will continue with SSMS and MSSQL.

Answer 3

If any of the columns is of datatype double/float and has empty cells replace those with NULL or NA. Try importing the CSV. This worked for me.

Answer 4

Do not use command line, it need set up environment parameter to allow you import from local csv file.

Instead use import wizard is much easier for me.

However I only import first 100 lines.

I solve it by choose latin1 instead of default utf-8

https://bugs.mysql.com/bug.php?id=89044