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
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
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
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");
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.