MySQL: Correct LOAD DATA INFILE Syntax

[oddz]

I’m currently attempting to load the below csv file into a table. When I run the below command no errors are received. The csv was generated via Excel on a mac and mysql is running on a Debian Linux vm. What am I doing incorrectly?

File:
migrate_data.example.csv (342 Bytes)

Table

CREATE TABLE tmp_xxx_bronto_waitlist (
  store BIGINT UNSIGNED NOT NULL,
  email VARCHAR(255) NOT NULL,
  sku VARCHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Command:

mysql > LOAD DATA INFILE '/var/www/local.site/play/migrate_data.example.csv' INTO TABLE tmp_xxx_bronto_waitlist FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY "\r\n" (@store,email,sku) SET store = CAST(@store AS UNSIGNED);

Command Response:

Query OK, 1 row affected (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

Thanks

[oddz]

I changed the table schema and load command some what with the same result (no errors). My guess is the line ending is incorrect…

Schema:

CREATE TABLE tmp_xxx_bronto_waitlist (
  store VARCHAR(9) NOT NULL,
  store_id smallint(5) UNSIGNED NULL,
  email VARCHAR(255) NOT NULL,
  customer_id int(10) UNSIGNED NULL,
  sku VARCHAR(64) NOT NULL,
  product_id int(10) UNSIGNED NULL,
  INDEX(store_id),
  INDEX(customer_id),
  INDEX(product_id),
  FOREIGN KEY(store_id) REFERENCES bed_core_store(store_id),
  FOREIGN KEY(customer_id) REFERENCES bed_customer_entity(entity_id),
  FOREIGN KEY(product_id) REFERENCES bed_catalog_product_entity(entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Command:

mysql> LOAD DATA INFILE '/var/www/local.site/play/migrate_data.example.csv' 
INTO TABLE tmp_xxx_bronto_waitlist 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY "\r\n" 
(@store,@store_id,@email,@customer_id,@sku,@product_id) 
SET store = @store,
    store_id = (SELECT store_id FROM bed_core_store WHERE store_id = CAST(@store AS UNSIGNED)),
    email = @email,
    customer_id = (SELECT entity_id FROM bed_customer_entity WHERE email = @email),
    sku = @sku,
    product_id = (SELECT entity_id FROM bed_catalog_product_entity WHERE sku = @sku);

[oddz]

The problem was on mac excel saves csv files with \r line ending. Changed “\r\n” to just “\r” and the rows were imported properly.

Excel and line endings - Nice R Code

mysql> LOAD DATA INFILE '/var/www/local.site/play/migrate_data.example.csv' 
INTO TABLE tmp_xxx_bronto_waitlist 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY "\r" 
(@store,@store_id,@email,@customer_id,@sku,@product_id) 
SET store = @store,
    store_id = (SELECT store_id FROM bed_core_store WHERE store_id = CAST(@store AS UNSIGNED)),
    email = @email,
    customer_id = (SELECT entity_id FROM bed_customer_entity WHERE email = @email),
    sku = @sku,
    product_id = (SELECT entity_id FROM bed_catalog_product_entity WHERE sku = @sku);

[oddz]

There was another issue preventing the columns from being populated properly which was fixed by only capturing the values of the csv file. Also casting the data in the csv to the proper data type for comparison in the conditions.

mysql> LOAD DATA INFILE '/var/www/local.site/play/migrate_data.example.csv' 
INTO TABLE tmp_xxx_bronto_waitlist 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY "\r" 
(@store,@email,@sku)
SET store = @store,
    store_id = (SELECT store_id FROM bed_core_store WHERE store_id = CAST(@store AS UNSIGNED)),
    email = @email,
    customer_id = (SELECT entity_id FROM bed_customer_entity WHERE email = CAST(@email AS CHAR(255))),
    sku = @sku,
    product_id = (SELECT entity_id FROM bed_catalog_product_entity WHERE sku = CAST(@sku AS CHAR(64)));