MySQL LOAD DATA INFILE is missing columns

[worchyld]

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 '\n'
;

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.

[worchyld]

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 '\n'
  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.

[worchyld]

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 '\n'
IGNORE 2 LINES  
(OfferProcessType,TradingName)

Yey!

[r937]

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