# Bulk MySQL import



## xy16644 (Jan 16, 2014)

I have about a 100 email addresses that I need to import into my new server*'*s MySQL database. I have all the email addresses listed in a text file. Is there a way I can bulk import them into the MySQL table that stores my email addresses?

My email address table looks as follows:

```
mysql> SELECT * FROM mailserver.virtual_aliases;
+----+-----------+-------------------------+-------------------+
| id | domain_id | source                  | destination    |
+----+-----------+-------------------------+-------------------+
|  1 |         1   | alias1@domain.com     | user@domain.com |
```

Currently I have inserted a few email addresses manually:

```
INSERT INTO `mailserver`.`virtual_aliases`
  (`id`, `domain_id`, `source`, `destination`)
VALUES
  ('1', '1', 'alias1@domain.com', 'user@domain.com');
```

I'm sure there*'*s a quicker/easier way to do this than to insert each email address individually!


----------



## SirDice (Jan 17, 2014)

How about just writing a simple Perl script that reads the file and inserts the data? You can use databases/p5-DBI and databases/p5-DBD-mysql.


----------



## xy16644 (Jan 17, 2014)

I don't know any Perl. I thought there may be a way to use an SQL statement to suck in the email addresses from a text file?


----------



## kpa (Jan 17, 2014)

You really should invest some time in learning Perl or some other equivalent scripting language, for example Python if that looks more natural for you. You run into this type of tasks all the time while working in UNIX environment and the simplest and most effective solution is usually writing a small script to do the task.


----------



## SirDice (Jan 17, 2014)

It's not that hard. I agree with @kpa, invest some time in some scripting language as you're bound to need it quite a lot.


```
#!/usr/bin/perl

use strict;
use DBI;

my $database = "mydb";
my $username = "username";
my $password = "password";

my $dbh = DBI->connect("dbi:mysql:${database}",${username}, ${password});

my $sql = "INSERT INTO 'mailserver'.'virtual_aliases' (id, domain_id, source, destination) VALUES (?,?,?,?);";

my $sth = $dbh->prepare($sql);

open(FH, "myfile.txt" );
while($_=<FH>) {
        chomp;
        my($d1,$d2,$d3,$d4) = split(/,/);

        $sth->execute($d1,$d2,$d3,$d4);
}

close(FH);
$dbh->disconnect();
```
Mind you I haven't tested the code and there's absolutely no error handling. But it should read myfile.txt, read each line. Split each line on a comma into four parts and insert those four bits of data into your table. It might take a bit of time to create a script like this but imagine how much time you'd save running the script instead of inserting the data one by one by hand. And best of all, if done properly you can re-use the script many times for even bigger amounts of data.

You're working with computers, you should make them do your work for you


----------



## xy16644 (Jan 17, 2014)

Thanks everyone! I agree, I do need to learn some more on the scripting side of things. It's one of those things on my "To Do for FreeBSD" list.

I appreciate the script. I shall give it a try this weekend hopefully.


----------

