Import CSV data into SQL database

Question

I have a table with the following fields:

Name (varchar)
Ranking (int)
Age (int)
Favourite Court (varchar)

and a CSV file like this

name;age;current_ranking;favourite_court
sample1;22;5;Hard
sample2;21;6;Clay

I try to import it with MySQL using: LOAD DATA LOCAL INFILE 'c:/players.csv' INTO TABLE players FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n'; or LOAD DATA LOCAL INFILE 'c:/players.csv' INTO TABLE players FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' (name,age,current_ranking,favourite_court);, with or without file header (that is name;age...). It does not work, fields are messed up. Why?

Answer 1

You need to name the columns from your table, not the columns as headed in the file:

LOAD DATA LOCAL INFILE 'c:/players.csv' INTO TABLE players
FIELDS TERMINATED BY ';'
IGNORE 1 LINES
(Name, Age, Ranking, `Favourite Court`)

Also, as noted under LOAD DATA INFILE Syntax:

Note 

If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use \r as a line terminator when writing files. To read such files, use LINES TERMINATED BY '\r'