Converting Your Excel Worksheet into a Working MySQL Database
(Page 1 of 4 )
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:
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.
Next: Conversion requirements and restrictions >>
More Database Articles
More By Codex-M
