2

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
Allen
  • 27
  • 3

1 Answers1

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);
...
MyICQ
  • 987
  • 1
  • 9
  • 25
  • 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. – Allen Mar 08 '22 at 08:20
  • 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. – MyICQ Mar 08 '22 at 10:32