Data TutorialsDatabases

Learn how to import Excel data into a MySQL database

Posted by AJ Welch

The following is an example walkthrough of importing an Excel document into a MySQL database. To run this tutorial you will need an Excel file, and admin access to a running MySQL instance.

For the example we’ll be using the following Excel file on rental boats:

boats.xlsx

  1. Open your Excel file and click Save As. Choose to save it as a .CSV (Comma Separated) file. If you are running Excel on a Mac, you will need to save the file as a Windows Comma Separated (.csv) or CSV (Windows) to maintain the correct formatting.

  2. Log into your MySQL shell and create a database. For this example the database will be named boatdb. Note that the --local-infile option is needed by some versions of MySQL for the data loading we’ll do in the following steps.
    $ mysql -u root -p --local-infile
    mysql> create database boatdb;
    mysql> use boatdb;
    
  3. Next we’ll define the schema for our boat table using the CREATE TABLE command. For more details, see the MySQL documentation.
    CREATE TABLE boats (
    d INT NOT NULL PRIMARY KEY,
    name VARCHAR(40),
    type VARCHAR(10),
    owner_id INT NOT NULL,
    date_made DATE,
    rental_price FLOAT
    );
    
  4. Run show tables to verify that your table was created.
    mysql> show tables;
    +------------------+
    | Tables_in_boatdb |
    +------------------+
    | boats            |
    +------------------+
    
  5. Now that there is a database and a table setup, the data can be imported with the LOAD DATA command.
    LOAD DATA LOCAL INFILE "/path/to/boats.csv" INTO TABLE boatdb.boats
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES
    (id, name, type, owner_id, @datevar, rental_price)
    set date_made = STR_TO_DATE(@datevar,'%m/%d/%Y');
    

If you are a Chartio user, you can now connect the MySQL database to Chartio and chart away.

Visual SQL Hero

Introducing Visual SQL

SQL may be the language of data, but not everyone can understand it. With our visual version of SQL, now anyone at your company can query data from almost any source—no coding required.

Learn about Visual SQL