How to Create a Csv Upload Page With Php

Import CSV File into MySQL using PHP

past Vincy. Last modified on May 26th, 2021.

In this tutorial, I will walk you lot 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. At that place are various ways to read the CSV information and check my previous linked commodity for a different option.

I usedfgetcsv()office to read the database table column data from the CSV.

If y'all are looking for PHP lawmaking to restore SQL script into database, delight check my previous article instance of restoring the database by importing the SQL script.

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

HTML Form to Import CSV File

This HTML lawmaking is used to show a class with the CSV file import option. After submitting the file, PHP lawmaking parses the CSV data and prepares INSERT query to load the data into the database.

Afterward 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 past specifying .csv using the take attribute.

Subsequently importing the complete data from the CSV file it displays the stored data in a grid view. The code to call back the stored data and the filigree view is as follows.

<h2>Import CSV file into Mysql using PHP</h2>      <div id="response"         grade="<?php if(!empty($type)) { repeat $type . " display-block"; } ?>">         <?php if(!empty($bulletin)) { echo $message; } ?>         </div>     <div class="outer-scontainer">         <div class="row">              <grade class="form-horizontal" activeness="" method="post"                 name="frmCSVImport" id="frmCSVImport"                 enctype="multipart/form-data">                 <div class="input-row">                     <label class="col-doc-4 control-label">Choose CSV                         File</label> <input blazon="file" name="file"                         id="file" have=".csv">                     <push type="submit" id="submit" name="import"                         class="btn-submit">Import</push button>                     <br />                  </div>              </class>          </div>                <?php             $sqlSelect = "SELECT * FROM users";             $upshot = $db->select($sqlSelect);             if (! empty($consequence)) {                 ?>             <tabular array id='userTable'>             <thead>                 <tr>                     <th>User ID</th>                     <th>User Name</thursday>                     <thursday>First Name</th>                     <th>Concluding Proper noun</th>                  </tr>             </thead> <?php                                  foreach ($issue as $row) {                     ?>                                      <tbody>                 <tr>                     <td><?php  echo $row['userId']; ?></td>                     <td><?php  echo $row['userName']; ?></td>                     <td><?php  echo $row['firstName']; ?></td>                     <td><?php  repeat $row['lastName']; ?></td>                 </tr>                     <?php                 }                 ?>                 </tbody>         </table>         <?php } ?>     </div>        

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 volition 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).prepare(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.examination($("#file").val().toLowerCase())) {         	    $("#response").addClass("error");         	    $("#response").addClass("display-block");             $("#response").html("Invalid File. Upload : <b>" + fileType + "</b> Files.");             return imitation;         }         return true;     }); }); </script>        

PHP Code to Import CSV Data to MySQL

In PHP code, information technology reads the uploaded CSV file and parses the data. It opens the input file in reading mode and gets the column data using fgetcsv().

Information technology keeps on go along this process in a loop until information technology reaches the terminate of the CSV file. For each iteration. information technology gets the assortment of column information of a single record. It prepares INSERT query using these data and executes information technology to load the data into the database.

<?php use Phppot\DataSource;  require_once 'DataSource.php'; $db = new DataSource(); $conn = $db->getConnection();  if (isset($_POST["import"])) {          $fileName = $_FILES["file"]["tmp_name"];          if ($_FILES["file"]["size"] > 0) {                  $file = fopen($fileName, "r");                  while (($column = fgetcsv($file, 10000, ",")) !== Imitation) {                          $userId = "";             if (isset($column[0])) {                 $userId = mysqli_real_escape_string($conn, $column[0]);             }             $userName = "";             if (isset($column[ane])) {                 $userName = mysqli_real_escape_string($conn, $column[1]);             }             $password = "";             if (isset($column[2])) {                 $countersign = mysqli_real_escape_string($conn, $column[ii]);             }             $firstName = "";             if (isset($column[iii])) {                 $firstName = mysqli_real_escape_string($conn, $column[three]);             }             $lastName = "";             if (isset($column[four])) {                 $lastName = mysqli_real_escape_string($conn, $column[four]);             }                          $sqlInsert = "INSERT into users (userId,userName,countersign,firstName,lastName)                    values (?,?,?,?,?)";             $paramType = "issss";             $paramArray = array(                 $userId,                 $userName,                 $password,                 $firstName,                 $lastName             );             $insertId = $db->insert($sqlInsert, $paramType, $paramArray);                          if (! empty($insertId)) {                 $type = "success";                 $message = "CSV Data Imported into the Database";             } else {                 $blazon = "error";                 $message = "Problem in Importing CSV Data";             }         }     } } ?>        

Download

↑ Back to Elevation

marshallwituarmay92.blogspot.com

Source: https://phppot.com/php/import-csv-file-into-mysql-using-php/

Share this post