Reputation: 2595
This error has been driving me crazy for a few days now
this is one row of about 10 in a 1000+ row file that will not import to my table.
I have attempted to add all of the columns in the insert statement, I have attempted removing columns, I have copied into notepad to make sure there were not strange characters, I have checked line endings, tried replacing spaces, removing backticks and nothing
Can Someone please point to a solution.
to make this even stranger, Some of the lines when I run this from client and it outputs the error with the sql if I copy that output and paste it in a new window it will run fine. I have attempted running from multiple clients(adminer, dbeaver, mysqlworkbench and phpmysql) and It returns the same errors in the same locations
DDL
CREATE TABLE `xxx_projects` (
`id` int(3) unsigned zerofill NOT NULL ,
`status` int(3) unsigned zerofill NULL DEFAULT 1,
`name` varchar(100) NOT NULL,
`gp_id` varchar(100) DEFAULT NULL,
`project_type` varchar(15) DEFAULT 'SFH',
`project_type_id` int(11) NOT NULL,
`master_plan_id` int(11) DEFAULT '0',
`client_id` int(3) unsigned zerofill NOT NULL,
`city_id` int(3) unsigned zerofill NOT NULL,
`image_id` int(11) DEFAULT NULL,
`lat` decimal(9,6) NOT NULL,
`lon` decimal(9,6) NOT NULL,
`kml_info` varchar(1000) DEFAULT NULL,
`url` varchar(1000) DEFAULT NULL,
`last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`closeout_date` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=922 DEFAULT CHARSET=utf8;
Insert Statement
INSERT INTO `xxx_projects`
(`id`, `status`, `name`, `project_type_id`, `client_id`, `city_id`, `lat`, `lon`)
VALUES (563,0,'Santa Ana Collection',10,135,256,33.73369 , -117.92058 );
The Error Message
SQL Error [1054] [42S22]: Unknown column ' ' in 'field list'
Upvotes: 3
Views: 9194
Reputation: 2595
Character 160 (non-breaking space) is the Culprit
In my original source file the user had been copy/pasting into excel from web pages to grab the data
I failed to sanitize that correctly and ended up with non-breaking spaces in my sql instead of char 32.
To resolve this in the original excel file I used this
SUBSTITUTE(JackedUpString,CHAR(160),CHAR(32))
Hope this helps someone else Since I did not find anything in SO related to this one
This Article was useful as well
Upvotes: 2
Reputation: 472
You declared your status row as int but you are trying to put a character in it. Note that '0' is a character and 0 is an integer. From my experience with mysql I think this is the problem . Hope this solves your issue. Also if I were you, I'd declare 'lat' and 'lon' as varchar.
Upvotes: 0