I am trying to populate a table on my database with some rows containing some latin characters specifically "Ñ". This list is from an excel where I convert to a csv file.
My CSV file has contents like these:
"1619","137601000","CITY OF LAS PIÑAS","METRO MANILA","NCR","National Capital Region","MM" "1620","137602000","CITY OF MAKATI","METRO MANILA","NCR","National Capital Region","MM" "1627","137604000","CITY OF PARAÑAQUE","METRO MANILA","NCR","National Capital Region","MM"
I load this list to my target table with the following command:
LOAD DATA LOCAL INFILE '/my/path/to/file.csv' INTO TABLE mytable FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
I've also tried adding the CHARACTER SET to my load statement, but I've got same result:
LOAD DATA LOCAL INFILE '/my/path/to/file.csv' INTO TABLE mytable CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
But after loading, when I checked on the content of my table, fields that contains "Ñ" are being truncated.
| 1619 | 137601000 | CITY OF LAS PI | METRO MANILA | NCR | National Capital Region | MM | | 1620 | 137602000 | CITY OF MAKATI | METRO MANILA | NCR | National Capital Region | MM | | 1627 | 137604000 | CITY OF PARA | METRO MANILA | NCR | National Capital Region | MM |
Tried some of the suggestion on this resource but still to no avail.
I tried to extract the content of the table as it may only be a representation problem, but field with "Ñ" were really truncated.
Below are helpful infos that may help in resolving this issue.
I created my database with with this command:
CREATE DATABASE myDB DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci
and my table with this:
CREATE TABLE `location` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`code` int(11) NOT NULL,
`city` varchar(60) NOT NULL,
`province` varchar(30) NOT NULL,
`region` varchar(4) NOT NULL,
`regionname` varchar(40) NOT NULL,
`area` varchar(3) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
OS Variant and Version
CentOS Linux release 7.1.1503 (Core)
NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"
CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"
CentOS Linux release 7.1.1503 (Core)
CentOS Linux release 7.1.1503 (Core)
MySQL Variant and Version
- mysql Ver 15.1 Distrib 5.5.41-MariaDB, for Linux (x86_64) using readline 5.1
Table column character setup
+-------------+--------------------+-----------------+ | column_name | character_set_name | collation_name | +-------------+--------------------+-----------------+ | id | NULL | NULL | | code | NULL | NULL | | city | utf8 | utf8_unicode_ci | | province | utf8 | utf8_unicode_ci | | region | utf8 | utf8_unicode_ci | | regionname | utf8 | utf8_unicode_ci | | area | utf8 | utf8_unicode_ci | +-------------+--------------------+-----------------+
My Database setup
+-------------+----------------------------------------------------------------------------------------------+ | Database | Create Database | +-------------+----------------------------------------------------------------------------------------------+ | myDbName | CREATE DATABASE `myDbName` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ | +-------------+----------------------------------------------------------------------------------------------+