Lubos Rendek

The only solution is determination.

Import Data From CSV File to MySQL With Bash Script

| Comments

Here is what you need to this simple demonstration on how to import data from CSV file to MySQL database. Fist we need a comma separated value file like:

1
2
3
4
5
6
7
8
9
10
11
$ cat input.csv 
54,68,74
54,67,77
54,67,78
54,67,76
54,67,79
54,67,74
54,67,75
54,67,68
54,67,63
54,65,63

Next, you need MySQL database with at least one table. Our database is called “mydata” and contains a single table named “cost”:

1
2
3
4
5
6
7
8
9
10
mysql> describe cost;
+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| id      | int(11) | NO   | PRI | NULL    | auto_increment |
| column1 | int(11) | NO   |     | NULL    |                |
| column2 | int(11) | NO   |     | NULL    |                |
| column3 | int(11) | NO   |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Lastly, we need a bash script to import our comma separated CSV file into our table. The file can look like the one below:

1
2
3
4
5
6
7
8
9
$ cat import-SQL.sh 
#!/bin/bash

IFS=,
while read column1 column2 column3
      do
        echo "INSERT INTO cost (column1,column2,column3) VALUES ('$column1', '$column2', '$column3');"

done < input.csv | mysql -u myusername -p mypassword mydata;

Most of the stuff above is self explanatory except IFS variable. IFS or Internal field separator is a bash buildin bash variable which is by default to set to contain space like “ ”. It is used to read input and since now our input is comma separated we set IFS to “,”. Ok, enough talk, let’s run the script:

1
$ ./import-SQL.sh

No ouptut, good output. Let’s check our database:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select * from cost;
+----+---------+---------+---------+
| id | column1 | column2 | column3 |
+----+---------+---------+---------+
|  1 |      54 |      68 |      74 |
|  2 |      54 |      67 |      77 |
|  3 |      54 |      67 |      78 |
|  4 |      54 |      67 |      76 |
|  5 |      54 |      67 |      79 |
|  6 |      54 |      67 |      74 |
|  7 |      54 |      67 |      75 |
|  8 |      54 |      67 |      68 |
|  9 |      54 |      67 |      63 |
| 10 |      54 |      65 |      63 |
+----+---------+---------+---------+
10 rows in set (0.00 sec)

Comments