How to import large flat text file into mysql table

import.md

Import Large flat text file into mysql

Say you have a 1GB text file (words.txt) and has a word each line. Like this

1. John
2. Doe
3. Jane
4. Smith

And you want to dump this into a mysql table for example

id	word
1	John
2	Doe
3	Jane
4	Smith

We can write script to parse it and make sql statement and add it - but that's going to take quite long. Here's a what we'll do.

• Create a mysql db + table
• Replace each word with sed command to build a query string
• import in mysql

Step 1

---

Create a table in any of your mysql db. For example words in dictionary.

CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Btw, MyISAM will be faster to import

Step 2

---

Convert the flat text file into a sql export file.

sed 's/^/insert into words(word) values("/; s/$/");/;' words.txt > new-words.txt

We'll get a new-words.txt file with contents like

insert into words(word) values("John");
insert into words(word) values("Doe");
insert into words(word) values("Jane");
insert into words(word) values("Smith");

Step 3

---

Import the new file into mysql directly.

mysql -uroot -p dictionary < new-words.txt

RESULT : 9 Million rows imported in less than 7 minutes. Intel Core i7, 2.8Ghz , 12 GB RAM

To import multiple fields separated by comma or tabs, you need to change the sed command and play with it how it works.