CODEWITHSUNDEEP
mysqlcsvimport
Urbycoz
Urbycoz

Reputation: 7421

MySQL import from csv file with character exceptions

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.)

Upvotes: 1

Views: 4165

Answers (2)

Devart
Devart

Reputation: 122032

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 '!'

Upvotes: 0

chrishall78
chrishall78

Reputation: 158

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

Upvotes: 1

Related Questions