MySQL throws a syntax error when specifying columns

Question

I have an issue when trying to import a .csv file into a table.

As far as I know (and as I've read in the reference manual), the syntax is something like this:

load data infile '/path/to/my/file.csv'
    into table myTable(field1, field2, field3,..., fieldk)
    fields terminated by ',' optionally enclosed by '"'
    lines terminated by '\n'
    ignore 1 lines;

However, MySQL client throws this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fields terminated by ',' optionally enclosed by '"' lines terminated by '\n'

I need to specify the column list, because my table has a column that are not present in the file (the id column).

I've checked (and rechecked) the syntax, and everything seems OK... So I don't have a clue what could be wrong. I've tried enclosing the field names in backticks (`) but it doesn't work either... Is the field list wrong? Do I need to specify something I'm missing?

Background info:

OS: Debian 7.5 (wheezy), 64-bit
MySQL version: 5.5.37-0+wheezy1
All tables are MyISAM

Attempted solution:

I could, of course, create a temporary table and drop the "problematic" column. I've tested this solution, and it works:

drop table if exists temp_myTable;
create temporary table temp_myTable like myTable;
alter table temp_myTable drop column id;
load data infile '/path/to/my/file.csv'
    into table temp_myTable
    fields terminated by ',' optionally enclosed by '"'
    lines terminated by '\n'
    ignore 1 lines;
insert into myTable (field1, field2, ... fieldk)
    select field1, field2, ... fieldk
    from temp_myTable;
drop table if exists temp_myTable;

However, I think this is quite cumbersome... Why should I write 6 instructions when I should be able to solve it with one?

Barranka · Accepted Answer · 2014-05-26 17:43:07Z

5

After reading more carefully the reference manual, I found that the right way to write the column list is at the end of the sentence:

load data infile '/path/to/my/file.csv'
    into table myTable
    fields terminated by ',' optionally enclosed by '"'
    lines terminated by '\n'
    ignore 1 lines
    (field1, field2, field3,..., fieldk); -- The field list goes here

Right now, I'm feeling really dumb... but I put this knowledge here, just in case anyone is facing this same issue.

answered May 26, 2014 at 17:43

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

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

Collectives™ on Stack Overflow

MySQL throws a syntax error when specifying columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related