MYSQL-TOOLS

Comparison of graphical tools for MySQL

Home Export and Import Data in MySQL Exporting and Importing Data in MySQL

Exporting and Importing Data in MySQL

Print PDF
User Rating: / 0
PoorBest 

EXPORTING: Data Out of a Table

We can export data from a MySQL database to text files, using a SELECT statement with certain export definitions.

Syntax
<select statement>::=
SELECT ....
INTO OUTFILE '<filename>'
{FIELDS
[TERMINATED BY '<value>']
[[OPTIONALLY] ENCLOSED BY '<value>']
[ESCAPED BY '<value>']}
| {LINES
[STARTING BY '<value>']
[TERMINATED BY '<value>']}
| INTO DUMPFILE '<filename>'
FROM <tables>... rest of SELECT statement

Note: The <export definition> precedes the FROM clause of SELECT. An out file is a text file with exported data in a delimited format. A delimited format is one in which the values and rows are separated and enclosed by specific types of characters. For example, a tab is commonly used to separate values in the same row, and a line break (also known as newline) is often used to separate rows.

In contrast to an out file, a dump file contains only one row that is not delimited. Dump files are used primarily for large values, such BLOB columns.

Use Field and Line Separators

As shown in the syntax, the FIELDS clause includes the following three subclauses:

  • TERMINATED BY: Character(s) to be used to separate columns. By default, a tab is used - \t.
  • ENCLOSED BY: Character to enclose each column. By default, no characters are used.
  • ESCAPED BY: Character to escape special characters. By default, a backslash is used - \\.

The values you specify TERMINATED BY and ENCLOSED BY clauses are used as is. The ESCAPED BY clause is used to escape any characters in the data that are also present in the FIELDS subclauses. This allows for the data values to be treated literally, rather than separators. The ESCAPED BY subclause escapes the following characters when they appear in a value:

  • The first character in the FIELDS TERMININATED BY and LINES TERMINATED BY subclauses.
  • The character in the ENCLOSED BY subclause.
  • The character in the ESCAPED BY subclause.
  • Uppercase N, when used to represent NULL.

Whenever any of these separator characters appear in a data value, the ESCAPED BY character precedes the character. You will see an example of this later in this section.

Now take a look at the LINES clause, shown in the following syntax:

Syntax
LINES
[STARTING BY '<value>']
[TERMINATED BY '<value>']

As you can see, the LINES clause supports the following two subclauses:

  • STARTED BY: character(s) to begin each row of data. None by default.
  • TERMINATED BY: character(s) to be used to end each row . A Newline by default - \n.

When you're specifying the values that can be inserted in any of the FIELDS or LINES subclauses, you can use a literal value, or you can use one of the special values supported by the subclauses. Whenever you use a special value, it must be preceded by a backslash. The following table provides the meanings for the primary special values that you can use in your FIELDS and LINES subclauses.

Value Meaning
\' Single quote
\\ Backslash
\n Newline
\r Carriage return

\s

Space

\t

Tab

As an example, you can specify that a space (\s) be used between columns in a row via FIELDS TERMINATED BY \s subclause).

Code Sample: ExportImport/Demos/Out-File-Simple.sql

SELECT f.film_id, f.title, f.rating, f.rental_rate, c.name
INTO OUTFILE 'filmlist.txt'
FROM film f, film_category fc, category c
WHERE f.film_id = fc.film_id
AND fc.category_id = c.category_id;
Code Explanation

A file called 'filmlist.txt' is created in MySQL data directory and the result set from Select statement will be written to that file.

Here are the contents of the filmlist.txt file:

    19 AMADEUS HOLY PG Action
21 AMERICAN CIRCUS R Action
29 ANTITRUST TOMATOES NC-17 Action

The file is saved to the location of the database folder that corresponds to the database in which you're working. For example, if you're working in the test database, the file is saved to the test folder in your data directory. To save the file to a different location, you must specify a path for that file.

