Import Data From CSV File to MySQL With Bash Script

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)