CODEWITHSUNDEEP
mysqlcsvmariadb
eozzy
eozzy

Reputation: 68650

Loading selected CSV columns in MySQL

I'm trying to import selected columns from a CSV to the DB using:

LOAD DATA LOCAL INFILE 'local/path/to/file.csv' 
INTO TABLE product 
(ItemName,ItemShortDescription,RetailPrice,Brand,Category,@PRICE_1,@PRICE_2,@PRICE_3,Weight,Length,Width,Height) 
FIELDS TERMINATED BY '|' 
ESCAPED BY '"'
LINES TERMINATED BY '\n'

.. so skipping the 3 columns from the CSV (PRICE_1,PRICE_2,PRICE_3)

product table:

CREATE TABLE `product` (
  `pid` int(255) NOT NULL AUTO_INCREMENT,
  `ItemName` varchar(255) DEFAULT NULL,
  `ItemShortDescription` varchar(255) DEFAULT NULL,
  `RetailPrice` varchar(255) DEFAULT NULL,
  `Brand` varchar(255) DEFAULT NULL,
  `Category` varchar(255) DEFAULT NULL,
  `Weight` varchar(255) DEFAULT NULL,
  `Length` varchar(255) DEFAULT NULL,
  `Width` varchar(255) DEFAULT NULL,
  `Height` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=11387 DEFAULT CHARSET=utf8mb4;

.. but it results in an error.

enter image description here

What am I doing wrong?

Upvotes: 0

Views: 38

Answers (1)

Shadow
Shadow

Reputation: 34232

As mysql documentation on load data says, the field list comes after the field/line delimiters.

LOAD DATA LOCAL INFILE 'local/path/to/file.csv' 
INTO TABLE product  
FIELDS TERMINATED BY '|' 
ESCAPED BY '"'
LINES TERMINATED BY '\n'
(ItemName,ItemShortDescription,RetailPrice,Brand,Category,@PRICE_1,@PRICE_2,@PRICE_3,Weight,Length,Width,Height)

Upvotes: 1

Related Questions