MySQL LOAD DATA INFILE is missing columns

I’m testing a small sample of a CSV file to be uploaded into MySQL.

However the LOAD DATA INFILE command is missing the first column and I don’t know how to get it in.

It always puts my second column from CSV into the first column.

Here is the database;


; Pseduocode Database
id (primary key, auto_increment, int)
OfferProcessType (var 32)
TradingName (var 32)

Now, here is the CSV file.


OfferProcessType,TradingName
E,
A,My Travel

And here is my MySQL call;


LOAD DATA INFILE 'testy.csv' INTO TABLE `testy`
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\
'
;

Problems;

  1. For some reason this call does not pull in my first column. How do I get it to read the first column and put it into the database?

  2. How do I ensure that the columns in the CSV file are the same as those in MySQL?

  3. How do I make sure MySQL does not import the column names, or any blank rows (in my example the second row is empty).

I am currently adding this line: “IGNORE 2 LINES;” into my MySQL call.

This removes the first 2 rows, but I don’t know if this is the right way to do it.

Okay, according to the docs, I can specify column names, but I don’t know how to use it.

Everytime I try it, it always skips the first column.

Example;


LOAD DATA INFILE 'testy.csv' 
  INTO TABLE `testy`
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\
'
  IGNORE 2 LINES  
  (id, OfferProcessType, TradingName) 
;

The last line is meant to be the columns, but it seems to completly ignore this.

The ignore 2 lines seems to work for hiding the first 2 rows.

I’ve got it working now!


# Working version
LOAD DATA INFILE 'testy.csv'  IGNORE INTO TABLE testy 
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\
'
IGNORE 2 LINES  
(OfferProcessType,TradingName)

Yey!

congrats for working it out, and thanks for updating us on the solution

:slight_smile: