Converting Your Excel Worksheet into a Working MySQL Database

Microsoft Excel is the world’s most popular and common spreadsheet office applications software. It is used to store and manipulate data in engineering, IT and business sectors. Thanks in part to the popularity of the Internet and certain kinds of web sites, many business owners find that they need to convert their Excel spreadsheets into proper MySQL databases. This article will show you how to do this, without forcing you to purchase additional software.

Background and importance

Business owners often define their data using MS Excel because it is common and easy to manipulate. When they want to convert their MS Excel workbook into a working MySQL database, however, problems arise. For a database to be used in a dynamically-driven website, as is frequently done today, it needs to be in a MySQL file extension; otherwise, it will not work. MySQL is the most popular open source database and commonly used in conjunction with the PHP scripting language to command website operations, as in E-commerce websites. 

An important database in Excel that can be exported into MySQL can be anything a website owner or developer can think of, from a price list database to customer information to contact information; virtually anything you find important enough to store in MS Excel also means it’s important to put it in a MySQL database. 

When you are a beginner or unfamiliar with MySQL, if you do a search in Google for “Convert MS Excel to MySQL” you will find that the top search results are for programs or software for sale. This means it will cost you to convert. The objective of this tutorial is to teach you the basics of how to convert or export a database in MS Excel into a working MySQL database without ever spending any money out of your pocket.

Let us discuss the database basics. 

Basic principles 

A database is a collection of information to be used in processing other information. In MS Excel, you have two things, as you know very well: 

  • Workbook

  • Worksheets 

A workbook comprises several worksheets. For example, you have a workbook entitled “Movie” with three worksheets named:  

  • Best Movies by Year (with two columns, year and top movies in that year).

  • Released Movies by Rating (with two columns, movie title and its equivalent rating).

  • Worst Movies by Directors (with two columns, director name and their worst movie released). 

When you export the data into MySQL, it will now be called “database” and “table.” The database consists of tables, and applying the concept of data structures, it will now be:

MS Excel “Workbook" -> MySQL “Database”

MS Excel “Worksheet” -> MySQL “Table” 

Do you see and understand the analogy? In order to convert MS Excel data into MySQL, make sure you convert it according to proper data structures. 

Therefore, for the above MS Excel example, you need first to create a database named “Movie,” and then create tables after that. Just as in Excel, you cannot create or name a worksheet without opening or saving the workbook first. 

{mospagebreak title=Conversion requirements and restrictions} 

We need to determine what is needed, as well as the limitations, in order to convert an Excel workbook into a working MySQL database. Take note of the following before doing a conversion: 

  • Not all MySQL versions or phpmyadmin interfaces are the same. Some may differ according to the versions released. So this tutorial will try its best to illustrate two versions: the offline version using XAMPP/phpmyadmin, and the online version using a real MySQL database provided by a web hosting company.

  • Depending on your type of hosting, some hosting companies will not allow direct import of an external file to the MySQL database, so there are alternative steps to this illustrated below. For example, there are some instances where the website has been hosted under shared conditions (like those of free web hosting), importing of files to the MySQL environment is not being supported. In this case, you can either upgrade your hosting account or try other steps.

  • Before exporting any Excel-related worksheets to MySQL, first you need to create a database to handle it.

  • You can only export one worksheet at a time to the MySQL database that you have created. Exporting is fast and easy provided they are in compatible formats and within a reasonable file size.

  • The Excel worksheet should be clean (free of fancy formatting such as images, colors, wrap text and fancy fonts) before exporting it to MySQL. If the first column in your Excel spreadsheet has the field name “Movie ratings,” the first field name should occupy the first column and first row, so it will be in cell A1, in the Excel worksheet. 

{mospagebreak title=Exact steps in the XAMPP phpmyadmin panel} 

We will illustrate first how to export MS Excel data to MySQL using the XAMPP phpmyadmin control panel. This is the local host version of the real MySQL database placed online. You can use this during the development stage of your website. 

  1. Open the Excel workbook, then go to File -> save as “CSV (Comma delimited).” If there is a warning that says “Do you want to keep the workbook in this format?” Click “yes” and close the workbook. Click “NO” if there is a warning that asks “Do you want to save the changes made to yourworkbook.csv?”

  2. Go to phpmyadmin and create a database using the same name as your workbook.

    To do this in XAMPP, go to http://localhost/phpmyadmin and then, under the main page, look for the database link. Click that; it will take you to a page that asks you for a database name. Type the name of the database and then leave it as default “collation.”

  3. Create the name of the first database table. Note that we can export one worksheet at a time to MySQL. This is true whether we’re doing it offline (using the XAMPP Localhost phpmyadmin panel) or online (using the actual MySQL server).

  4. Then “Enter the number of fields,” which is equal to the number of columns of the Excel worksheet table. For example if your Excel worksheet table has three fields (name, email and address), then enter 3 as number of fields.

  5. MySQL will then ask you to enter the field name. Enter it exactly as you have it in the Excel worksheet. Then on the type, change it from VARCHAR to TEXT. Leave everything else the way it is, and then click “SAVE.”

  6. The next step is to click “IMPORT” (you can see this under “Structure tab” in phpmyadmin). Refer to the guide below: 

Location of the text file: (click “Browse” and navigate to the .csv file you need to import) 

Character set of the file: Set it to “utf8” 

Check "Allow interrupt of import in case script detects it is close to time limit." This might be good way to import large files, however it can break transactions. 

Number of records (queries) to skip from start: 1 

Format of imported file: CSV with LOAD data 

Uncheck:

Replace table data with file

Ignore duplicate rows 

Fields terminated by: , (change it to comma)

Fields enclosed by: (change it to blank, or empty it)

Fields escaped by:

Lines terminated by: auto

Column names: (change it to blank, or empty it)

Use LOCAL keyword: Check this one 

Finally when everything is set, click “GO.” The csv file will then be exported to your first MySQL database table. You can then click “BROWSE” in the phpmyadmin navigation to see the exported data. You can now see the table in MySQL format. 

If you have other worksheets to be exported as another MySQL table, repeat the above procedure, starting at step three, which you can easily create by clicking the database name link on the left navigation (link in blue) or in the top (near header). 

{mospagebreak title=Exact steps in an online MySQL server (where direct file import is not allowed)} 

Below are the steps necessary to import Excel worksheet data into a MySQL table in an actual MySQL database for real websites (not using XAMPP localhost): 

1. Do Steps 1 to 5 in the XAMPP Phpmyadmin methods above.

2. Then go to http://csv2sql.evandavey.com/ . This will convert csv data into MySQL insert statements.

3. Type the exact table name you have created using above steps.

4. Browse to the location of the CSV file you have saved in the above steps.

5. Set the “Mode” to “Insert Statements.”

6. Leave the “Primary Key Field (Update Only):” blank.

7. After all are set, click “GO”

8. Then go to the “SQL” tab (you will find this at the top navigation of phpmyadmin) under the MySQL database table you have created in the above steps.

9. Copy and paste the result in Step 7 in the phpmyadmin SQL query box. It should like the image below:

 

 

10. After everything is set, click “GO.”

11. Click “Browse” and you can now see your Excel Worksheet being transformed into a MySQL table. 

If you need to export several Excel worksheets as another MySQL table, repeat procedure 1 to 9 above. 

If you have any questions, just comment on this post. 

6 thoughts on “Converting Your Excel Worksheet into a Working MySQL Database

  1. Thanks a heap for this article and for the free online converter…. I was trying to upload an EXCEL file that was exported as a CSV but for some strange reason the first 2 cells kept veering off into the nanospace. The online converter was the solution and I was able to get the data inserted into the table eaily and painless…. This page is BOOKMARKED!!!!

  2. HI,

    I always like to Convert my Excel Worksheet into a Working MySQL Database, Its really wonderful program, which is very easy to use.

[gp-comments width="770" linklove="off" ]