How to import csv data with json type fields into mysql database. The mysql table has columns of the corresponding json type

user_17363508 0
Viewed 483

I have a mysql data table and a csv file, the table has a json type column, and the csv file has a corresponding json type field, I use the "load data local infile..." method to import the csv file into mysql , there is a problem with this process.

here is my datasheet details:

mysql> desc test;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| content | json         | YES  |     | NULL    |                |
| address | varchar(255) | NO   |     | NULL    |                |
| type    | int          | YES  |     | 0       |                |
+---------+--------------+------+-----+---------+----------------+

and my sql statement:

mysql> load data local infile '/Users/kk/Documents/test.csv'
    -> into table test
    -> fields terminated by ','
    -> lines terminated by '\n'
    -> ignore 1 rows
    -> (id,address,content,type);
ERROR 3140 (22032): Invalid JSON text: "The document root must not be followed by other values." at position 3 in value for column 'test.content'.

My csv file data is as follows

"id","address","content","type"
1,"test01","{\"type\": 3, \"chain\": 1, \"address\": \"test01\"}",1
2,"test02","{\"type\": 3, \"chain\": 2, \"address\": \"test02\"}",1
csv mysql json
1 Answers

If you are able to hand-craft a single insert statement that works (example here) you could go via a preprocessor written in a simple scripting language. Python, AutoIT, PowerShell, ... Using a preprocessor you have more control of fields, quoting, ordering etc compared to direct import in MySQL.

So for example (assuming you have used Python)

python split.py /Users/kk/Documents/test.csv > /tmp/temp.sql
mysql -h myhostname -u myUser mydatabase < temp.sql

where temp.sql would be something like

insert into test (content, address, type) values (`{"type":3,"chain":1,"address":"test01"}`, `test01`, 1);
...
user_18180590
Related