How to Import CSV File into MySQL Database using PHP/MySQLi

Submitted by nurhodelta_17 on Monday, March 12, 2018 - 20:18.

Getting Started

I've used bootstrap to improve the design of presentation of this tutorial. This bootstrap is included in the downloadable of this tutorial but, if you want, you may download bootstrap using this link.

Creating our Database

Next, we create our MySQL database. I've included a .sql file in the downloadable of this tutorial which is a mysql database file. All you have to do is import the said file. If you have no idea on how to do this, please refer to my tutorial, How import .sql file to restore MySQL database. You should be able to create a database with tables named mydatabase.

Creating our Form

Next, we're going to create our upload form. Also, for you to check the uploaded data, I've included the table that contains the uploaded data as well. Create a new php file and name it as index.php and paste the below codes.

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<title>Import CSV File into MySQL Database using PHP/MySQLi</title>
	<link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css">
</head>
<body>
<div class="container">
	<h1 class="page-header text-center">Import CSV File into MySQL Database</h1>
	<div class="row">
		<div class="col-sm-3">
			<h3>Import File Form</h3>
			<form method="POST" action="import.php" enctype="multipart/form-data">
				<div class="form-group">
					<label for="file">File:</label>
					<input type="file" id="file" name="file">
				</div>
				<button type="submit" name="import" class="btn btn-primary btn-sm">Import</button>
			</form>
			<?php
				session_start();
				if(isset($_SESSION['message'])){
					?>
					<div class="alert alert-info text-center" style="margin-top:20px;">
						<?php echo $_SESSION['message']; ?>
					</div>
					<?php
 
					unset($_SESSION['message']);
				}
 
			?>
		</div>
		<div class="col-sm-9">
			<table class="table table-bordered table-striped">
				<thead>
					<th>UserID</th>
					<th>Firstname</th>
					<th>Lastname</th>
					<th>Address</th>
				</thead>
				<tbody>
					<?php
					//connection
					$conn = new mysqli('localhost', 'root', '', 'mydatabase');
 
					$sql = "SELECT * FROM members";
					$query = $conn->query($sql);
 
					while($row = $query->fetch_array()){
						?>
						<tr>
							<td><?php echo $row['id']; ?></td>
							<td><?php echo $row['firstname']; ?></td>
							<td><?php echo $row['lastname']; ?></td>
							<td><?php echo $row['address']; ?></td>
						</tr>
						<?php
					}
 
					?>
				</tbody>
			</table>
		</div>
	</div>
</div>
</body>
</html>

Creating our Import Script

Lastly, we create our upload/import data script. Create a new file and name it as import.php and paste the below codes on the file.

<?php
 
	session_start();
	//connection
	$conn = new mysqli('localhost', 'root', '', 'mydatabase');
 
	if(isset($_POST['import'])){
		//check if input file is empty
		if(!empty($_FILES['file']['name'])){
			$filename = $_FILES['file']['tmp_name'];
			$fileinfo = pathinfo($_FILES['file']['name']);
 
			//check file extension
			if(strtolower($fileinfo['extension']) == 'csv'){
				//check if file contains data
				if($_FILES['file']['size'] > 0){
 
					$file = fopen($filename, 'r');
 
					while(($impData = fgetcsv($file, 1000, ',')) !== FALSE){
						$sql = "INSERT INTO members (firstname, lastname, address) VALUES ('".$impData[0]."', '".$impData[1]."', '".$impData[2]."')";
						$query = $conn->query($sql);
 
						if($query){
							$_SESSION['message'] = "Data imported successfully";
						}
						else{
							$_SESSION['message'] = "Cannot import data. Something went wrong";
						}
					}
					header('location: index.php');
 
				}
				else{
					$_SESSION['message'] = "File contains empty data";
					header('location: index.php');
				}
			}
			else{
				$_SESSION['message'] = "Please upload CSV files only";
				header('location: index.php');
			}
		}
		else{
			$_SESSION['message'] = "File empty";
			header('location: index.php');
		}
 
	}
 
	else{
		$_SESSION['message'] = "Please import a file first";
		header('location: index.php');
	} 
 
?>

That ends this tutorial. P.S. If you have no .csv file to test, I've included a test.csv file in the downloadable on this tutorial that you can use. Also, if you have an excel file instead of csv file, just save the file as a new .csv file. Happy Coding :)

Comments

Hi i would like to know how

Hi i would like to know how can i change both lines that shows $conn = new mysqli('localhost', 'root', '', 'mydatabase'); use one single database php file? Should i replace the above line with the one below instead? Thanks for your help $conn = new mysqli 'database.php';

Import csv

Thank you very much for this code. Worked perfectly and I managed to convert it to import SQL file into database. Much appreciated.

help??

Great tutorial, just 1 little question. Why are the notifications not showing when rest is working ok?

Add new comment

Add new comment
1340 views