How to import CSV file into MySQL 3 examples

John D K -

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

plus one bonus tool.

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

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

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

dbForge Studio

dbForge Studio for MySQL is a tool that allows you to import and export data from/to tables and views in your MySQL databases. Supported formats include text, MS Excel, XML, CSV, DBF and many more.

If you will import into a new table:

  • On the Database menu
  • click Import Data
  • The Data Import Wizard opens.

If you import into an existing table:

  • Right-click a table in the database explorer and select Import Data from the context menu.
  • Select the CSV import format and specify the source data location.
  • Specify the MySQL connection, database, schema, and table to import the data.
  • Preview the source data and specify additional options to customize the import.
  • Specify the data formats for the source data.
  • Match source columns with target columns.
  • If you are importing data into a new table, you can change the properties of the Target column by double-clicking them in the top grid.
  • Select an import mode to determine how dbForge Studio should import the data.
  • Select how dbForge Studio should handle errors during the import and whether you want to receive a log file with details about the import session.
  • Click Import and view the progress of the import