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