TXT to MySQL

• How do I import data into MySQL from a simple text file?

• ---

  If you have a plain text file that contains data, the LOAD DATA INFILE statement can be used to import it into MySQL. As an example, suppose that you have a text file called data.txt in the /tmp directory. Suppose further that within the file there is one record per line and that the fields of each are separated by a vertical bar. Below is what you might enter through the mysql client to import the data into the table table1

  LOAD DATA INFILE '/tmp/data.txt'
  INTO TABLE db1.table1
  FIELDS TERMINATED BY '|';
  

  The first line of this statement specifies the path and the name of the file to import. For a Windows server, the forward-slashes are still used for the file's path, but a drive may need to be specified at the beginning of the path (e.g, 'c:/tmp/prospects.txt'). The INTO TABLE clause specifies the database and the table to import into. The third line above specifies the vertical bar as the field delimiter.
  For some text files, you may need to specify the line terminator. If lines are terminated by a line-feed (\n), it doesn't need to be specified since it's the default. However, suppose that the rows in the text file start and end with double-quotes and a Windows hard-return (i.e., a return and a line-feed). Suppose also that the fields are terminated with a [TAB]. The statement would need to be entered like this:

  LOAD DATA INFILE '/tmp/data.txt'
  INTO TABLE table1
  FIELDS TERMINATED BY '|'
  LINES STARTING BY '"'
  TERMINATED BY '"\r\n';
  

  Sometimes the first row of a data text file will contain column headings. To have MySQL ignore the first lines of text, add the IGNOREclause to the end of the LOAD DATA INFILE statement like this:

  ...
  IGNORE 1 LINES;
  

  If the fields in the data text file are surrounded by characters like double-quotes, add the ENCLOSED BY clause like so:

  LOAD DATA LOW_PRIORITY INFILE '/tmp/data.txt'
  REPLACE INTO TABLE table1
  FIELDS TERMINATED BY '|'
  ENCLOSED BY '"'
  TERMINATED BY '\r\n'
  IGNORE 1 LINES;
  

  If the fields in the data text file are not in the same order or the file doesn't contain the same number of columns as the receiving table, a list of columns and their order needs to be given. Below is an example of how this might look:

  LOAD DATA LOW_PRIORITY INFILE '/tmp/data.txt'
  REPLACE INTO TABLE table1
  FIELDS TERMINATED BY '|'
  (col1, col4, col3);
  

  In this example, only three of the columns possible are contained in the data text file. They are in a different order than found in the table. So their respective column names from the table are listed in a comma-separated list within parentheses at the end of the statement.

Share on Google Plus

About Elmirakom

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.