Importing CSV files into MySQL from the Linux command line

Every so often I need to bulk import csv type files into MySQL and I forgot the process. Here are the steps for a simple csv into MySQL.

Assuming you have a csv file with contents like below (with no headings line)

234245,0bMHxBO76w5T23t
564565,0BzRf05nA0lrn2G
234233,0eHhkxexkLDkVRz
124235,0Ezp93248igtsYu
12314,0I1d96ujWqrqqU6

Ensure the mysql-client is installed and use the mysqlimport command. Refer below for a detailed example.

mysqlimport  --fields-terminated-by=, --verbose --local -u root -p mysql-database-name mysql-table-name.csv

where

  • --fields-terminated-by is the delimited
  • --local indicates that the csv or file is on your local file system
  • -u is the MySQL user
  • -p prompts for the MySQL password
  • mysql-database-name replace this with the name of your database
  • mysql-table-name the name of the csv file needs to match the target MySQL table name, the extension doesn’t matter. This can also include the file path at the start ie. /var/www/session.csv

If your csv has a headings row (such as example below) you can added the option --ignore-lines=1 to skip that line

user_id, session_id
234245,0bMHxBO76w5T23t
564565,0BzRf05nA0lrn2G
234233,0eHhkxexkLDkVRz
124235,0Ezp93248igtsYu
12314,0I1d96ujWqrqqU6

If the import works you’ll see a similar output to below, the number inf Records should match the lines in the csv, just watch for the numbers in Skipped and Warning to ensure all rows were imported - these should be 0

Enter password: 
Connecting to localhost
Selecting database users
Loading data from LOCAL file: session.csv into session
users.session: Records: 58918  Deleted: 0  Skipped: 0  Warnings: 0
Disconnecting from localhost

Justin Kelly

Justin Kelly

Web Developer, Business Analytics, Data Engineer specialising in PHP, ReactJS, Tableau, AWS RedShift and Matillion ETL

Based in Melbourne, Australia

Feel free to contact me justin@kelly.org.au or _justin_kelly

Leave a comment

Name Notify me of replies by email.