# Loading JSON Data from a CSV File

> **📝 Note**: For more information on loading data from CSV Files, refer to [Load Data from CSV Files](https://docs.singlestore.com/db/v9.1/load-data/load-data-from-files/load-data-from-csv-files.md).

## 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


```

```output

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;


```

```output

+--------+-----------------------------------------------------+
| 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


```

```output

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;


```

```output

+--------+-----------------------------------------------------+
| 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


```

```output

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;


```

```output

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


```

```output

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;


```

```output

+--------+------------------------------------------------------+
| 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"} |
+--------+------------------------------------------------------+
```

***

Modified at: April 3, 2025

Source: [/db/v9.1/load-data/load-data-from-files/load-data-from-csv-files/loading-json-data-from-a-csv-file/](https://docs.singlestore.com/db/v9.1/load-data/load-data-from-files/load-data-from-csv-files/loading-json-data-from-a-csv-file/)

(An index of the documentation is available at /llms.txt)