Converting Your Excel Worksheet into a Working MySQL Database - Exact steps in the XAMPP phpmyadmin panel
(Page 3 of 4 )
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.
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?”
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.”
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).
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.
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.”
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).
Next: Exact steps in an online MySQL server (where direct file import is not allowed) >>
More Database Articles
More By Codex-M
