MySQL load file

When you use the Select ... with load DATA infile. into outfile the fields and processing options for the two commands must match when the data in a database is written into a file and then immediately read back to the database, otherwise, LOAD data infile will not interpret the contents of the file correctly. Assume that you use SELECT ... into OutFile writes a comma-delimited field to a file:

Mysql> SELECT * FROM table1 to OUTFILE ' data.txt '
Fields TERMINATED by ', '
From ...

In order to read the comma-delimited file back, the correct statement would be:

mysql> LOAD DATA INFILE ' data.txt ' into TABLE table2
Fields TERMINATED by ', ';

Conversely, if you try to read the file with the statement shown below, it will not work because it commands load DATA infile to find the locator between the fields:

mysql> LOAD DATA INFILE ' data.txt ' into TABLE table2
Fields TERMINATED by ' \ t ';

The possible result is that each input row is interpreted as a single field.

The LOAD DATA infile can be used to read files obtained from external sources. For example, a file in dBASE format will have a comma-delimited field surrounded by double quotation marks. If the lines in the file are terminated by a newline character, the command shown below describes the field and row processing options that you will use to mount the file:

mysql> LOAD DATA INFILE ' data.txt ' into TABLE tbl_name
Fields TERMINATED by ', ' enclosed by ' "'
LINES TERMINATED by ' \ n ';

Any field or row processing option can specify an empty string ('). If not empty, the fields [optionally] enclosed by and fields escaped by value must be a single character. Fields TERMINATED by and lines TERMINATED by value can be more than one character. For example, write a line terminated by a carriage return newline pair (CR+LF), or read a file containing such a line, specifying a lines TERMINATED by ' \ r \ n ' clause.

For example: Load data infile ' data.txt ' into table user_info fields terminated by ' # ' enclosed by ' "lines terminated by ' \ r \ n ';

MySQL load file