merge multiple fields of csv file while running mysqlimport command

Question

I have a mysql table like this

+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| roll_no | varchar(50) | NO | MUL | NULL | |
| marks | varchar(100) | YES | | NULL | |
| academy | varchar(100) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+

the marks column can have multiple numbers separated by comma like this

45,67,78,80, # First Example
34,56, # Second Example

I have to run mysqlimport command such that the fields after the first fields till second-last fields should merge into marks column

I tried something like this

mysqlimport --fields-terminated-by=, --columns='roll_no, marks, academy' --ignore --local -u root -pxxxx result results.csv

But extra fields (after 3rd one) gets truncated. How to handle such scenarios where the csv fields are dynamic

• Colin 't Hart: Running on a unix-like system? Why not use some commands like cut or awk first?

Answer 1

Because it is a CSV file (Comma Separated Values), it thinks that the each of the marks is a different field. Your initial export should either separate values by a different char, like "#", or quot the values bu ".