CODEWITHSUNDEEP
mysqlsqlcsv
chink
chink

Reputation: 1643

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

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

Upvotes: 3

Views: 1766

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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]', '')

Upvotes: 2

Simon R
Simon R

Reputation: 3772

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

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

Upvotes: 1

Related Questions