Import CSV File into MySQL using PHP

Last modified on February 24th, 2018 by Vincy.

In this tutorial, I will walk you through PHP code to import CSV file into MySQL database by parsing the comma-separated data. The input CSV file is sent via a HTML form. There are various ways to read the CSV data and check my previous linked article for a different option. I used fgetcsv() function to read the database table column data from the CSV.

csv-to-database

If you are looking for PHP code to restore SQL script into database, please check my previous article example of restoring the database by importing the SQL script

HTML Form to Import CSV File

This HTML code is used to show a form with the CSV file import option. After submitting the file, PHP code parses the CSV data and prepares INSERT query to load the data into the database. After importing the data into the database the added rows are listed in the browser. When the user uploads the CSV source, the file type is restricted by specifying .csv using the accept attribute.

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

    </div>
    <div id="labelError"></div>
</form>
 

The jQuery validation for checking the uploaded file type is done by using regex. It checks if the uploaded file is with the .csv extension. If so, it will allow further default action to submit the file binaries to the PHP. Otherwise, it stops form submit and displays the error to the browser.

<script type="text/javascript">
	$(document).ready(
	function() {
		$("#frmCSVImport").on(
		"submit",
		function() {

			$("#response").attr("class", "");
			$("#response").html("");
			var fileType = ".csv";
			var regex = new RegExp("([a-zA-Z0-9\s_\\.\-:])+("
					+ fileType + ")$");
			if (!regex.test($("#file").val().toLowerCase())) {
				$("#response").addClass("error");
				$("#response").addClass("display-block");
				$("#response").html(
						"Invalid File. Upload : <b>" + fileType
								+ "</b> Files.");
				return false;
			}
			return true;
		});
	});
</script>

PHP Code to Import CSV Data to MySQL

In PHP code, it reads the uploaded CSV file and parses the data. It opens the input file in reading mode and gets the column data using fgetcsv(). It keeps on continue this process in a loop until it reaches the end of the CSV file. For each iteration. it gets the array of column data of a single record. It prepares INSERT query using these data and executes it to load the data into the database.

<?php
$conn = mysqli_connect("localhost", "root", "test", "phpsamples");

if (isset($_POST["import"])) {
    
    $fileName = $_FILES["file"]["tmp_name"];
    
    if ($_FILES["file"]["size"] > 0) {
        
        $file = fopen($fileName, "r");
        
        while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
            $sqlInsert = "INSERT into users (userId,userName,password,firstName,lastName)
                   values ('" . $column[0] . "','" . $column[1] . "','" . $column[2] . "','" . $column[3] . "','" . $column[4] . "')";
            $result = mysqli_query($conn, $sqlInsert);
            
            if (! empty($result)) {
                $type = "success";
                $message = "CSV Data Imported into the Database";
            } else {
                $type = "error";
                $message = "Problem in Importing CSV Data";
            }
        }
    }
}
?>

After importing the complete data from the CSV file it displays the stored data in a grid view. The code to retrieve the stored data and the grid view is as follows.

<?php
$sqlSelect = "SELECT * FROM users";
$result = mysqli_query($conn, $sqlSelect);
            
if (mysqli_num_rows($result) > 0) {
?>
<table id='userTable'>
    <thead>
        <tr>
            <th>User ID</th>
            <th>User Name</th>
            <th>First Name</th>
            <th>Last Name</th>

        </tr>
    </thead>
    <?php
	while ($row = mysqli_fetch_array($result)) {
    ?>

    <tbody>
        <tr>
            <td><?php  echo $row['userId']; ?></td>
            <td><?php  echo $row['userName']; ?></td>
            <td><?php  echo $row['firstName']; ?></td>
            <td><?php  echo $row['lastName']; ?></td>
        </tr>
     <?php
     }
     ?>
    </tbody>
</table>
<?php } ?>

Import CSV File into MySQL using PHP Output

This Screenshot displays the list of imported CSV data from the database after successful CSV file import.

import-csv-file-into-mysql-using-php-output

Download

This PHP code tutorial was published on February 24, 2018.

↑ Back to Top

Share this Article