Warning: The SELECT statement will not overwrite and will fail if a file by that name already exists.

By default, whenever a value is NULL, an uppercase N is returned, preceded by a ESCAPED BY character.

Here is an example using field separators where values are separated by commas (as per TERMINATED BY subclause), enclosed in double quotes (as per ENCLOSED BY subclause) and Nulls are preceded by an asterisk (as per ESCAPED BY subclause).

Code Sample: ExportImport/Demos/Out-File-Fields.sql

SELECT f.film_id, f.title, f.rating, f.rental_rate, c.name
INTO OUTFILE 'filmlist_fields.txt'
FIELDS
TERMINATED BY ','
ESCAPED BY '*'
ENCLOSED BY '"'
FROM film f, film_category fc, category c
WHERE f.film_id = fc.film_id
AND fc.category_id = c.category_id;
Code Explanation

A file called 'filmlist_fields.txt' is created in MySQL data directory and the result set from Select statement will be written to that file.

Here are the contents of the filmlist_fields.txt file:

            "19","AMADEUS HOLY","PG","0.99","Action"
"21","AMERICAN CIRCUS","R","4.99","Action"
"29","ANTITRUST TOMATOES","NC-17","2.99","Action"

Note: Any asterisks in fields values is preceded by an asterisk quote because an asterisk is specified in the FIELDS subclauses.

Here is an example of a LINE clause:

Code Sample: ExportImport/Demos/Out-File-Lines.sql

SELECT f.film_id, f.title, f.rating, f.rental_rate, c.name
INTO OUTFILE 'filmlist_lines.txt'
LINES
STARTING BY '+BEGIN+'
TERMINATED BY '_END_\n'
FROM film f, film_category fc, category c
WHERE f.film_id = fc.film_id
AND fc.category_id = c.category_id;
Code Explanation

Here are the contents of the filmlist_lines.txt file:

    +BEGIN+19 AMADEUS HOLY PG 0.99 Action_END_
+BEGIN+21 AMERICAN CIRCUS R 4.99 Action_END_
+BEGIN+29 ANTITRUST TOMATOES NC-17 2.99 Action_END_

As you can see, we are not limited to just symbols in the LINE subclauses. The SELECT statement above starts each line with a +BEGIN+ and terminates each line with _END_

Code Sample: ExportImport/Demos/Out-File-Field-Lines.sql

SELECT f.film_id, f.title, f.rating, f.rental_rate, c.name
INTO OUTFILE 'filmlist_field_lines.txt'
FIELDS
TERMINATED BY '</TD><TD>'
LINES
STARTING BY '<TR><TD>'
TERMINATED BY '</TD></TR>\n'
FROM film f, film_category fc, category c
WHERE f.film_id = fc.film_id
AND fc.category_id = c.category_id;
Code Explanation

Here are the contents of the filmlist_field_lines.txt file:

    <TR><TD>19</TD><TD>AMADEUS HOLY</TD><TD>PG</TD><TD>0.99</TD><TD>Action</TD></TR>
<TR><TD>21</TD><TD>AMERICAN CIRCUS</TD><TD>R</TD><TD>4.99</TD><TD>Action</TD></TR>
<TR><TD>29</TD><TD>ANTITRUST TOMATOES</TD><TD>NC-17</TD><TD>2.99</TD><TD>Action</TD></TR>

The data has been intentionally made to look like an HTML Table fragment, with each data row mapping to an HTML-Table-Row.

Dump a Row to a File

Exporting data to a dump file is simpler as there is no option to specify:

Code Sample: ExportImport/Demos/Dump-File.sql

SELECT f.film_id, f.title, f.rating, c.name
INTO DUMPFILE 'filmlist_dump.txt'
FROM film f, film_category fc, category c
WHERE f.film_id = fc.film_id
AND fc.category_id = c.category_id
AND f.film_id = 998;
Code Explanation

