How to import a CSV file into MySQL database?

**Q. How can I import CSV files into MySQL tables?

A. First thing, is to have a clean CSV file, one that does not have ^M chars, usually inserted by Windows editors. Then be sure to have the same structure on the CSV file than the one you have in the MySQL table.

Let's say you have a file called adress.csv with this structure

Name,Last Name,Address
John,Wayne,Fifth Avenue
Marlon,Brando,Hollywood
Marilyn,Monroe,Brentwood

Now that you have a table with the same structure (you can use PHPMyAdmin to create one in a MySQL database), you can import the data from the csv file into the table.

mysqlimport --ignore-lines=1 --fields-terminated-by=, --verbose --local -u [user] -p [database] /path/to/address.csv
  • --ignore-lines will ignore the number of specified lines, in this case we are omiting the first one as it is the header of the table
  • --fields-terminated-by Will tell the command what character is the delimiter one, a comma in this case.
  • --verbose Makes the command to be talkie, I always prefer this mode with commands when using them for first time.
  • --local Tells the command that the CSV file should be read in the local file system of the server.
  • --u [user] Is the user with rights to write on the database
  • --p Tells the command to prompt for [user]'s password
  • [database] The name of the database where the table was created and where the data is going to be inserted
  • /path/to/address.csv Is the complete path to the csv file, be sure to match the name of the file address in this case, with the name of the table, otherwish it will not work, the extension might be anything or none at all.