How to Import Excel/CSV file to MySQL Database using PHP

billyhuynh · 2025-02-27T07:14:13+0000

In this tutorial im going to show you how to create n application that can be used to Import CSV/Excel file using PHP. We will create a simple PHP web application that has a form for our import feature and table to display our imported data to the database.

*Upgrade Info

The tutorial source code was originally written in PHP MySQL and I have updated it into PHP MySQLi.

To start with this project, please make sure that you have already installed a local web server that can run PHP script and MySQL Database such as "XAMPP" and "WAMP". Then also make sure that the "Apache" and "MySQL" is started (for XAMPP/WAMP, open the XAMPP/WAMP Control Panel and start the Apache and MySQL).

Now, we will create a Database in your PHPMyAdmin named “studentdb” then execute this SQL query to create a new table called "subject".

CREATE

TABLE

IF

NOT

EXISTS

`subject`

(
`SUBJ_ID`

INT

(

11

)

NOT

NULL

AUTO_INCREMENT

,
`SUBJ_CODE`

VARCHAR

(

30

)

NOT

NULL

,
`SUBJ_DESCRIPTION`

VARCHAR

(

255

)

NOT

NULL

,
`UNIT`

INT

(

2

)

NOT

NULL

,
`PRE_REQUISITE`

VARCHAR

(

30

)

NOT

NULL

DEFAULT

'None'

,
`COURSE_ID`

INT

(

11

)

NOT

NULL

,
`AY`

VARCHAR

(

30

)

NOT

NULL

,
`SEMESTER`

VARCHAR

(

20

)

NOT

NULL

,
PRIMARY

KEY

(

`SUBJ_ID`

)
)

ENGINE=

InnoDB DEFAULT

CHARSET=

latin1 AUTO_INCREMENT

=

500

;

Then inside you htdocs or document root folder create a new folder and name it as “excel”. Next, we need to create a PHP file naming “db.php” that will hold our database connection and here’s the following code:

<?php
$conn

=

mysqli_connect

(

"localhost"

,

"root"

,

""

)

or die

(

"Could not connect"

)

;
mysqli_select_db

(

$conn

,

"studentdb"

)

or die

(

"could not connect database"

)

;
?>

Next, create another PHP file named “index.php”,and this PHP file will be the first page that will load to our browser when we will access/browse the “excel” folder from our web directory. And this file will load all the list of subjects if the subject table is not empty well as this page will allow the user to import the CSV/Excel file and upload the data to MySQL Database.
The page Interface will be look like the image below.

And here’s the code for the “index.php” file:

<!DOCTYPE html>
<?php
include

'db.php'

;
?>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Import Excel To MySQL Database Using PHP </title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="description" content="Import Excel File To MySQL Database Using php">
<link rel="stylesheet" href="css/bootstrap.min.css">
<link rel="stylesheet" href="css/bootstrap-responsive.min.css">
<link rel="stylesheet" href="css/bootstrap-custom.css">
</head>
<body>
<!-- Navbar
================================================== -->
<div class="navbar navbar-inverse navbar-fixed-top">
<div class="navbar-inner">
<div class="container">
<a class="btn btn-navbar" data-toggle="collapse" data-target=".nav-collapse">
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</a>
<a class="brand" href="#">Import Excel To MySQL Database Using PHP</a>
</div>
</div>
</div>
<div id="wrap">
<div class="container">
<div class="row">
<div class="span3 hidden-phone"></div>
<div class="span6" id="form-login">
<form class="form-horizontal well" action="import.php" method="post" name="upload_excel" enctype="multipart/form-data">
<fieldset>
<legend>Import CSV/Excel file</legend>
<div class="control-group">
<div class="control-label">
<label>CSV/Excel File:</label>
</div>
<div class="controls">
<input type="file" name="file" id="file" class="input-large">
</div>
</div>
<div class="control-group">
<div class="controls">
<button type="submit" id="submit" name="Import" class="btn btn-primary button-loading" data-loading-text="Loading...">Upload</button>
</div>
</div>
</fieldset>
</form>
</div>
<div class="span3 hidden-phone"></div>
</div>
<table class="table table-bordered">
<thead>
<tr>
<th>ID</th>
<th>Subject</th>
<th>Description</th>
<th>Unit</th>
<th>Semester</th>
</tr>
</thead>
<?php
$SQLSELECT

=

"SELECT * FROM subject "

;
$result_set

=

mysqli_query

(

$conn

,

$SQLSELECT

)

;
while

(

$row

=

mysqli_fetch_array

(

$result_set

)

)
{
?>
<tr>
<td><?php

echo

$row

[

'SUBJ_ID'

]

;

?>

</td>
<td><?php

echo

$row

[

'SUBJ_CODE'

]

;

?>

</td>
<td><?php

echo

$row

[

'SUBJ_DESCRIPTION'

]

;

?>

</td>
<td><?php

echo

$row

[

'UNIT'

]

;

?>

</td>
<td><?php

echo

$row

[

'SEMESTER'

]

;

?>

</td>
</tr>
<?php
}
?>
</table>
</div>
</div>
</body>
</html>

Next, we’re going to create another PHP file naming “import.php” that we will use to process the data from CSV/Excel to MySQL Database. Here’s the following code:

<?php
include

'db.php'

;
if

(

isset

(

$_POST

[

"Import"

]

)

)

{
echo

$filename

=

$_FILES

[

"file"

]

[

"tmp_name"

]

;
if

(

$_FILES

[

"file"

]

[

"size"

]

>

0

)
{
$file

=

fopen

(

$filename

,

"r"

)

;
while

(

(

$emapData

=

fgetcsv

(

$file

,

10000

,

","

)

)

!==

FALSE

)
{
//It wiil insert a row to our subject table from our csv file`
$sql

=

"INSERT into subject (`SUBJ_CODE`, `SUBJ_DESCRIPTION`, `UNIT`, `PRE_REQUISITE`,COURSE_ID, `AY`, `SEMESTER`)
values('$emapData[1]

','$emapData[2]

','$emapData[3]

','$emapData[4]

','$emapData[5]

','$emapData[6]

','$emapData[7]

')"

;
//we are using mysql_query function. it returns a resource on true else False on error
$result

=

mysqli_query

(

$conn

,

$sql

)

;
if

(

!

$result

)
{
echo

"<script type=\"

text/javascript\"

>
alert(\"

Invalid File:Please Upload CSV File.\"

);
window.location = \"

index.php\"
</script>"

;
}
}
fclose

(

$file

)

;
//throws a message if data successfully imported to mysql database from excel file
echo

"<script type=\"

text/javascript\"

>
alert(\"

CSV File has been successfully Imported.\"

);
window.location = \"

index.php\"
</script>"

;
//close of connection
mysqli_close

(

$conn

)

;
}
}
?>

After this, you can try it on your own, and I have attached with this sourcecode the example CSV/Excel file used for this tutorial, or you can also create a new CSV file to Import using below the below column orders.

ID

Subject Code

Subject Description

Unit

Pre Requisite

Course ID

AY (Academic Year)

Semester

DEMO

That's it! That would be the end of our tutorial on Importing CSV/Excel File data to MySQL Database using PHP. By the way in this application I am using the Twitter bootstrap framework, you can download and use it like in this application.

If you want to see some new Source Code or Application and Tutorials Just click here.

Happy Coding :)

Download

You must upgrade your account or reply in the thread to view hidden text.

How to Import Excel/CSV file to MySQL Database using PHP

More options

billyhuynh

451,993

323,299

323,308