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

Question

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

MyICQ · Accepted Answer · 2022-03-07 07:05:25Z

0

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);
...

answered Mar 7, 2022 at 7:05

MyICQ

1,1761 gold badge12 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Allen Over a year ago

I found the cause of the error. There is a , separator in the json field, and the csv file fields are also separated by ,. The fields terminated by , of the import statement will separate the content of the json field and cause an error. So I need to replace the delimiter of the csv file with another delimiter.

MyICQ Over a year ago

Yes, CSV is a weak format that way. Which is why I often choose the preprocessor way, because I can implement regular expressions that take the JSON string regardless of separator etc. Pretty much no matter what you do, you risk braking the import by JSON content.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related