MySQL import from csv file with character exceptions

Question

I'm trying to import a CSV files into my MySQL table using the following query:

LOAD DATA INFILE 'C:\\machines.csv'
INTO TABLE `machines` 
CHARACTER SET latin1
FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 0 LINES;

However it give the following error

1406- Data too long for column 'technicalInfo' at row 10334

It seems that the issue is that the row contains a ⌀ character in the technicalInfo column.

Is it possible to remove/replace this character within the query?

(N.B. Obviously another solution would be to remove it from the csv file itself. The problem is that I am likely to be importing different versions of this file multiple times every day, so I'd rather build it in to the query.)

@abhinav Thanks. Looks like it's supposed to be "utf8" (without the dash). — Urbycoz
– Urbycoz, Commented Nov 10, 2011 at 11:34

chrishall78 · Accepted Answer · 2011-11-10 11:19:41Z

1

The problem is that the '⌀' character is not available in the latin1 character set and that is why you're getting the error. Can you change the character set to utf8 and see if you still experience the problem?

[1] MYSQL Error 1406 details: http://bugs.mysql.com/bug.php?id=18908

answered Nov 10, 2011 at 11:19

chrishall78

1587 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Urbycoz Over a year ago

Hmm...now I'm getting the following error: 1366- Incorrect string value: '\xEF\xBB\xBF0-0...' for columm 'technicalInfo' at row 1

chrishall78 Over a year ago

Also check that the machines table's character set is utf8 and not latin1.

Urbycoz Over a year ago

The machines table is now set to utf8, but I still get the same error.

Devart · Accepted Answer · 2011-11-10 11:26:05Z

0

You can change or remove that symbol, e.g. -

LOAD DATA INFILE 'C:\\machines.csv'
INTO TABLE `machines` 
CHARACTER SET latin1
FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 0 LINES
(column1, column2, @var1) -- specify column names here
SET column3 = REPLACE(@var1, '⌀', ''); -- replace it with '!'

answered Nov 10, 2011 at 11:26

Devart

120k23 gold badges172 silver badges189 bronze badges

1 Comment

Urbycoz Over a year ago

This give me the following error:

1270- Illegal mix of collations (latin_swedish_ci,IMPLICIT),(utf8_general_ci,COERCIBLE),(utf8_general_ci,COERCIBLE) for operation 'replace'

Collectives™ on Stack Overflow

MySQL import from csv file with character exceptions

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related