Row 1 doesn't contain data for all columns

Question

I am trying to upload a csv into MySQL using the Workbench, and so far all my attempts have proven fruitless.

I initially attempted to use the "Upload" function, but it complained about any null/empty fields I had.

I am now attempting to use this function:

LOAD DATA infile 'C:\\temp\\SubEq.csv'
INTO TABLE cas_asset_register.subequipment
fields terminated BY ','
lines terminated BY '\n'
(seid, parentme, @vparentse, name, status, description, equipmenttype, comments, removed, active, @vsupplierid)
SET
ParentSE = nullif(@vparentse,''),
SupplierId = nullif(@vsupplierid,'')
;

But again, it appears to be complaining about (possibly) the same thing:

Error Code: 1261. Row 1 doesn't contain data for all columns

I have had a look at the answers for this and this question, but neither have solved my issue.

The table create query:

CREATE TABLE `subequipment` (
  `SEId` int(11) NOT NULL AUTO_INCREMENT,
  `ParentME` int(11) DEFAULT NULL,
  `ParentSE` int(11) DEFAULT NULL,
  `Name` varchar(255) DEFAULT NULL,
  `Status` varchar(100) DEFAULT NULL,
  `Description` varchar(255) DEFAULT NULL,
  `EquipmentType` int(11) DEFAULT NULL,
  `Comments` text,
  `Removed` tinyint(1) NOT NULL DEFAULT '0',
  `Active` tinyint(1) DEFAULT '1',
  `SupplierId` int(11) DEFAULT NULL,
  PRIMARY KEY (`SEId`),
  UNIQUE KEY `Unique_Constraint_ME` (`Name`,`ParentME`,`Active`),
  UNIQUE KEY `Unique_Constraint_SE` (`Name`,`ParentSE`,`Active`),
  KEY `ParentME` (`ParentME`),
  KEY `ParentSE` (`ParentSE`),
  KEY `EquipmentType` (`EquipmentType`),
  KEY `fk_subequipment_supplierequipment` (`SupplierId`),
  KEY `fk_subequipment_status_idx` (`Status`),
  CONSTRAINT `fk_subequipment_majorequipment` FOREIGN KEY (`ParentME`) REFERENCES `majorequipment` (`MEId`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_subequipment_status` FOREIGN KEY (`Status`) REFERENCES `componentstatus` (`StatusName`) ON UPDATE CASCADE,
  CONSTRAINT `fk_subequipment_subequipment` FOREIGN KEY (`ParentSE`) REFERENCES `subequipment` (`SEId`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_subequipment_supplierequipment` FOREIGN KEY (`SupplierId`) REFERENCES `supplierinfo_equipment` (`SupplierId`) ON UPDATE CASCADE,
  CONSTRAINT `fk_subequipment_userdefinedcode` FOREIGN KEY (`EquipmentType`) REFERENCES `userdefinedcode` (`UDCId`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

As you can see from my upload query, I am expecting "ParentSE" and "SupplierId" to be empty, even though they are foreign key fields.

Each line of the csv is properly indexed (i.e. there are enough fields to match the table):

1,1,,P7YCGPF,Abg va hfr,Nfcver Npre Yncgbc,13,"Qngr npdhverq: 61/52/7566
Zbqry: 0297T
Frevny Ahzore: YKE057551588125P16156",0,1,

What's going wrong?

Object

are you saying query from db to csv what tools you used from query to csv – Awn

I'm sorry @reds I don't quite follow what you're asking? – Object

@Object Looks like the error is coming from the empty string values in your .csv files. The Error 1261 is a result of MySQL not having anything to insert rather than being able to insert NULL. So if your .csv file is something like: 'a', 'b', 'c', '', 'e' MySQL does not know what to do with the '' value unless it is changed from '' to NULL like: 'a', 'b', 'c', 'NULL', 'e'. I am not sure I am understanding your question correctly but if this answer seems correct I am willing to help further. – Smiga

@Smiga I feel; like you're probably on the right track, as I am expecting at least two of these values to be empty (hence the SET ParentSE = nullif(@vparentse,'') sections). I'm just not sure if this is being implemented correctly – Object

Your NULLIF statement looks correct so the only other thing I could think of is double checking the .csv files to make sure that they do indeed contains those '' empty strings like you expect. Once you have confirmed this I cannot see why it would be going wrong. You could write a script that parses your .csv files to and replaces those '' with NULL, but I am not sure if this is viable for you. Sorry I can't help you further on the MySQL side but I'd look into it further and see what you can find. If nothing I could help with a program to replace '', but that's up to you. – Smiga

What do you mean by this 1,1,, double comma in the csv file – Whiting

Answer 1

Does your data in any of your fields in the csv file contain commas? This screws up the field termination criteria when you're trying to upload it into MySQL. If this is the case, try saving it as a tab delimited txt file and replacing

fields terminated BY ','

with

fields terminated BY '\t'

Sorry if this is not the right answer to your question, I wanted to post this as a comment but my reputation is not high enough :P

Worldling

No where in the csv are there any commas where there ought not be. As you can see from the excerpt, the error is throwing at "Row 1", which I have included for comparison. however, I will save it as a tab-delimited and try again. – Object

Answer 2

I was getting the same error for the last line in my CSV file. This may not solve your problem, but it solved mine and hope it helps someone.

For me I needed a carriage return character at the very end of the file to get onto a new line. Imports without errors now

Hannis

I'm rather late to the thread, but this works for me. Thank you! – Caracul

Answer 3

I had the same problem and comparing the columns in the CSV file and the table in my database resolved the issue. The number of columns, the column types, and in some cases empty values for string types (e.g., "" for string type) should be the same.

Bernini

Answer 4

For me the problem was that the file had to be tab-separated. I read some users reported that the file had to be semicolon-separated.

Photocopier

Answer 5

Try below.

LOAD DATA INFILE '/var/lib/mysql-files/data_file.csv'
INTO TABLE dbcs_info
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(col1, col2, col3, col4, col5)
SET 
    col1 = NULLIF(col1, ''),
    col2 = NULLIF(col2, ''),
    col3 = NULLIF(col3, ''),
    col4 = NULLIF(col4, ''),
    col5 = NULLIF(col5, '');

col1 = NULLIF(col1, '')

this sets value to null, if Field/column value is empty string.

Stoecker