Load data stored in a text file into a table.

Syntax

LOAD DATA [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[ (col_name, ...) ]
[SET  col_name = expr,...]

If no FIELDS or LINES clause is specified, then MemSQL uses the same defaults as MySQL::

FIELDS TERMINATED BY ‘\t’ ENCLOSED BY '’ ESCAPED BY ‘\’ LINES TERMINATED BY ‘\n’ STARTING BY '’

The SET clause allows you to set columns to specific values. For example, if your input file has 9 columns but the table has a 10th column called foo, you can add SET foo=0.

Notes

The behavior of MemSQL’s LOAD DATA command has several functional differences from MySQL’s command:

• LOAD DATA will load the data into MemSQL in parallel to maximize performance. This makes LOAD DATA in MemSQL much faster on machines with a larger number of processors.
• LOAD DATA supports loading compressed .gz files.
• The only supported charset_name is utf8.
• This command must be run on the master aggregator or a child aggregator node (see Node_Requirements).

The mysqlimport utility can also be used to import data into MemSQL. mysqlimport uses LOAD DATA internally.

Examples

If the order of columns in the table is different from the order in the source file, you can name them explicitly. In this example, the columns are loaded in reverse order:

load data infile 'foo.tsv' into table foo (fourth, third, second, first);

You can also skip columns in the source file using the “@” sign. In this example only the first and fourth columns are imported into table foo.

load data infile 'foo.tsv' into table foo (bar, @, @, baz);

The default column delimiter is the tab (“t”) character, ASCII code 09. You can specify a different delimiter, even multi-character delimiters, with the COLUMNS TERMINATED BY clause:

load data infile 'foo.csv' into table foo COLUMNS TERMINATED BY ',';

load data infile 'foo.oddformat' into table foo COLUMNS TERMINATED BY '|||';

Was this Article Helpful?

Thanks For Your Feedback!