LOAD DATA INFILE 'some_file.txt' INTO TABLE 'some_table' (list, of, columns, ...);
|
Migrating data between other applications and MySQL is often easiest done by exporting the data to a structured text format (such as tab-separated values) and then importing it into MySQL. The import could be done by converting the data into a set of SQL queries, but this approach can be cumbersome and error-prone. A simpler (and much faster) method for getting data from structured text files into MySQL is to use the LOAD DATA INFILE command. By default, LOAD DATA INFILE understands how to import files that are in a tab-separated value format, where each field in the file is separated by a tab and each row by a newline. When LOAD DATA INFILE reads a row from the data file, it adds a row to the target table using the column mapping specified. For example, you want to import the following text file into your book table: Where the Wild Things Are Maurice Sendakfine Cigars of the PharoahHergegood .........
If this file is saved to /Users/zak/books_and_authors.txt, you can import the text using the following command: USE library; LOAD DATA INFILE '/Users/zak/books_and_authors.txt' INTO TABLE book (title, author, cond);
This should result in table book containing the following new rows: +---------+---------------+----------------+------+ | book_id | title | author | cond | +---------+---------------+----------------+------+ | 5 | Cigars of ... | Herge | good | | 6 | Where the ... | Maurice Sendak | fine | | 7 | ... | ... | ... | +---------+---------------+----------------+------+
There are a few key things to note in the LOAD DATA INFILE command: The data file is stored on the same machine as the MySQL server. Tip If you are working with a remote MySQL server and want to load data from a local file to the server, you need to use the LOCAL keyword. Visit http://mysql.com/LOADDATAINFILE for more information.
You need to specify the complete path to the file from which you load data. If you just specify a filename without a path, the MySQL server will look somewhere in its data directory. If you are using the LOCAL keyword and do not specify a full path, the MySQL client will look in the directory from which it was started. On Windows systems, you should still use forward slashes in your path (instead of backslashes). For example, on a Windows system, the Unix-style path used ('/Users/zak/books_and_authors.txt') might instead be 'C:/Desktop/book_and_authors.txt'. If backslashes had been used in the previous path, they would be interpreted as character escape sequences. Additionally, here are a few general tips to make your use of LOAD DATA INFILE easier: If the output of the command is followed by a warning or error (such as Query OK, 3 rows affected, 1 warning (0.01 sec)), use SHOW WARNINGS; or SHOW ERRORS; to display the exact problems that were encountered. To test if you have set up a complex LOAD DATA INFILE correctly, create a temporary table like the table with which you plan to work. Then run your LOAD DATA INFILE command on the temporary table instead. If necessary for performance or storage reasons, limit the amount of data that you load into the temporary table. For example, if you had wanted to test the code in this example before letting it near your production database, you could have used the following code: USE library; # Create a temp table like your real table CREATE TEMPORARY TABLE book_temp LIKE book; # load in part of your data LOAD DATA INFILE '/Users/zak/books_and_authors.txt' INTO TABLE book_temp (title, author, cond) IGNORE 195 LINES; # ignore the first 195 lines SELECT * FROM book_temp; # see if things look ok The command has many options to allow you to control how data is imported, such as what format of data is read, how many rows are imported, and so on. See http://mysql.com/LOADDATAINFILE for more information.
|
