MySQL Tutorial/MySQL Utilities/Import Export Data

Exporting Data

The command to export data to a text file is similar to the standard SELECT command.

MySQL creates a "tab delimited" file by default.

   <source lang="sql">

SELECT <columns> FROM <table_name> INTO OUTFILE <file_name></source>

Importing data

Importing data is a very simliar process to exporting.

The syntax for importing a file is:

   <source lang="sql">

LOAD DATA INFILE "file_name.txt" INTO TABLE tbl_name (field1, field2...etc)</source>

LOAD DATA INFILE into a table

   <source lang="sql">

mysql> mysql> mysql> CREATE TABLE Topic(

   ->    TopicID     SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ->    Name        VARCHAR(50) NOT NULL,
   ->    InStock     SMALLINT UNSIGNED NOT NULL,
   ->    OnOrder     SMALLINT UNSIGNED NOT NULL,
   ->    Reserved    SMALLINT UNSIGNED NOT NULL,
   ->    Department  ENUM("Classical", "Popular") NOT NULL,
   ->    Category    VARCHAR(20) NOT NULL,
   ->    RowUpdate   TIMESTAMP NOT NULL
   -> );

Query OK, 0 rows affected (0.05 sec) mysql> mysql> mysql> INSERT INTO Topic (Name, InStock, OnOrder, Reserved, Department, Category) VALUES

   ->                   ("Java",          10,      5,       3,        "Popular",    "Rock"),
   ->                   ("JavaScript",    10,      5,       3,        "Classical",  "Opera"),
   ->                   ("C Sharp",       17,      4,       1,        "Popular",    "Jazz"),
   ->                   ("C",             9,       4,       2,        "Classical",  "Dance"),
   ->                   ("C++",           24,      2,       5,        "Classical",  "General"),
   ->                   ("Perl",          16,      6,       8,        "Classical",  "Vocal"),
   ->                   ("Python",        2,       25,      6,        "Popular",    "Blues"),
   ->                   ("Php",           32,      3,       10,       "Popular",    "Jazz"),
   ->                   ("ASP.net",       12,      15,      13,       "Popular",    "Country"),
   ->                   ("VB.net",        5,       20,      10,       "Popular",    "New Age"),
   ->                   ("VC.net",        24,      11,      14,       "Popular",    "New Age"),
   ->                   ("UML",           42,      17,      17,       "Classical",  "General"),
   ->                   ("www.sqle.ru",25,      44,      28,       "Classical",  "Dance"),
   ->                   ("Oracle",        32,      15,      12,       "Classical",  "General"),
   ->                   ("Pl/SQL",        20,      10,      5,        "Classical",  "Opera"),
   ->                   ("Sql Server",    23,      12,      8,        "Classical",  "General");

Query OK, 16 rows affected (0.00 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> mysql> select * from Topic; +---------+----------------+---------+---------+----------+------------+----------+---------------------+ | TopicID | Name | InStock | OnOrder | Reserved | Department | Category | RowUpdate | +---------+----------------+---------+---------+----------+------------+----------+---------------------+ | 1 | Java | 10 | 5 | 3 | Popular | Rock | 2007-07-23 19:09:52 | | 2 | JavaScript | 10 | 5 | 3 | Classical | Opera | 2007-07-23 19:09:52 | | 3 | C Sharp | 17 | 4 | 1 | Popular | Jazz | 2007-07-23 19:09:52 | | 4 | C | 9 | 4 | 2 | Classical | Dance | 2007-07-23 19:09:52 | | 5 | C++ | 24 | 2 | 5 | Classical | General | 2007-07-23 19:09:52 | | 6 | Perl | 16 | 6 | 8 | Classical | Vocal | 2007-07-23 19:09:52 | | 7 | Python | 2 | 25 | 6 | Popular | Blues | 2007-07-23 19:09:52 | | 8 | Php | 32 | 3 | 10 | Popular | Jazz | 2007-07-23 19:09:52 | | 9 | ASP.net | 12 | 15 | 13 | Popular | Country | 2007-07-23 19:09:52 | | 10 | VB.net | 5 | 20 | 10 | Popular | New Age | 2007-07-23 19:09:52 | | 11 | VC.net | 24 | 11 | 14 | Popular | New Age | 2007-07-23 19:09:52 | | 12 | UML | 42 | 17 | 17 | Classical | General | 2007-07-23 19:09:52 | | 13 | www.sqle.ru | 25 | 44 | 28 | Classical | Dance | 2007-07-23 19:09:52 | | 14 | Oracle | 32 | 15 | 12 | Classical | General | 2007-07-23 19:09:52 | | 15 | Pl/SQL | 20 | 10 | 5 | Classical | Opera | 2007-07-23 19:09:52 | | 16 | Sql Server | 23 | 12 | 8 | Classical | General | 2007-07-23 19:09:52 | +---------+----------------+---------+---------+----------+------------+----------+---------------------+ 16 rows in set (0.00 sec) mysql> mysql> CREATE TABLE Topic3

   -> (
   ->    Name VARCHAR(50) NOT NULL,
   ->    InStock SMALLINT UNSIGNED NOT NULL,
   ->    Category VARCHAR(20)
   -> );

mysql> mysql> mysql> SELECT Name, InStock, Category INTO OUTFILE "TopicCountry.sql"

   ->    FIELDS
   ->       TERMINATED BY ","
   ->       ENCLOSED BY """
   -> FROM Topic WHERE Category="Country";

mysql> mysql> mysql> LOAD DATA INFILE "TopicCountry.sql"

   -> INTO TABLE Topic3
   -> FIELDS
   ->    TERMINATED BY ","
   ->    ENCLOSED BY """;

Query OK, 1 row affected (0.00 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 mysql> mysql> drop table Topic; Query OK, 0 rows affected (0.00 sec)</source>

Load data to your table

Create a text file myTable.txt containing one record per line.

The values are separated by tabs.

The order should be consistant with the columns listed in the CREATE TABLE statement.

For NULL values, use \N (backslash, capital-N).

For example:

   <source lang="sql">

1 firstName lastName M \N 1997-12-09</source>

Turn Exporting and Importing Data on

You should be able to turn this feature on by issuing the command:

   <source lang="sql">

local-infile=1</source>