Loading a CSV file with inconsistent spaces after commas

Question

I want to load a CSV file using LOAD DATA INFILE command, but the spaces after the commas are inconsistent i.e. there are commas which are followed by a space and commas that aren't.

I tried using FIELDS TERMINATED BY "," directive, but some of the fields in the resulting table contained a leading space; If the input was

abc,def, ghi, klm

then after the loading my table had

column1 = 'abc'
column2 = 'def'
column3 = ' ghi'
column4 = ' klm'

Note that columns 3 and 4 contain a leading space.

I want my columns to not contain leading spaces. How should I do that?

Answer 1

Would this work?

LOAD DATA INFILE 'file.csv'
INTO TABLE t1 (column1, @col2, @col3, @col4)
SET
  column2 = TRIM(@col2),
  column3 = TRIM(@col3),
  column4 = TRIM(@col4)

Answer 2

You can use a short sed replace pattern to do it:

sed -i 's/, /,/g' file.csv

After that, file.csv (its content was "abc,def, ghi, klm") contains:

abc,def,ghi,klm