CODEWITHSUNDEEP
mysqlload-data-infile
Anupam
Anupam

Reputation: 538

mysql not reading the whole file, skipping rows instead while using `load data local`

In one of my assignment problem, I need to read an input file that has 131262 lines.

Following is my code for reading the input:

create table batsman_scored(
    id int(10) unsigned auto_increment primary key,
    match_id int(10),
    over_id int(10),
    ball_id int(10),
    runs_scored int(10),
    innings_no int(10)
);
load data local infile "data/batsman_scored.csv" 
into table batsman_scored
fields terminated by ','
lines terminated by '\n'
ignore 2 rows
;

The problem is, for some reason, MySQL is not reading the whole file, instead, it is getting terminated after few lines. I checked the input files, and there is nothing suspicious there. Mysql output:

Query OK, 568 rows affected, 65535 warnings (0.50 sec)
Records: 131260  Deleted: 0  Skipped: 130692  Warnings: 261952

Can someone tell, why it's skipping so many rows? When does MySQL skip rows?

I checked that MySQL can store a lot more than 568.

CSV data

Upvotes: 0

Views: 461

Answers (2)

Akina
Akina

Reputation: 42834

Use

create table batsman_scored(
    id int(10) unsigned auto_increment primary key,
    match_id int(10),
    over_id int(10),
    ball_id int(10),
    runs_scored int(10),
    innings_no int(10)
);

load data local infile "data/batsman_scored.csv" 
into table batsman_scored
fields terminated by ','
lines terminated by '\n'
ignore 2 rows
(match_id, over_id, ball_id, runs_scored, innings_no)
;

The values for id column are not stored in CSV (the table contains 6 columns whereas CSV contains 5 values per row), and they wouldn'd be loaded from the file, they are generated by autoincrement during importing. So the columns list for loading must be specified, w/o id column.

Upvotes: 1

Anupam
Anupam

Reputation: 538

I am still waiting for an answer here, but commenting the auto_increment line:

create table batsman_scored(
    // id int(10) unsigned auto_increment primary key,
    match_id int(10),
    over_id int(10),
    ball_id int(10),
    runs_scored int(10),
    innings_no int(10)
);
load data local infile "data/batsman_scored.csv" 
into table batsman_scored
fields terminated by ','
lines terminated by '\n'
ignore 2 rows
;

Upvotes: 0

Related Questions