Skip to content

Instantly share code, notes, and snippets.

@pappu687
Last active February 27, 2023 21:14
Show Gist options
  • Save pappu687/9e7e84e3c56711ae2354863623c3007c to your computer and use it in GitHub Desktop.
Save pappu687/9e7e84e3c56711ae2354863623c3007c to your computer and use it in GitHub Desktop.
How to import large flat text file into mysql table

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment