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:
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.
- Log into your MySQL shell and create a database. For this example the database will be named
boatdb. Note that the
--local-infileoption 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;
- Next we’ll define the schema for our
boattable using the
CREATE TABLEcommand. 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 );
- Run show tables to verify that your table was created.
mysql> show tables; +------------------+ | Tables_in_boatdb | +------------------+ | boats | +------------------+
- Now that there is a database and a table setup, the data can be imported with the
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.