Important

Self-managed SingleStore will soon transition from version 9.1 RC to version 10. This new semantic versioning scheme will provide SingleStore with finer control over engine and feature releases that were not possible with the current versioning scheme.

In the interim, SingleStore 9.1 RC can be used to preview, evaluate, and provide feedback on the new and upcoming features in SingleStore 10 prior to its general availability. Ahead of this transition, SingleStore 9.0 is recommended for production workloads, which can later be upgraded to SingleStore 10.

Loading JSON Data from a CSV File

Examples

To use an ENCLOSED BY <char> as a terminating field, a TERMINATED BY clause is needed. For clarity, instances of an ENCLOSED BY <char> appearing within a field value can be duplicated, and they will be understood as a singular occurrence of the character.

If an ENCLOSED BY "" is used, the quotes are treated as follows:

"The ""New"" employee" → The "NEW" employee
The "New" employee → The "NEW" employee
The ""NEW"" employee → The ""NEW"" employee

Example 1

An ENCLOSED BY clause is required when a csv file has a JSON column enclosed with double quotation marks (" ").

SQL

CREATE TABLE employees(emp_id int, data JSON);

csv file contents

emp_id,data
159,"{""name"": ""Damien Karras"", ""age"": 38, ""city"": ""New York""}"

SQL

LOAD DATA INFILE '/tmp/<file_name>.csv' INTO TABLE employees
     FIELDS TERMINATED BY ','
     ENCLOSED BY '"'
     IGNORE 1 LINES;

SELECT * FROM employees;

+--------+-----------------------------------------------------+
| emp_id | data                                                |
+--------+-----------------------------------------------------+
|    159 | {"age":38,"city":"New York","name":"Damien Karras"} |
+--------+-----------------------------------------------------+

Example 2

An ESCAPED BY clause is required when a character is specified as an escape character for a string. The example below uses a backslash (\) as the escape character.

csv file contents

emp_id,data
298,"{\"name\": \"Bill Denbrough\", \"age\": 25, \"city\": \"Bangor\"}"

SQL

LOAD DATA INFILE '/tmp/<file_name>.csv' INTO TABLE employees
     INTO TABLE employees
     FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
     IGNORE 1 LINES;

SELECT * FROM employees;

+--------+-----------------------------------------------------+
| emp_id | data                                                |
+--------+-----------------------------------------------------+
|    298 | {"age":25,"city":"Bangor","name":"Bill Denbrough"}  |
|    159 | {"age":38,"city":"New York","name":"Damien Karras"} |
+--------+-----------------------------------------------------+

Example 3

This example will fail as the JSON field in the csv file is not in the correct format.

csv file contents

emp_id,data
410,"{"name": "Annie Wilkes", "age": 45, "city":"Silver Creek"}"

SQL

LOAD DATA INFILE '/tmp/<file_name>.csv' INTO TABLE employees
     FIELDS TERMINATED BY ','
     ENCLOSED BY '{'
     IGNORE 1 LINES;

ERROR 1262 (01000): Leaf Error (127.0.0.1:3307): Row 1 was truncated; it contained more data than there were input columns

Example 4

An ENCLOSED BY clause is required when a csv file has a JSON column enclosed with curly brackets ({ }).

csv file contents

emp_id,data
089,{"name": "Wilbur Whateley","age": 62,"city": "Dunwich"}

SQL

LOAD DATA INFILE '/tmp/<file_name>.csv' INTO TABLE employees
     FIELDS TERMINATED BY ','
     ENCLOSED BY '{'
     IGNORE 1 LINES;

SELECT * FROM employees;

+--------+------------------------------------------------------+
| emp_id | data                                                 |
+--------+------------------------------------------------------+
| 298    | {"age":25,"city":"Bangor","name":"Bill Denbrough"}   |
| 159    | {"age":38,"city":"New York","name":"Damien Karras"}  |
|  89    | {"age":62,"city":"Dunwich","name":"Wilbur Whateley"} |
+--------+------------------------------------------------------+

Loading JSON Data from a CSV File

On this page

Examples

Was this article helpful?

On this page

Was this article helpful?