CODEWITHSUNDEEP
mysqlcsv
Ben
Ben

Reputation: 2523

Row 1 doesn't contain data for all columns

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?

Upvotes: 8

Views: 42421

Answers (5)

Aashutosh Kumar
Aashutosh Kumar

Reputation: 831

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.

Upvotes: 0

isgoed
isgoed

Reputation: 772

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.

Upvotes: 0

Chris Palmer
Chris Palmer

Reputation: 31

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

Upvotes: 2

Enayat
Enayat

Reputation: 4042

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.

Upvotes: 0

jlyh
jlyh

Reputation: 701

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

Upvotes: 5

Related Questions