I have an file with Currency Sign delimeter : 20130217¤18122¤14 20130217¤62152¤14 20130217¤54512¤10
LOAD DATA INFILE '$file'
REPLACE INTO TABLE $my_table
FIELDS TERMINATED BY '¤'
IGNORE 1 LINES
The table has columns date, id, num.
The error is #1292 - Incorect date value: '20130217¤18122¤14' for column 'DATE' at row 1
The reason you're getting this is because non-ASCII terminators are not fully supported.
See this error message in the docs:
Error: 1638 SQLSTATE: HY000 (WARN_NON_ASCII_SEPARATOR_NOT_IMPLEMENTED) Message: Non-ASCII separator arguments are not fully supported
The good news is that multi-character separators are supported, so you could use sed
or something to replace your ¤
with #%&%#
or something equally unique, and use that as a separator:
The FIELDS TERMINATED BY, LINES STARTING BY, and LINES TERMINATED BY values can be more than one character.
(see here)