CODEWITHSUNDEEP
mysqlsqlcsvimport
kk_
kk_

Reputation: 292

MySQL CSV-Import Can't match values of a column

I get the following situation: I imported a .csv into mysql via "LOAD DATA LOCAL INFILE".

Everything works fine, but one column is kinda broken.

It's filled with "category-names" like 'LFP-Graphic Art' and 'Papiere, Blöcke, Formulare'.

If I look for a entry in the table with a simple statement, I get zero findings:

SELECT * FROM t_category
WHERE category = 'LFP-Graphic Art';

If I just let it show everthing in the table, I can see that there are indeed entries with category 'LFP-Graphic Art'.
I also already looked for whitespaces, but that doesn't seem to be the problem.

I guess something went wrong with the Import?
I formatted the .csv file in UTF8 without BOM.

Edit:
When I update the dataset and just delete a character, then the select-statement works fine.

Example line from the csv:

active;supplier;SUPPLIER_AID;categorie
1;kb;10101;FP-Graphic Art

And the MySQL-table:

t_import

name, type, collation
active, tinyint(4), latin1_general_ci
supplier, char(255), latin1_general_ci
SUPPLIER_AID, char(255), latin1_general_ci
category, varchar(255), utf8_general_ci

Upvotes: 0

Views: 428

Answers (2)

Scoutman
Scoutman

Reputation: 1630

Maybe it works with a TRIM(). I think there is any hidden character.

SELECT * FROM t_category WHERE TRIM(category) = TRIM('LFP-Graphic Art');

Is there is a line end problem you can update the field.

-- with a \n line end (Unix)
UPDATE t_category SET category = TRIM(BOTH '\n' from category)
-- with a \n\r line end (Windows)
UPDATE t_category SET category = TRIM(BOTH '\n\r' from category)

When it is possible to load the data again, add the 'terminated by':

LOAD DATA .... LINES TERMINATED BY '\n'
-- or
LOAD DATA .... LINES TERMINATED BY '\n\r'

Upvotes: 0

Lelio Faieta
Lelio Faieta

Reputation: 6663

Try to check the 'line terminated by' statement. Since the field with issues is the last one it is possible that you are loading incorrectly the carriage return char.

Upvotes: 1

Related Questions