Importing CSV files into MySQL from the Linux command line05 Aug 2016
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
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
--fields-terminated-byis the delimited
--localindicates that the csv or file is on your local file system
-uis the MySQL user
-pprompts for the MySQL password
mysql-database-namereplace this with the name of your database
mysql-table-namethe 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.
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
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