CODEWITHSUNDEEP
mysqlload-data-infile
Barranka
Barranka

Reputation: 21047

MySQL throws a syntax error when specifying columns

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:

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?

Upvotes: 2

Views: 2309

Answers (1)

Barranka
Barranka

Reputation: 21047

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.

Upvotes: 5

Related Questions