How to Import CSV File in MySQL PHP

July 3, 2021 admin

In this tutorial, we are going to see how to import a CSV file into MySQL database using PHP. CSV stands for Comma Separated Values and contains all the data separated by commas.

We will use three files:

The index.php file which will contain part of the user interface.
The import_csv.php file to connect MySQL and parse the CSV data and store it in a MySQL table.
The db_connect.php file to connect to the database.

Create a database

Create a MySQL database named ‘test’ for example. If you already have a test database, run the code below in the SQL query window of the database. As shown in the screenshot below:

CREATE TABLE IF NOT EXISTS `product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(256) NOT NULL,
`description` text NOT NULL,
`price` int(255) NOT NULL,
primary key (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Connection to the MySQL database with PHP

After creating the “product” table, we will create the file db_connect.php to establish a connection with the MySQL database.

<?php
	$server = "localhost";
	$username = "root";
	$password = "";
	$db = "test";
	$conn = mysqli_connect($server, $username, $password, $db);
?>

Steps to import a CSV file into a MySql database using PHP

Step 1: Create the import_csv.php file and add the below code into this file.

<?php
	// Connect to database
	include("db_connect.php");

	if (isset($_POST["import"])) {
		
		$fileName = $_FILES["file"]["tmp_name"];
		
		if ($_FILES["file"]["size"] > 0) {
			
			$file = fopen($fileName, "r");
			
			while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
				$sql = "INSERT into product (id,name,description,price)
					   values ('" . $column[0] . "','" . $column[1] . "','" . $column[2] . "','" . $column[3] . "')";
				$result = mysqli_query($conn, $sql);
				
				if (! empty($result)) {
					$type = "success";
					$message = "Data is imported into the database";
				} else {
					$type = "error";
					$message = "Problem importing CSV data";
				}
			}
		}
	}
	//Return to the index page
	header('Location: index.php');
	exit;
?>

The code below is used to create an interface to upload a CSV file.

Step 2: Create index.php file to display the CSV file. You need to add the code below to the index.php file.

<!DOCTYPE html>
<html>

<head>
	<title>Import CSV File in MySQL PHP</title>
</head>

<body>
    <h3>Read the article on : <a href="https://stackhowto.com/how-to-import-csv-file-in-mysql-php/" target="_blank">How to Import CSV File in MySQL PHP</a></h3>

    <form enctype="multipart/form-data" action="import_csv.php" method="post">
        <div class="input-row">
            <label class="col-md-4 control-label">Choose a CSV file</label>
            <input type="file" name="file" id="file" accept=".csv">
            <br />
            <br />
            <button type="submit" id="submit" name="import" class="btn-submit">Import</button>
            <br />
        </div>
    </form>

    <?php
			// Connect to database
			include("db_connect.php");

            $sql = "SELECT * FROM product";
            $result = mysqli_query($conn, $sql);

            if (mysqli_num_rows($result) > 0) {
    ?>
        <table>
            <thead>
                <tr>
                    <th>Product ID</th>
                    <th>Product Name</th>
                    <th>Description</th>
                    <th>Price</th>
                </tr>
            </thead>
            <?php while ($row = mysqli_fetch_array($result)) { ?>
                <tbody>
                    <tr>
                        <td> <?php  echo $row['id']; ?> </td>
                        <td> <?php  echo $row['name']; ?> </td>
                        <td> <?php  echo $row['description']; ?> </td>
                        <td> <?php  echo $row['price']; ?> </td>
                    </tr>
            <?php } ?>
                </tbody>
        </table>
        <?php } ?>
</body>
</html>