In this statement, the data extracted from the film table is exported to the filmlist_dump.txt file. Notice that the SELECT statement returns only one row of data. The SELECT statement must return exactly one row if you want to save data to a dump file. In addition, the values are not in any way delimited. When added to the file, all values from that row are run together, as shown in the following results:

998ZHIVAGO CORENC-17Horror

CTAS: Creating a New Table Using a ResultSet

One can create a table using a select statement at the end of a New table definition. The values returned by that statement are automatically inserted in the new table. The select result set must be compatible with the data types, order and count of the columns in the new table.

For example, the following CREATE TABLE statement extracts data from the film and category tables and adds it to the new film2 table:

Code Sample: ExportImport/Demos/Create-Table-Film2-As-Select.sql

DROP TABLE film2;
CREATE TABLE film2 (
film_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY REFERENCES film(film_id),
film_title VARCHAR(50),
rating ENUM('G','PG','PG-13','R','NC-17') DEFAULT 'G',
rental_rate DECIMAL(4,2) NOT NULL DEFAULT 4.99,
category_name VARCHAR(25)
)ENGINE=InnoDB DEFAULT CHARSET=utf8
SELECT f.film_id,f.title as film_title,f.rating,f.rental_rate,c.name as category_name
FROM film f, film_category fc, category c
WHERE f.film_id = fc.film_id
AND fc.category_id = c.category_id;
Code Explanation

The first part of the statement is a standard table definition with four columns. The table definition is followed by a SELECT statement to read values to be inserted in the new table.

Warning: For mismatching column names in the NEW table, SELECT statement must use names from the NEW table as aliases, otherwise new columns will be created causing confusion and data loss.

Once the table has been created, check the contents of the new table.

SELECT * FROM film2;

SELECT here should return results similar to what the SELECT used in CTAS would have returned.

Warning: In the new table, the columns data types must be compatible with the data being read. We need to worry not only about data type (CHAR versus INT), but also the data length (VARCHAR(5) versus VARCHAR(50)). MySQL WILL truncate and convert data, possibly causing unintended data loss.

Tip: As a verification aid, a select script with UNION between the new table and SELECT used to populate the table is provided. If everything went well, this SELECT should return a 1000 rows, same number as in original film table.

Code Sample: ExportImport/Exercises/Create-Table-Film2.sql

DROP TABLE film2;
CREATE TABLE film2 (
film_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY REFERENCES film(film_id),
film_title VARCHAR(50),
rating ENUM('G','PG','PG-13','R','NC-17') DEFAULT 'G',
rental_rate DECIMAL(4,2) NOT NULL DEFAULT 4.99,
category_name VARCHAR(25)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Copying Data into an Existing Table

Using INSERT and REPLACE statements, you can add or replace data in an existing table. As you now know, the main difference between the two is in how existing values in a primary key column or a unique index are treated.

Code Sample: ExportImport/Demos/Insert-Film2-As-Select.sql

source ../Exercises/Create-Table-Film2.sql;

INSERT INTO film2
SELECT f.film_id,f.title as film_title,f.rating,f.rental_rate,c.name as category_name
FROM film f, film_category fc, category c
WHERE f.film_id = fc.film_id
AND fc.category_id = c.category_id;
Code Explanation

The first part of the statement is a standard table definition with four columns. The table definition is followed by a SELECT statement to read values to be inserted in the new table.

 

Tip: If you want to keep the existing table, you can use TRUNCATE to delete all existing rows.

Once the table has been created, check the contents of the new table.

SELECT * FROM film2;

Again, film2 should contain same rows as the populating SELECT statement returns at that instant.

Note: Using REPLACE would not throw PK errors, as it would overwrite the data instead.

Using the LOAD DATA to Import Data

The LOAD DATA statement allows you to import values directly from a delimited text file, and these files may have been created by a SELECT export data as discussed earlier.)

