Importing data from a file into a MySQL database

In this example we will focus on the exercise importing existing data into a MySQL database and the various problems you may run into.

First, let’s look at the data we wish to import, it is the password file for the modern stone age family

$ cat data.csv
fred,5sxfa3s\
wilma,dZc9(1a>
pebbles,a4gD3i"[
dino,6@pj&NrV

Now, we need to create the table which we will import the data to.

mysql> use bedrock;
Database changed
mysql> create table passwd (
      -> username varchar(8),
      -> password varchar(8) );
Query OK, 0 rows affected (0.01 sec)
mysql>

Great, now let’s proceed to importing our data

$ mysql -u root -p
Enter password:
mysql> use bedrock;
Database changed
mysql> LOAD DATA LOCAL INFILE './data.csv'
      -> INTO TABLE passwd
      -> FIELDS TERMINATED BY ','
      -> LINES TERMINATED BY '\n';
ERROR 1148 (42000): The used command is not allowed with this MySQL version
mysql>

If you get this error, then try starting mysql as follows

$ mysql --local-infile=1 -u root -p
Enter password:
mysql> use bedrock;
Database changed
mysql> LOAD DATA LOCAL INFILE './data.csv'
      -> INTO TABLE passwd
      -> FIELDS TERMINATED BY ','
      -> LINES TERMINATED BY '\n';
Query OK, 3 rows affected, 2 warnings (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 2

Okay, we’re getting close, but we are getting warnings, and only three records were created, we had four entries in data.csv, let’s investigate further.

mysql> select * from passwd;
+----------+----------+
| username | password |
+----------+----------+
| fred     | 5sxfa3s
 |
| pebbles  | a4gD3i"[ |
| dino     | 6@pj&NrV |
+----------+----------+
3 rows in set (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------------------+
| Level   | Code | Message                                                                   |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1265 | Data truncated for column 'password' at row 1                             |
| Warning | 1262 | Row 1 was truncated it contained more data than there were input columns |
+---------+------+---------------------------------------------------------------------------+
2 rows in set (0.00 sec)

The warnings indicated something wrong with the first row, the select shows fred’s password is missing the trailing \, let’s try to escape this.

$ sed -e 's/\\/\\\\/' data.csv > data.csv.new
$ cat data.csv.new
fred,5sxfa3s\\
wilma,dZc9(1a>
pebbles,a4gD3i"[
dino,6@pj&NrV

Okay, good, now lets retry the import. First we will delete all the data in the table to avoid any conflicts

mysql> delete from passwd where 1>0;
Query OK, 3 rows affected (0.01 sec)

mysql> LOAD DATA LOCAL INFILE './data.csv.new'
      -> INTO TABLE passwd
      -> FIELDS TERMINATED BY ','
      -> LINES TERMINATED BY '\n';
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

Great… let’s view the resulting table

mysql> select * from passwd;
+----------+----------+
| username | password |
+----------+----------+
| fred     | 5sxfa3s\ |
| wilma    | dZc9(1a> |
| pebbles  | a4gD3i"[ |
| dino     | 6@pj&NrV |
+----------+----------+
4 rows in set (0.00 sec)