Import Data

Back to Course Website

Export to Text File

Backup or Export Row Data to a Text file with Select

The SELECT query in MySQL includes an INTO OUTFILE option to save the results of the SELECT query to a file. You can use this to create a text file containing row and column values from a table. To use this option, specify INTO OUTFILE and the name of the file after the column names and before the FROM clause.

SYNTAX of EXPORT ROW DATA

SELECT oneOrMoreColumnNames
       INTO OUTFILE "pathAndNameOfTextFile"
       FROM tableName;

The following example saves three columns' values for all of the rows of the mail table into a text file:

Example of EXPORT ROW DATA

SELECT name, address, city
       INTO OUTFILE "mail.txt"
       FROM mail_table;

The INTO OUTFILE option saves a file on the MySQL server, and the MySQL username you are using must have the correct permissions to be able to create a file. If you do not have access to write a file on the server or to read the file after it has been created, you can use client-side utilities to create a similar file. These include the mysqldump utility, described later in this unit.

When you use this INTO OUTFILE option, the created file is a simple text file, with one row per database row. The fields are separated with tab characters by default. You can use various options with the FIELDS and ROWS keywords to modify the format of the output file.

You can use INTO OUTFILE with any combination of SELECT query options. You can specify column names to export, use a WHERE clause to choose rows from the table and use a LIMIT clause to limit the number of rows. You can test the SELECT query without the INTO OUTFILE clause and make sure the right data is displayed before creating the output file.

Format an Import file

MySQL allows you to import data into a table from a text file. This is useful when you need to transfer data from another application into MySQL. Before you attempt to import a file, be sure it is formatted correctly.

To create an import file compatible with MySQL, start with a simple text file. You can create a text file in an editor or export one from an application. Be sure to use a text editor that saves as raw text. Common text editors include Notepad under Windows, and emacs, vi, and pico under UNIX. You can also use a word processor to create the file if you save it as a simple text file.

The file should include one line for each row of data to be added to the table. Each line should end with a newline character. The columns of data should be in the same order on each row. The particular order is not important as long as it is consistent.

One popular format for the text is tab-delimited: Separate the columns with a tab character if possible (and you should put a tab at the end of each line so it imports and displays correctly). If not, consistently use another character, such as a comma, and MySQL can still recognize the file. The file will be easiest to import if the fields are not enclosed within quotation marks or other character. If your application insists on quotation marks, you will need to specify a FIELDS ENCLOSED BY option when you import the data into MySQL.

Your text file should include only data for the table. There should not be any headers, column names, or other extraneous text at the beginning or end of the file. The file should not include any blank lines. If a column's value is blank, you can include a blank field in the file as long as you still include the correct number of tabs or separator characters.

Import from a Text File

The LOAD DATA command in MySQL allows you to import a text file into a MySQL table. You can use this command to import a text file you created with a SELECT INTO OUTFILE command, or a file you created yourself or exported from another application.

SYNTAX of LOAD DATA

LOAD DATA INFILE "pathAndNameOfTextFile"
       INTO TABLE tableName (oneOrMoreColumnNames);

The following is a simple example of a LOAD DATA statement. This loads the file mail.txt into the address table and puts the values into name, address, and city columns.

Example of LOAD DATA

LOAD DATA INFILE "mail.txt"
       INTO TABLE address_table (name, address, city);

The LOAD DATA statement supports several options. First, if you specify the LOW_PRIORITY keyword before INFILE, MySQL waits until no clients are reading from the table before importing the data. If you specify CONCURRENT instead, other clients are allowed to read data from the table while the LOAD DATA operation is working.

You specify the column names after INTO TABLE in parentheses, in the same order they appear in the import file. If you do not specify column names, MySQL will expect the file to contain values for every column in the table's definition, in the same order as they are defined in the table.

By default, MySQL expects column values to be separated with tab characters and lines to end with a newline character \n. You can change this behavior with the FIELDS TERMINATED BY and LINES TERMINATED BY options. You can also specify ENCLOSED BY and ESCAPED BY options, if your file is not in the default format. Specify any of these options after the table name and before the list of columns.

If the text file contains an entry that would conflict with an existing table row, the import stops with an error. You can specify the IGNORE option to prevent this error and skip conflicting rows, or specify REPLACE and the data in the text file will override any conflicting data in the table.

Export Data from a Spreadsheet

Because MySQL allows you to import a simple text file, you can move data into MySQL table from any application that can export to a text file. One common application is a spreadsheet, such as Microsoft Excel. Because spreadsheets store data in columns and rows, their format can easily be adapted to a MySQL table.

When exporting data from any application, the ideal format is a text file with tabs separating columns and one line per row. This is the MySQL default format for the LOAD DATA statement. If this option is not available, columns separated by commas or other characters will work.

When you save a file in Excel, one of the available file types is Text (tab delimited). This format matches MySQL's default settings and makes it easy to export data for use with a MySQL table. If you are using a different spreadsheet, look for a similar option. If a character other than a tab is used to separate fields, note it so you can specify it in the FIELDS SEPARATED BY clause of the LOAD DATA statement later.

Before exporting data:

  1. be sure your data is organized into a single worksheet
  2. have a consistent number of columns on each row.
  3. remove any rows that contain header information, captions, or comments. Only rows of data should be included.
  4. Remove punctuation ($ or comma) from numbers. You may need to right click on numbers that have special formatting ($ or comma) and remove their formatting.

