MySQL in a Nutshell

Name

LOAD DATA INFILE

Synopsis

LOAD DATA [LOW_PRIORITY|CONCURRENT] [LOCAL] INFILE 'filename'

    [REPLACE|IGNORE] INTO TABLE table

    [FIELDS [TERMINATED BY 'C'] 

        [[OPTIONALLY] ENCLOSED BY 'C']

        [ESCAPED BY 'C']]

    [LINES [STARTING BY 'C'] [TERMINATED BY 'C']]

    [IGNORE count LINES]

    [(column, . . . )]

You can use this statement to import organized data properly from a text file into a table on the server. The data text file must be located either in the directory of the database into which the data is to be imported or in a directory on the server's filesystem with the file permission set to read for all filesystem users. If the text file is on the client's filesystem, the LOCAL flag must be given. This feature must be enabled on both the client and the server with --local-infile=1. See Chapter 10 for more information on these settings. Here is a basic example of how you can use this statement:

LOAD DATA INFILE '/tmp/catalog.txt'
   INTO TABLE catalog
   FIELDS TERMINATED BY '|'
   LINES TERMINATED BY '\n';

In this example, the file to be loaded is in the /tmp directory and is called catalog.txt. The data contained in the file is to be inserted into the catalog table in the current database in use. Each field in the text file is terminated with a vertical bar character. The rows of data in the text file are on separate lines. They are separated by a newline character (\n). This is the default for a Unix text file. For DOS or Windows systems, lines are usually terminated with a \n\r, signifying a newline ...