MySQL Forums :: Newbie :: Load data infile not working


Advanced Search

Load data infile not working
Posted by: Colleen Boye ()
Date: July 15, 2013 04:52PM

I was just loading some data from a file and I'm getting some behavior I haven't seen before. It only loads the very first field in the first line and skips everything else. But instead of giving an error, it just gives a ton of warnings 1262/1263/1265. I experimented and it seems like it will incorrectly import *any text file* into *any table* without giving an error.

I need to straighten this out so that it will successfully import data into the correct table and abort with an error otherwise.

Here's my query (changing the table name as necessary):

load data local infile 'C:/Users/DaBoyes/Desktop/Temp.txt' into table materials 
fields terminated by '\t' lines terminated by '\r\n' ignore 1 lines;

Here's the text of my test file:

Quote

TreatNum EventNum TreatStart TreatEnd Conservator Evaluation Failed FailDate Qty Equipment SurfaceTemp SurfaceMoisture Temperature Humidity Wind Sun Rain Weather Notes RecordDate RecordCreator RecordStatus
1 \N 6/19/1979 4/23/1981 \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N now() cboye A
2 \N 5/25/1979 4/23/1981 \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N now() cboye A
3 \N 7/25/1979 4/23/1981 \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N now() cboye A

Here's the correct table that it should be able to import into (but doesn't):

CREATE TABLE `treatments` (
  `TreatNum` int(6) unsigned zerofill NOT NULL DEFAULT '000000',
  `EventNum` int(6) unsigned DEFAULT NULL,
  `TreatStart` datetime DEFAULT CURRENT_TIMESTAMP,
  `TreatEnd` datetime DEFAULT CURRENT_TIMESTAMP,
  `Conservator` varchar(45) DEFAULT NULL,
  `Evaluation` tinyint(1) NOT NULL DEFAULT '0',
  `Failed` tinyint(1) NOT NULL DEFAULT '0',
  `FailDate` date DEFAULT NULL,
  `Qty` int(3) unsigned DEFAULT NULL,
  `Equipment` varchar(100) DEFAULT NULL,
  `SurfaceTemp` int(3) unsigned DEFAULT NULL,
  `SurfaceMoisture` int(3) unsigned DEFAULT NULL,
  `Temperature` int(3) unsigned DEFAULT NULL,
  `Humidity` int(3) unsigned DEFAULT NULL,
  `Wind` tinyint(1) DEFAULT NULL,
  `Sun` tinyint(1) DEFAULT NULL,
  `Rain` tinyint(1) DEFAULT NULL,
  `Weather` varchar(200) DEFAULT NULL,
  `Notes` varchar(500) DEFAULT NULL,
  `RecordDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `RecordCreator` varchar(45) DEFAULT NULL,
  `RecordStatus` char(1) NOT NULL DEFAULT 'U',
  PRIMARY KEY (`TreatNum`),
  UNIQUE KEY `TreatStart_UNIQUE` (`TreatStart`),
  UNIQUE KEY `TreatEnd_UNIQUE` (`TreatEnd`),
  KEY `TreatEventNum` (`EventNum`),
  KEY `TrEventNum` (`EventNum`),
  FULLTEXT KEY `Equipment` (`Equipment`),
  FULLTEXT KEY `Weather` (`Weather`),
  FULLTEXT KEY `Notes` (`Notes`),
  CONSTRAINT `TrEventNum` FOREIGN KEY (`EventNum`) REFERENCES `events` (`EventNum`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And here's another table that should yield an error (but doesn't):

CREATE TABLE `materials` (
  `MatNum` int(6) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `MatName` varchar(30) NOT NULL,
  `MatType` varchar(20) DEFAULT NULL,
  `RecordDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `RecordCreator` varchar(45) DEFAULT NULL,
  `RecordStatus` char(1) NOT NULL DEFAULT 'U',
  PRIMARY KEY (`MatNum`),
  UNIQUE KEY `MatName_UNIQUE` (`MatName`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

And here's a sample of the warnings I get regardless of which table it is (these are the warnings for Materials):

Quote

0 row(s) affected, 11 warning(s): 1263 Column set to default value; NULL supplied to NOT NULL column 'MatName' at row 1 1265 Data truncated for column 'RecordDate' at row 1 1263 Column set to default value; NULL supplied to NOT NULL column 'RecordStatus' at row 1 1262 Row 1 was truncated; it contained more data than there were input columns 1263 Column set to default value; NULL supplied to NOT NULL column 'MatName' at row 2 1265 Data truncated for column 'RecordDate' at row 2 1263 Column set to default value; NULL supplied to NOT NULL column 'RecordStatus' at row 2 1262 Row 2 was truncated; it contained more data than there were input columns 1263 Column set to default value; NULL supplied to NOT NULL column 'MatName' at row 3 1265 Data truncated for column 'RecordDate' at row 3 1263 Column set to default value; NULL supplied to NOT NULL column 'RecordStatus' at row 3 Records: 3 Deleted: 0 Skipped: 3 Warnings: 11



Edited 1 time(s). Last edit at 07/15/2013 04:54PM by Colleen Boye.

Options: ReplyQuote


Subject Written By Posted
Load data infile not working Colleen Boye 07/15/2013 04:52PM
Re: Load data infile not working Barry Galbraith 07/15/2013 10:31PM
Re: Load data infile not working Colleen Boye 07/16/2013 02:55PM
Re: Load data infile not working Barry Galbraith 07/16/2013 07:43PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.