If you do not yet have a MySQL table prepared for the data, created it based on the fields in the spreadsheet. If you define the columns of the table in the same order as they are used in the spreadsheet, you will not need to specify columns names in the LOAD DATA statement when you import the file into MySQL.

Export Data From Microsoft Access

Microsoft Access is the most popular desktop database application, and it is often used to store business data. Access includes a sophisticated Export option that can export a file in several different formats. You can use this feature to create a text file that you can import into a MySQL table using the LOAD DATA statement.

To export data form Access:

  1. Open the table you want to export.
  2. At thte top of the interface, click the External Data tab, look for the "Export" section, and select "Text file" from the "File" menu.
  3. The Export Text Wizard prompts you for several options to control the formatting of the text file...
  4. Select a filename and location. Click OK.
  5. Choose the Delimited option.
  6. Next you can select comma, tab, or another character to separate fields, and choose whether to enclose data in quotation marks or another character. Select the Tab delimited option from the wizard and select none for the Text Qualifier option rather than a quotation mark, so that the file will be created in the default format for the LOAD DATA command in MySQL.
  7. Look around for other options that can help you export tricky data, such as dates...
  8. If the last piece of data at the end of each row is getting lost, you may need to open the txt file and manually add a tab at the end of each row.

Like a MySQL database, an Access database can contain any number of tables. If you need to transfer multiple tables of data to MySQL, you must export each table separately to a text file.

To prepare a table for export, be sure it contains only rows of data you want to include in the MySQL table. The table should not include an comments or field names in the first rows, as this would result in invalid data being exported.

You can use a similar process to export data from most other database applications. If an application does not let you choose the characters to enclose and separate fields, you can use the FIELDS SEPARATED BY and ENCLOSED BY options in the LOAD DATA statement to make MySQL work with the format of the file.

Back Up an Entire Database

While you can export data to a text file using SELECT and the INTO OUTFILE option, that process creates a simple text file. MySQL includes a separate utility, mysqldump, that you can use to back up a database to a text file. The output file includes SQL statements and can be used to rebuild the database or tables.

Steps to prepare to use mysqldump:

  1. Open a DOS window (Start menu > Run > cmd)
  2. Navigate to mysql bin directory (type this: cd\wamp\bin\mysql\mysql5.0.51b\bin)
  3. Run mysqldump as shown below.
    • The mysqldump command will NOT work from the mysql command line prompt, you must be in DOS.
    • Do NOT end with a semicolon.

To use mysqldump, specify a database name and one or more table names.

This utility supports the same -u and -p options as the mysql command. You will usually need to use these options to specify a username with access to the tables you are dumping and the correct password.

SYNTAX of BACKUP DATABASE

mysqldump -uusername -ppassword databaseName oneOrMoreTableNames
            > textFileName

The mysqldump utility does not create a text file by itself -- it normally dumps the text to the standard output at the shell prompt. The following example dumps the mail and address tables from the testdb database (though not to a text file because none is indicated):

Example of BACKUP DATABASE

mysqldump -uroot testdb mail_table address_table

Note that in that example we are using the all-powerful "root" as our username, which does not require a password.

You can use the > operator from the command line to save the output to a file instead.

SYNTAX of BACKUP to a TEXT FILE

mysqldump -uroot testdb mail_table address_table >mybackup.sql

The resulting text file is created in C:wamp\bin\mysql\mysql5.0.51b\bin. Had you wanted it on the D drive, you could have specified >D:/mybackup.sql

If you do not specify table names, mysqldump will dump the entire database. You can also back up more than one database by specifying the -B option and a list of databases. In this case, you cannot specify a particular table name. The following example backs up the db1 and db2 databases:

mysqldump -uroot -B db1 db2

The mysqldump utility always includes all of the columns of each table. You can specify the format of the text output using several options: --fields-terminated-by, --fields-enclosed-by, --fields escaped by, and --lines--terminated-by. These are the same as the options for the LOAD DATA command.

Restore a Backup File

When you have created a backup text file using mysqldump, you can use it to re-create the database or tables that were backed up. The text file includes SQL statements, such as CREATE TABLE and INSERT, to rebuild the backed up databases or tables. To use the file, you can simply route it through the mysql command to process the SQL statements.

The following example uses the mysql command to restore the database backed up in a db.txt file:

SYNTAX of RESTORE from TEXT FILE

mysql testdb 

	
You can also use the SOURCE command within the MySQL monitor. This command reads SQL commands from a text file you specify.


	
As with the standard mysql command, you can use the -u option to specify a username and the -p option to specify a password. The 
	username you specify must have permission to create the database or table you are restoring. You may also need to use the - h options 
	to specify a host name.


	
If you are restoring a database or table that has been corrupted or updated incorrectly, you should first use the DROP TABLE or
	 DROP DATABASE commands to delete any existing data.


	
The file created by mysqldump is a standard text file. If you need to modify the SQL statements in the file before import, 
	you can use any text editor. If you have a backup file for an entire database and need to restore a single table, you use text 
	editor to move the statements for that table to a different file.


	
Along with restoring a database that has been lost or corrupted, you can use the backup file to import the database and tables 
	onto a different MySQL server. This is an efficient way to move data between servers.