Skip to content

Instantly share code, notes, and snippets.

Last active February 27, 2023 21:14
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
What would you like to do?
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` (
  `word` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)

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