How to avoid white spaces being created while importing data from CSV file to MySQL table

Question

I am importing data from a CSV file into a MySQL table. When basic queries such as

 select * from Mapping where floor  = 'L1';

were not working, after lot of struggle I found that it is because of the white spaces and query

 select * from Mapping where floor  like '%L1%';

was giving me the expected results.

This is how I am loading my CSV to a table:

LOAD DATA LOCAL INFILE "../.../mapping.csv" INTO TABLE db.Mapping
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'   
    IGNORE 1 LINES

I am not sure where the white spaces are coming from? Am I loading my CSV in a correct manner? Is there something that I can do to avoid white spaces?

Update

I have tried as per the answers,

Query OK, 0 rows affected (0.00 sec)                                                                                                                                
Rows matched: 49  Changed: 0  Warnings: 0

It says no rows affected.

 select * from Mapping where floor  = 'L1';

is still not working, but

 select * from Mapping where floor  like '%L1%';

is giving the expected results.

Data of CSV file:

V,M,Floor
1,15,L1
2,14,L1
3,14,L1
4,16,L1
5,16,L1
6,6,L1
7,14,L1
8,13,L1
9,12,L1
10,11,L1
11,16,L1

See "Input Preprocessing" in the [manuals](https://dev.mysql.com/doc/refman/8.0/en/load-data.html) — Paul Spiegel, Dec 14 '19 at 13:17
See the second answer [here](https://stackoverflow.com/questions/4162362/mysql-how-can-i-use-rtrim-in-my-load-data-infile-query) — Paul Spiegel, Dec 14 '19 at 13:19
You might want to share a few lines of sample data, which results in records causing you trouble. — Abecee, Dec 14 '19 at 14:38
I have a csv file, is it possible to attach a csv file here? — chink, Dec 14 '19 at 14:39
You might want to for non-printing characters, e.g., `SELECT *, LENGTH(floor) FROM Mapping WHERE floor LIKE '%L1%';`. — Abecee, Dec 14 '19 at 15:31
No, I altered the position of columns, put floor as second and M as third column. There wasn't any issue then — chink, Dec 27 '19 at 05:43

Gordon Linoff · Answer 1 · 2019-12-14T13:28:54.203

2

Usually, I advocate loading data into staging tables where all columns are strings and then doing to cleaning in the database.

However, load data infile does have some nice features for cleaning data. If your extra characters are spaces, you can use trim(). The key is variables and SET:

LOAD DATA LOCAL INFILE "../.../mapping.csv"
    INTO TABLE db.Mapping 
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'   
    IGNORE 1 LINES
    (@floor, . . . )  -- list the rest of the columns here
    SET floor = TRIM(@floor);

If the errors are more complicated, then regular expressions are helpful:

SET floor = REGEXP_REPLACE(@floor, '[^A-Z0-9]', '')

edited Dec 14 '19 at 13:28

answered Dec 14 '19 at 13:19

Gordon Linoff

1,242,037
58
646
786

Not sure if that matters - but according to the [docs](https://dev.mysql.com/doc/refman/8.0/en/load-data.html) the column list should come right before `SET` – Paul Spiegel Dec 14 '19 at 13:26
`REGEXP_REPLACE` is not working in my version of MySQL ->`mysqld Ver 5.7.28-0` – chink Dec 16 '19 at 04:24

score 1 · Answer 2 · answered Dec 14 '19 at 13:18

1

You could always trim the whitespace after the data is inserted;

UPDATE db.Mapping SET floor = trim(floor);

answered Dec 14 '19 at 13:18

Simon R

3,732
4
31
39

How to avoid white spaces being created while importing data from CSV file to MySQL table

Update

2 Answers2