Importing data from an Excel/Google spreadsheet is made very simple using PHPMyAdmin.
Having a solid understanding of MySQL does help quite a bit but it is not necessary to an extent, depending on what you plan to do with your database.
Today I will show you how to generate a table by importing a spreadsheet as well as how to simply import data into an existing table.
First we will start off with allowing PHPMyAdmin to create a table for us and to do that, let’s start off with a simple spreadsheet.
I am using Google Spreadsheets to create my data however you can use any other spreadsheet tool with a CSV Export functionality including Microsoft Excel. Here I have created a simple page of example data about cars with their make, model and year.
We have four columns here describing the data: ID, Make, Model and Year. Using PHPMyAdmin we can import this data as-is and create a new table from it. Simply export or save the file as a Comma Separated Values (CSV) file and import it as shown here:
In this case make sure you tick the box labeled: “The first line of the file contains the table column names”.
Now in the next example I will I will import the following additional data into the table we have just created, adding new data to what already is in place.
To do this, all you need to do is remove the columns at the top of the data. This time I will open and edit the CSV file so you can see how the data we are importing is formatted and how I manipulate it.
That is all that is involved! The process is very simple, and with a better understanding of MySQL you can only benefit from knowing this.
So a quick overview of how to import a CSV into a MySQL database:
- Create your data in a spreadsheet
- Export/Save your spreadsheet as a .csv
- (optionally) If importing data into an existing table, remove columns from CSV or within the spreadsheet
- In a database in PHPMyAdmin, click “Import”
- Click “Choose File”
- Choose your .csv file and click “Open”
- (optionally) If importing data with no table pre-existing, tick “The first line of the file contains the table column names” box
- Finally, click “Go”
- You’re done!
If you would like to see the entire process, I have also created a video with commentary for you to see: