Excel File Import into MySQL Database

Swimsheets Tutorial Fall 2017:

Description:

In order to upload an excel file into a data base, you first want to register with a web hosting site that will allow you to create data tables within a database. For our project, we used bluehost to create a domain online, a database user and a database. We then used https://codewithchris.com/iphone-app-connect-to-mysql-database/ to establish a database connection that will allow us to update and read the database from an outside source, in our case, this was XCode.

The code can be found at https://bitbucket.org/swimsheets/swimsheet.git

Setting up the Database:

The first step is to get a database set up. You can do this through the hosting website that you chose. You will want to set up a user for the database as well as the database itself. You can then use phpMyAdmin to create a table within this database.

Create an Excel File:

You can now create an excel file that matches the setup of the table you created in your database. The first row of the excel file should have titles that match those of the column names in your table. Add information into a few rows of the excel file so that you will be able to see if your code works. Save this file as an excel XML file.

Upload page:

The next step is creating a php file that will allow you to upload the excel document into the database. The code for this can be found at https://bitbucket.org/swimsheets/swimsheet/src/62cbaae0bb10316dd48f44ef94f161df9c15cc5f/Code%20for%20database/upload.php?at=master&fileviewer=file-view-default
You do not need to modify any of this code to get it to work; however, you can change the part that says “Names file” in line 8 to be the label of the file you are wanting to upload.

Importing the data:

You now want to create a new php file called import.php
Make sure the name is import.php as that is what the upload.php file you just created is linked to. The code that you will want to modify for import.php is here https://bitbucket.org/swimsheets/swimsheet/src/62cbaae0bb10316dd48f44ef94f161df9c15cc5f/Code%20for%20database/import.php?at=master&fileviewer=file-view-default

You will need to change line 6 that says mysqli_connect to match the username for the database user, the password for the database user, and the name of the database you are connecting to.

The first part of this code creates a function to add an object, in this case, we added a swimmer. In the parenthesis, you will want to edit it to match the columns you have in your table. The next part of the code creates an array. Change the parts in single parenthesis to match that of your excel file. The part that has $ in front should match the columns in your table and should match what is in your add function. The next part of the code actually reads in the file that you uploaded. Continue to modify the code to match the labels of your columns in the table and your excel file.
Where the code calls the add_person function is where you are actually entering the data into the table.

The part of the code within html is for displaying your excel data at yourwebhostingsite.com/import.php, for our project, this was swimsheets.com/import.php

This part is not necessary for your data to be imported into the table; however, it allows you to see if your data was imported correctly.

The labels within can be whatever you wish, but it makes the most sense to match it to the labels of the columns in your excel file and table.

The php echo is calling the data that you have uploaded in order to display it in the table you just created within html.

Testing:

In order to test how your code works, go first to yourwebdomain.com/upload.php, upload the file as an xml file. You should be redirected to yourwebdomain.com/import.php where you should be able to see what has been uploaded to your table.