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?