CSV imports with no errors but missing rows

Issue

I am loading a csv file into an empty table with success and no errors. When comparing the number of original rows (from viewing .csv in external application and from the Output Response) with the number of rows in my resulting table (from Table Inspector) seems like not all rows are importing. Table Inspector reports that the table has 1,416,824 rows while the original csv has 1,419,910 rows. There should be no replicated primary keys in the data set though it should have error’d out on those lines in my mind if that were the case.

Table structure:

CREATE TABLE `table1` (
  `pkfield` varchar(10) NOT NULL,
  `field1` varchar(3) DEFAULT NULL,
  `field2` varchar(1) DEFAULT NULL,
  `field3` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pkfield`),
  UNIQUE KEY `pkfield_UNIQUE` (`pkfield`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Import command:

LOAD DATA INFILE 'c:/table1.csv'
INTO TABLE myschema.table1
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

MySQL Workbench Response:

1419910 row(s) affected Records: 1419910  Deleted: 0  Skipped: 0  Warnings: 0

Sample from csv file (data mocked up):

6623950258,XYZ,A,B
5377103432,XYZ,A,B
9131144416,XYZ,A,A
1326703267,XYZ,A,B
7847786312,XYZ,A,B
1119927042,XYZ,B,B
4144055385,CDE,A,B
4563489252,XYZ,A,B
5733611912,XYZ,A,B
3309418377,XYZ,A,B
6928148128,XYZ,A,B
1152657670,XYZ,A,B
8143082292,CDE,A,B
9373340750,XYZ,A,A
3318949288,XYZ,A,B
1166427684,XYZ,A,B
5062296807,XYZ,B,A
4624323293,XYZ,A,B
3088992643,CDE,A,B
6477504847,XYZ,A,B

Any suggestions or explanations would be greatly appreciated. Kind regards.

Solution

Honestly, I’m myself not sure why the number of rows isn’t accurate after a fresh import of a table. I think the Table Inspector fetches the data from some statistics table and to my understanding that gets updated only when the table changes by more than 10 %. Perhaps this is the reason. However, the accurate amount of rows can always be fetched with traditional

select count(*) from myschema.table1;

As @nbayly told, this gives the expected result of 1419910 rows which matches the number LOAD DATA had reported.

Answered By – PerlDuck

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published