Syntax
<load data statement>::=
LOAD DATA [LOCAL] INFILE '<filename>'
[REPLACE | IGNORE]
INTO TABLE <table name>
[<import option> [<import option>]]
[IGNORE <number> LINES]
[(<column name> [{, <column name>}...])]
<import option>::=
{FIELDS
[TERMINATED BY '<value>']
[[OPTIONALLY] ENCLOSED BY '<value>']
[ESCAPED BY '<value>']}
| {LINES
[STARTING BY '<value>']
[TERMINATED BY '<value>']}

There are many options here, however relatively few of these elements are actually required. The following syntax shows the bare-bones components that make up a basic LOAD DATA statement:

Code Sample: ExportImport/Demos/Load-Data-Simple.sql

source ../Exercises/Create-Table-Film2.sql;

LOAD DATA INFILE 'filmlist.txt'
INTO TABLE film2;
Code Explanation

Create or TRUNCATE film2 table.

The specified file will be used to load data from an exported file into the given table name.

The filmlist.txt file exported earlier is being used to import into film2 table.

General Loading Rules

  • By default, the LOAD DATA statement retrieves data from files stored on the MySQL server host. If you are accessing MySQL from that host, then the file is read from the local location.
  • If you're accessing MySQL from a client computer where the target file is also located, you can specify the LOCAL keyword to direct MySQL to look for the file on the client computer rather than the server host.
  • Once you specify any option that you want to include in the LOAD DATA clause, you must specify the INFILE keyword, followed by the name of the target file, enclosed in single quotes.
  • If only a filename is specified, and no directory path, MySQL looks for the file in the folder associated with the current database (which is located in the data directory). You can also specify a full path, in which case MySQL looks in the specified location for the file.
  • The [REPLACE | IGNORE] options refer to values that are duplicated in a unique index when you try to insert data. If neither option is specified, you receive an error if you try to duplicate a unique key. REPLACE option replaces the existing rows with new rows. IGNORE option lets the insert process continue.
  • The syntax for fields and lines is similar to the export options in the SELECT statements we saw earlier.
  • Warning: For text files created in some Windows applications, LINES TERMINATED BY '\r\n' subclause may be needed even if default setting is a new line.
  • The IGNORE clause lets you skip or ignore a certain number of rows before load begins.
  • After the IGNORE clause, you can specify one or more columns from the target table. If you specify column names, each row returned by the LOAD DATA statement must include one value for each specified column. If you don't specify any column names, each row returned must include one value for each column in the table.
  • Note: Be sure that each row in the file that contains the data to be imported includes the correct number of values. The number of values should match the number of columns, and the values should be of a type compatible with the columns.
  • If you try to insert too few or too many values per row, you will receive unexpected / undesired results as MySQL attempts to insert the data in the order that it appears in the files. For example, if your file includes four values per row and your table includes six columns, MySQL attempts to place the first four values in the first four columns in the order that the values are specified, and no values are inserted in the remaining two columns. If a value cannot be inserted in a targeted column, the value is truncated or ignored.

More Loading Examples

In this example, the values are separated by a comma and enclosed by double quotes, as it was specified while exporting the out file in the complementary example.

Code Sample: ExportImport/Demos/Load-Data-Fields.sql

source ../Exercises/Create-Table-Film2.sql;

LOAD DATA INFILE 'filmlist_fields.txt'
INTO TABLE film2
FIELDS
TERMINATED BY ','
ESCAPED BY '*'
ENCLOSED BY '"'
;
Code Explanation

Create or TRUNCATE film2 table.

The specified file filmlist_fields.txt generated earlier will be used to load data into the film2 table.

The data file contains specific field values used as separators.

Same field separators must be specified while loading, as were given while exporting.

And to complete the symmetry, here is the lines and field-lines examples of LOAD for outputs generated before:

Code Sample: ExportImport/Demos/Load-Data-Lines.sql

