happymacarts
happymacarts

Reputation: 2595

SQL Error [1054] [42S22]: Unknown column ' ' in 'field list'

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

Answers (2)

happymacarts
happymacarts

Reputation: 2595

Solved!

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

Shiva T. Kota
Shiva T. Kota

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

Related Questions