CODEWITHSUNDEEP
mysqlcsv
drjrm3
drjrm3

Reputation: 4718

Why is my first column being skipped on input?

I have a .csv file which I want to load into a table via MySQL. If I have my SQL query as:

CREATE TABLE table_name (
id INT NOT NULL AUTO_INCREMENT,
playerID VARCHAR(40),
class VARCHAR(40),
system VARCHAR(40),
lvl INT,
build VARCHAR(40),
...
PRIMARY KEY (id)
);

Then my table consists of

id playerID class system lvl build ...

1 monk ps4 70 0 77 ...

whereas if I include the id at the end of the CREATE TABLE query:

CREATE TABLE table_name (
playerID VARCHAR(40),
class VARCHAR(40),
system VARCHAR(40),
lvl INT,
build VARCHAR(40),
...
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
);

then the id field correctly auto-increments and the playerID, class, etc. are correctly entered:

playerID class system lvl build ...

Player1 monk ps4 70 0 ...

In either case, the way I am reading in the data is:

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

So I am assuming the problem is that I am reading the data in with id being sent to the first argument in the .csv file, but how do I edit my queries to input an auto-incrementing Primary Key as the first column in the table?

EDIT:

The basic syntax of the .csv file is:

playerID, class, system, lvl, build, ...

player1, monk, ps4, 70, dual, ...

Upvotes: 0

Views: 43

Answers (1)

Paul Dixon
Paul Dixon

Reputation: 300965

You can specify a column list to handle cases when the CSV doesn't align with the table schema, for example

LOAD DATA LOCAL INFILE '/path/to/data.csv'
INTO TABLE table_name (playerID, class, system, lvl, build)
FIELDS TERMINATED  BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

See http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Upvotes: 1

Related Questions