source ../Exercises/Create-Table-Film2.sql;

LOAD DATA INFILE 'filmlist_lines.txt'
INTO TABLE film2
LINES
STARTING BY '+BEGIN+'
TERMINATED BY '_END_\n'
;
Code Explanation

Create or TRUNCATE film2 table.

The specified file filmlist_lines.txt generated earlier will be used to load data into the film2 table.

The data file contains specific line terminators.

Same line terminators must be specified while loading, as were given while exporting.

Code Sample: ExportImport/Demos/Load-Data-Field-Lines.sql

source ../Exercises/Create-Table-Film2.sql;

LOAD DATA INFILE 'filmlist_field_lines.txt'
INTO TABLE film2

FIELDS
TERMINATED BY '</TD><TD>'
LINES
STARTING BY '<TR><TD>'
TERMINATED BY '</TD></TR>\n'
;
Code Explanation

The specified file filmlist_field_lines.txt generated earlier will be used to load data into the film2 table.

The data file contains specific values for both field separators and line terminators.

Same separators and line terminators need to specified while loading.

Using the mysqlimport Utility to Import Data

The mysqlimport utility allows you to import delimited data in a text file and uses many of the same functions as the LOAD DATA statement.

The mysqlimport command statement does not include the name of the target table. Instead, the table is determined by the name of the file. MySQL assumes that the filename will be the same as the table name (not counting any file extensions). For example, while inserting data into the film2 table, the file must be named film2.<any-extension>.

We will reuse the Simple output filmlist.txt from our previous example, and copy the file to film2.txt to match the table name we will importing data into in the appropriate MySQL Data directory. Remember to create the table and or TRUNCATE it before loading to avoid duplicates.

mysqlimport --user=root --password=<password> sakila "film2.txt"

At our operating system's command prompt, we specify mysqlimport command, followed by the MySQL authentication arguments, followed the name of the database sakila and the filename film2.txt. The user and password are ones we normally use when accessing the MySQL databases.

The file is expected to be in Path C:\Program Files\MySQL\MySQL Server 5.0\data\sakila\.

If everything goes well, you should have a film2 table with 1000 records after the execution of command above.

Using Separators with mysqlimport Utility

This discussion will not be complete without an example using pre-defined separators. In another export example, we had used specific fields to generate filmlist_fields.txt, where the FIELDS clause was:

TERMINATED BY ','
ESCAPED BY '*'
ENCLOSED BY '"'

The field values are separated by a comma and enclosed by double-quotes and escaped with Asterisks.

So while running the mysqlimport command, we must specify these formatting characters with the command as shown :

Remember to create or clear the table before running this command. Also, please ensure there is a film2.txt file available in the data directory.

mysqlimport --user=root --password=<password> --fields-terminated-by="," --fields-escaped-by="*"
 --fields-enclosed-by="\"" sakila "..\Exercises\filmlist_fields.txt"

Three more separator arguments have been added to the command statement:

  • --fields-terminated-by=","
  • --fields-escaped-by="*"
  • --fields-enclosed-by="\""

As you can see, the mysqlimport utility allows for arguments that map to the FIELDS and LINES subclauses of a LOAD DATA statement. As an example, the FIELDS ESCAPED BY subclause in the LOAD DATA statement is the same as the --fields-escaped-by argument in the mysql import utility. For various mysqlimport arguments, please review the MySQL product documentation.

Exporting and Importing Data in MySQL Conclusion

In this lesson of the MySQL tutorial, we learnt the exporting and importing of data in MySQL Databases.

To continue to learn MySQL go to the top of this page and click on the next lesson in this MySQL Tutorial's Table of Contents.
 
mysql tools

HTTP-tunnel - Ability to manage MySQL database via the Internet. More...

'mysql.proc' doesn't exist fixed problem

_

Table 'mysql.proc' doesn't exist. Fix error 1146 Table doesn't exist here...