How to import CSV file into MySQL 3 examples

3 tools offers easy and free import of CSV files to MySQL is minimum efforts:

DBeaver

DBeaver - Universal Database Tool available for Linux, Windows and MacOS. I has free community edition. Support multiple DB like:
MySQL, PostgreSQL, MariaDB, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Derby, MongoDB. Import from CSV file can be done by:

  • Create CSV connection
    • File
    • New
    • Database connection
    • Flat files(CSV) ( * MySQL (download driver if needed)
      driver if needed)
    • Select Path
    • Finish
  • Create MySQL connection
    • File
    • New
    • Database connection
    • MySQL (download driver if needed)
    • Add connection parameters
    • Test and Save
    • Finish
  • Connect to CSV
    • Select a table you want to export, right click
    • Export Data
    • Database
  • In target database/schema of data transfer wizard choose your MySQL connection
  • Set columns mapping if needed
  • Start data transfer

Selection_014

HeidiSQL

HeidiSQL is a useful and reliable tool designed for web developers using the popular MySQL server, Microsoft SQL databases and PostgreSQL. It is created for Windows but it can run on Linux with Wine. Sometimes under Linux there are problems with relative paths if the files are located outside the wine folders. Another issue that could arise is with file encoding of the CSV files.

  • Select Schema
  • Select Table (optional)
  • Tools
  • Import CSV file...
  • Select file name
  • Encoding
  • Ignore first line
  • Column mapping
  • Columns separator
  • Escape fields
  • Database
  • Import

Selection_015

MySQL Workbench

MySQL Workbench is a graphical tool for working with MySQL servers and databases. Most probably is the most popular and the most used tool for MySQL.

One way to import CSV file is by using code like:

LOAD DATA LOCAL INFILE '/home/myfile.csv'
INTO TABLE test.testtable FIELDS TERMINATED BY ','
ENCLOSED BY '"' LINES TERMINATED BY '\n';

Another way is by:

  • Select schema
  • Select table (optional)
  • Table Data Import Wizard
  • Select File path
  • Select destionation
    • New table
    • Existing one
  • Select Encoding
  • Select additonation settings
    • Field separator
    • Line Separator
    • Enclose Strings
  • Next - this will do:
    • Prepare Import
    • Import data file
  • Finish

Selection_017

Related Article