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

Maybe this will help someone else.

But when importing .csv or .txt files that came from somewhere else and were not created in MySQL, any difference in time format will fail to be imported. I've been testing it and it loads any table that doesn't contain timedates, but the ones that do contain timedates fail.

It would be solved by changing the date format, by manually editing or writing a little script to change the column before importing it. With this changed, the import wizard doesn't seem to have a problem.

It feels almost the same as changing "," or "." in Excel.

Answer 2

1. First of all you have to clean your data, that may be the primary reason for the the data not to be imported from excel to MySql workbench.

2. Replace empty spaces to null from excel sheets, remove duplicates, format the right Data Type.

Data Cleaning

Replace Empty Cells with Null

3. MySql workbench accepts CSV type of files from excel for the current edition.
4. Use encoding uft8, This worked for me. uft8 encoding

Answer 3

Do not use the "Import Wizard" that does not work well. Better backup the database in SQL format and import it complete. Use MainMenu->Server->DataImport

https://www.youtube.com/watch?v=91hRSphTzdw

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

Answer 5

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 6

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 7

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.