MySQL workbench not importing all rows from csv

user_6108740 0
Viewed 6656

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?

enter image description here

mysql
4 Answers

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.

user_61087400

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.

user_93879840

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.

user_142071230

enter image description here

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. enter image description here

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

default utf-8 not work, latin1 works

user_35215150
Related