Excel to MySQL

Data Tutorial

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.