Loading selected CSV columns in MySQL

Question

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.

What am I doing wrong?

Shadow · Accepted Answer · 2018-02-10 00:59:36Z

1

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)

answered Feb 10, 2018 at 0:59

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

eozzy Over a year ago

Darn, that was it! Thanks! :)

Collectives™ on Stack Overflow

Loading selected CSV columns in MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related