Skip to content

Insert csv file

This document will guide you how to import the csv format data when connecting to MatrixOne Intelligence on the MySQL client.

When using MatrixOne Intelligence, it is supported to import csv data files located on theclient host using the LOAD DATA LOCAL syntax to the MatrixOne Intelligence cluster. For detailed syntax, please refer to LOAD DATA Syntax Introduction.

Note: CSV (comma-separated value) files are a special file type that can be created or edited in Excel. CSV files do not store information in multiple columns, but use comma-separated form. The CSV format available to MatrixOne must comply with theRFC4180 standard.

Syntax Structure

LOAD DATA LOCAL
INFILE 'file_name'
INTO TABLE tbl_name
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[PARALLEL {'TRUE' | 'FALSE'}]

Import data using the Load data local command in MySQL Client

Note: When using the Load data local command, the data file must be located on the server where the MySQL client that executes the statement is located.

Steps

  1. Create the corresponding data table in MatrixOne Intelligence.

  2. Copy the data file to the server where the MySQL client resides.

  3. Connect to MatrixOne Intelligence using the MySQL client, for example:

    mysql -h freetier-01.cn-hangzhou.cluster.matrixonecloud.cn -P 6001 -u 585b49fc_852b_4bd1_b6d1_d64bc1d8xxxx:admin:accountadmin -p --local-infile

  4. Execute the LOAD DATA LOCAL command in the MySQL client:

    mysql> LOAD DATA LOCAL INFILE '/tmp/xxx.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n";

limit

Loading the csv format supports JSON type, but you need to make sure that JSON does not contain field termination symbols. If JSON contains field termination symbols, then JSON needs to be wrapped in double quotes. For example:

  • Correct example: "{"a":1, "b":2}", 2
  • Error example: {"a":1, "b":2}, 2