Search code examples
mysqlsqlload-data-infile

Import excel data put into the SQL database?

I am tried to import excel data into my MySQL Query browser database. I try to use below the coding type in the MySQL Query to execute, but it cannot work.

My code:

LOAD DATA LOCAL INFILE
    'c:/2019/countries.csv'
    INTO TABLE countries
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 ROWS
    (id,name,country_code,language);

The My SQL query browser show me the error:

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 'ROWS (id,name,country_code,language)' at line 7

enter image description here

My database info:

enter image description here

My excel data info (My excel file location is"c:/2019/countries.csv"):

enter image description here

My CSV file info:

  id,name,country_code,language
  231,Andorra,20,en
  232,United Arab Emirates,784,en
  233,Afghanistan,4,en
  234,Antigua and Barbuda,28,en
  235,Anguilla,660,en
  236,Albania,8,en
  237,Armenia,51,en
  238,Angola,24,en
  239,Antarctica,10,en
  240,Argentina,32,en
  241,American Samoa,16,en

Anyone can guide me what I get the wrong in my coding? Thanks.

Solution

  • Depending on your MySQL version, IGNORE clauses in LOAD DATA exclusively uses LINES or in latter versions, interchangebaly uses LINES or ROWS. You can see this by docs of different versions:

    LINES and ROWS are synonymous

    [IGNORE number {LINES | ROWS}]

    Only LINES allowed (i.e., ROWS not supported)

    [IGNORE number LINES]