Sometimes we may need to import data from a comma-separated file into a
MySQL database table.
The first step we must do is create the table with the necessary fields:
CREATE TABLE `qualifications` (
`idnumber` INT(9) NULL,
`name` VARCHAR(100),
`email` VARCHAR(100),
`qualification` INT(2),
`period` VARCHAR(50);
Once you have created the table, then proceed to load the corresponding
data in each field, for this we will use the following instruction
LOAD DATA LOW_PRIORITY LOCAL INFILE ‘~\\your_file.csv’ INTO TABLE
`alumni`.`qualifications` CHARACTER SET utf8 FIELDS TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY ‘,’ ESCAPED BY ‘,’ IGNORE 1 LINES (`idnumber`,
`name`, `email`, `qualification`, `period`);
The result of this load is a table like the following:
| idnumber | name | qualification | period | |
|---|---|---|---|---|
| 1515151 | Name 1 | [email protected] | 12 | 2022-1 |
| 1515152 | Name 2 | [email protected] | 13 | 2022-1 |
| 1515153 | Name 3 | [email protected] | 14 | 2022-1 |
| 1515154 | Name 4 | [email protected] | 15 | 2022-1 |
| 1515155 | Name 5 | [email protected] | 16 | 2022-1 |
Possible errors when uploading a CSV file
Field Separators and Delimiters
When loading a CSV data file it is important to consider the field
separators, the field completion and the escape of each line.
In our example, the comma was used as a field delimiter and escape.
The encoding of the file
The encoding of both the file and the Database must also be considered
(in this case, it was done with UTF8).
If the encodings do not match, there may be errors.
The location of the CSV file
Depending on the operating system, the location of the CSV file is of
importance, as the file location string could be very long, and thus be
incompatible with MySQL.
The data type
The data type of the fields declared in the Database must match the data
types in the CSV file, otherwise errors may occur when loading the data.