Importing CSV file

Fonte: TecPorto
Saltar para a navegação Saltar para a pesquisa

If you ever wanted to import a CSV file into a MySQL database, this is the way to do it (example):

LOAD DATA LOCAL INFILE 'source.csv' INTO TABLE destination_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (col1, col2, col3);

This example assumes you are using the mysql command-line client and the file source.csv is in your current directory.

The following elements may need to be tweaked according to the actual formatting of your CSV file:

  • INFILE 'source.csv': This is the name of the file you want to load. Replace it with the appropriate name.
  • FIELDS TERMINATED BY ',': This indicates that, in this example, each field is separated from the next by a comma. If you are using tabs instead, try '\t'.
  • ENCLOSED BY '"': This means that the fields, most likely the text fields, are separated by double quotes. You might be using single quotes or no quotes at all.
  • LINES TERMINATED BY '\n': This is a common setting, but once again, it may not be right for you. On Windows it is likely you'll need to use '\r\n'.
  • (col1, col2, col3): These are the columns. If you want to import a CSV file with a smaller number of columns (fields) than your table, you'll need to manually specify onto which columns you want to import the data. The ordering of the names is relative to the order of the fields in your input CSV file.