Importing delimited text file into mysql.

Technical Q&A involving operating systems, networking, software, and hardware issues.

Moderator: jasonb

Post Reply
User avatar
jasonb
Site Administrator
Posts: 105
Joined: Tue Apr 22, 2003 1:54 pm
Location: Toronto, Canada
Contact:

Importing delimited text file into mysql.

Post by jasonb »

This one is less obvious that it should be. Even though there is help on the "mysqlimport" command (which is the tool to use for this) there is no clear example that helped me figure it out the first time I wanted to try this.

Let's say that you have the following text, where the fields are delimited by the "|" character, the name of the database is "company" and the name of the table you're importing to is "contact":

Code: Select all

Name|Address|Phone#
Name your text file contact, and make sure that everybody has read writes to it.

The command you would use to import the data is:

mysqlimport --fields-terminated-by="|" --password company contact

Whatever user your currently logged in as, will be the user that the command will run under.

If all you want to do is import a certain field, say names, you can do that too - let's say that the text file looks like this:

Code: Select all

Smith, John|Eves, Ernie|Doe, Jane
Issue the following command:

mysqlimport -c Names --lines-terminated-by="|" --password company contact

(If you had each name on a separate line, you wouldn't need to bother with the "--lines-separated-by" bit.)

To delete any previous entries in the table, you can add a "-d" switch to the command line.
Post Reply