
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.
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.
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.
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.
<h2>Import CSV file into Mysql using PHP</h2> <div id="response" class="<?php if(!empty($type)) { echo $type . " display-block"; } ?>"> <?php if(!empty($message)) { echo $message; } ?> </div> <div class="outer-scontainer"> <div class="row"> <form class="form-horizontal" action="" method="post" name="frmCSVImport" id="frmCSVImport" 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> </form> </div> <?php $sqlSelect = "SELECT * FROM users"; $result = $db->select($sqlSelect); if (! empty($result)) { ?> <table id='userTable'> <thead> <tr> <th>User ID</th> <th>User Name</th> <th>First Name</th> <th>Last Name</th> </tr> </thead> <?php foreach ($result as $row) { ?> <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 } ?> </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 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>
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 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, ",")) !== FALSE) { $userId = ""; if (isset($column[0])) { $userId = mysqli_real_escape_string($conn, $column[0]); } $userName = ""; if (isset($column[1])) { $userName = mysqli_real_escape_string($conn, $column[1]); } $password = ""; if (isset($column[2])) { $password = mysqli_real_escape_string($conn, $column[2]); } $firstName = ""; if (isset($column[3])) { $firstName = mysqli_real_escape_string($conn, $column[3]); } $lastName = ""; if (isset($column[4])) { $lastName = mysqli_real_escape_string($conn, $column[4]); } $sqlInsert = "INSERT into users (userId,userName,password,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 { $type = "error"; $message = "Problem in Importing CSV Data"; } } } } ?>
This Screenshot displays the list of imported CSV data from the database after successful CSV file import.
Thank you for the clear and simple tutorial.
Welcome Hyder. Keep reading and sharing.
Thank you very much for your contribution, it works perfect, it is a great help
Welcome Etor.
Thank you
Welcome Mustafa.
Excellent. Thanks for doing this.
Welcome Andy. Keep reading and sharing.
really helpful
Thank you Kawsikan.
Working Fine
Thank you Sumanta.
Thank you so much.
You saved a lot of time to me.
It’s so nice coding.
Welcome Prabha.
Hi Vincy Excellent job you have done. But i am facing an issue. My csv file has 85k rows in it and when i import it does not work. Your kind help is needed.
Hi Maria,
Thank you. What is the error you are getting. This code should work seamlessly for 85K records.
Thank you for this.
Really appreciate it much.
Helped me in my lessons to teach PHP.
More power!
So happy to hear that Angel. I am glad you are teaching PHP.
thank you very much for this simple tutorial
Thank you Hicham.
Thank you vincy… Your tutorial simple and easy to learn,
I often visit your web this.
Welcome Yaqin. Keep visiting :-)
Is there a way to skip the first row if the csv has headings?
Yes Chris. Just use a counter variable and use it to skip the first row.
Vincy,
Thanks. It works great.
In the internet it is difficult to get such hassle free guide.
regards
Wow! Thank you Yakub for the appreciation.
You are the best. There are so many scripts out there, none of that worked for me. saved me a lot of time. Thank you soo much
Thank you Pradeep.
Thank you :)
Welcome Fouad.
Thanks!
Welcome Feri.
I am just learning the ropes – the article was great
Thank you Raja.
Excellent! I had not tried yet, but am going to try this. by the reviews, I can understand your best performances…. Thanks Vincy “the great Programmer” I liked your best part of replies
Thank you Dhirendra for your nice words.
Thank you very much for your contribution, it works perfect, it is a great help
Welcome Deep.
Thank you very much
Welcome Sadik.
Hi great tutorial and working very well for me. My csv contains titles in row 1. How do I set to ignore this row?
Hi Rce,
Just declare a counter variable outside the while loop and increment it inside the loop. When it is ‘1’, just skip the processing.
Thank you so much.
Welcome.
thankyou
Welcome Luluk.
Thank you! Worked like a charm.
Welcome Laurie.