SO YOU’VE CREATED A DATABASE FROM A BUNCH OF EXCEL DATA
But how do you import the Excel data in MySQL? With phpMyAdmin the process is painless and easy. This comes in handy for developers who need to migrate data from one CMS platform or development environment to another.
This tutorial will explain this process step-by-step. The various options for database setup and character sets are beyond the scope of this tutorial. Here are the steps to follow in order to import Excel data into MySQL using phpMyAdmin:
Open your Excel file. Select the worksheet that you first want to export. If you have multiple worksheets in one Excel workbook, or file, you will need to export multiple files. You cannot import an Excel document with multiple worksheets in one swift swoop (unfortunately, at least to my knowledge).
Once your worksheet is selected, go to the ‘File’ menu and select ‘Save As’. In the formatting drop-down menu, select the ‘Comma Separated Values (.csv)’ option. You will see a prompt that tells you the Save As .csv option cannot save workbooks that contain multiple sheets (as explained in step 1). Simply click ‘OK’ and select the path for your saved document. Repeat this process for each worksheet in your Excel document.
Once you have saved each of your Excel worksheets separately into CSV format, you are ready to utilize phpMyAdmin’s import functionality.
Most hosting service providers utilize the phpMyAdmin program to manage MySQL databases. If you are unsure how to access databases on your server, please contact your service provider.
Once you have accessed phpMyAdmin, you will need to create a new database. Click on the ‘Database’ link. You will see a field labeled ‘Create new database’. Enter an appropriate name for your database and then click the ‘Create’ button. The SQL query that created your new database will be displayed on the screen.
You now need to create tables for your database. Under the ‘Create new table on database (Name you gave to database)’ input field, enter the name of your new table and the ‘Number of fields’ for your table. The number of fields should coincide with the amount of columns in the worksheet(s) you exported (and are about to import). Once you put the appropriate values in, select ‘Go’.
Next, you will need to manually type in the ‘Field’ names for this table. The field name should correspond with the column header name of your worksheet. You will also have to insert the max ‘Length/Values’ for the corresponding fields. These max values vary depending on what type of data will be included. A database column that has U.S. zip codes (without postal extension numbers) only needs to have the length/value set at 5. A database column that stores postal addresses will need more characters, hence a larger maximum length/value. Once you have assigned these header fields and their max values, click ‘Save’.
Now your ready to import your .csv file(s). On the left-hand column of phpMyAdmin interface, select the table you have just created. Next, click on the ‘Import’ tab.
Select the ‘Browse’ button and select the .csv file you created. Make sure ‘CSV’ is selected under ‘Format of imported file’. Next, you will have to switch the value of the ‘Fields terminated by’ field to a comma (,). You can leave everything else as is. Finally, click on the ‘Go’ button.
To make sure all your data was imported, click on the ‘Browse’ tab. You should see all the rows and data that have been imported from the .csv file.
To import multiple Excel worksheets, simply repeat steps 6 though 10 of this tutorial.