We can import the CSV files in PHP and store it into the database. The CSV (Comma-Separated Value) data can be imported easily using PHP Import CSV procedure. When we have a bunch of data to insert it into the database, then, in that case, inserting records one by one is very time-consuming. So to make it more easy and convenient, we can create a data-sheet in CSV format and then the CSV data can be imported easily to the database. So, here I am going to show you how to upload CSV file in PHP.
PHP Import CSV File
It is not possible to insert each record through the form when you have to insert the large data. Inserting a single-single record each time is a huge time taking process. So to fix these issues we can insert the data one time by importing it.
Check If Email Available in PHP MySQL Using AJAX
While importing the CSV file, every single line will be iterated one by one through the loop. The function will read the data line by line and on every iteration, we’ll be storing the record into the database. So, let’s create a new project in PHP.
PHP Import CSV Project
I will be using a PHP file upload concept, in which we’ll upload a datasheet that will be in CSV format. So, in the very first step, I will create a PHP file that will contain a form with an input type file and a button.
Create the index.php file and paste the code.
<!DOCTYPE html>
<html>
<head>
<title>Import CSV in PHP </title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<form method="post" enctype="multipart/form-data">
<div class="row mt-5">
<div class="col-md-6 m-auto border">
<label> Import Data </label>
<div class="form-group">
<input type="file" name="file" class="form-control">
</div>
<div class="form-group">
<button type="submit" name="import" class="btn btn-success"> Import Data </button>
</div>
</div>
</div>
</form>
</div>
</body>
</html>When you will run the above code, the result will look like this.
Drag and Drop Multiple File Upload in PHP Using Dropzone js
Create an excel sheet with some data. Here, I have created a sheet for some products. I will be importing these data.
Now, according to the above data, I will create a table in the database. So let’s connect our application to the database.
Create Database and Connection
- Here, my database is csv_importer and inside this database, I have created a table named products.
- Below, is the database structure. You simply create a database and paste the code there.
--
-- Database: `csv_importer`
--
-- --------------------------------------------------------
--
-- Table structure for table `products`
--
CREATE TABLE `products` (
`product_id` int(11) NOT NULL,
`product_name` varchar(100) NOT NULL,
`sku` varchar(50) NOT NULL,
`brand` varchar(50) NOT NULL,
`quantity` int(11) NOT NULL,
`price_per_unit` varchar(50) NOT NULL,
`created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `products`
--
ALTER TABLE `products`
ADD PRIMARY KEY (`product_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
MODIFY `product_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;- For database connection, create a separate folder and inside that folder create a file.
- In my case, I’ve created a folder named config and inside this folder, I have created a db-config.php file for creating a connection.
- So, the folder structure will look like this.
PHP File Upload Using jQuery and Ajax without Form Refresh
Now, paste this code inside the db-config.php file.
<?php
class DBController {
private $hostname = "localhost";
private $username = "root";
private $password = "root";
private $db = "csv_importer";
// Creating connection
public function connect() {
$conn = new mysqli($this->hostname, $this->username, $this->password, $this->db)or die("Database connection error." . $conn->connect_error);
return $conn;
}
// Closing connection
public function close($conn) {
$conn->close();
}
}
?>Make sure, you have replaced your database credentials in the above code. Now, moving to the function to upload CSV file in PHP.
Dependent Dropdown Selection Filter in PHP Using jQuery and AJAX
Upload CSV File in PHP
For uploading the CSV file, I will use the OOPs approach. So, I will create a Controller file in which, I will create the function for uploading the CSV file.
- Open index.php file and paste the below code at the top of the HTML.
<?php
// including db config file
include_once './config/db-config.php';
// including import controller file
include_once './controllers/import-controller.php';
// creating object of DBController class
$db = new DBController();
// calling connect() function using object
$conn = $db->connect();
// creating object of import controller and passing connection object as a parameter
$importCtrl = new ImportController($conn);
?>- In the above code, I have included a connection and import-controller file.
- Then, created the objects of both classes which are inside that file.
- I have passed the connection object in the class of ImportController as a parameter.
- Consequently, in the constructor of the ImportController class, we can get the connection object and furthermore, we can use the connection object as a global in that class.
Ajax PHP Form Handling Using jQuery without Form Refresh
Create ImportController File
- In the project directory, create a new folder with name controllers and inside this folder create a new file named
import-controller.php. - In the next step, simply paste the below code in the import-controller.php file.
<?php
class ImportController {
// getting connection in constructor
function __construct($conn) {
$this->conn = $conn;
}
// function for reading csv file
public function index() {
$fileName = "";
// if there is any file
if(isset($_FILES['file'])) {
// reading tmp_file name
$fileName = $_FILES["file"]["tmp_name"];
}
$counter = 0;
// if file size is not empty
if (isset($_FILES["file"]) && $_FILES["file"]["size"] > 0) {
$file = fopen($fileName, "r");
// eliminating the first row of CSV file
fgetcsv($file); ?>
<table class="table">
<thead>
<th> Sl </th>
<th> Product Name </th>
<th> SKU </th>
<th> Brand </th>
<th> Quantity </th>
<th> Price Per Unit </th>
<th> Response </th>
</thead>
<?php
while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
$counter++;
// assigning csv column to a variable
$product_name = $column[0];
$sku = $column[1];
$brand = $column[2];
$quantity = $column[3];
$price_per_unit = $column[4];
// inserting values into the table
$sql = "INSERT INTO products (product_name, sku, brand, quantity, price_per_unit) VALUES ('$product_name', '$sku', '$brand', '$quantity', '$price_per_unit') ";
$result = $this->conn->query($sql);
if($result == 1): ?>
<tr>
<td> <?php echo $counter; ?> </td>
<td> <?php echo $product_name; ?> </td>
<td> <?php echo $sku; ?> </td>
<td> <?php echo $brand; ?> </td>
<td> <?php echo $quantity; ?> </td>
<td> <?php echo $price_per_unit; ?> </td>
<td> <?php echo "<label class='text-success'>Success </label> " .date('d-m-Y H:i:s');?> </td>
</tr>
<?php endif;
}
?>
</table>
<?php
}
else{
}
}
}
?>Finally, move to the index.php file and paste the single line of code before the closing of the main div, so that it will display the values in a table which will be inserting into the database.
<div class="row mt-4">
<div class="col-md-10 m-auto">
<?php
$importResult = $importCtrl->index();
?>
</div>
</div>Now, after the above code, the index.php file will become like this –
<?php
// including db config file
include_once './config/db-config.php';
// including import controller file
include_once './controllers/import-controller.php';
// creating object of DBController class
$db = new DBController();
// calling connect() function using object
$conn = $db->connect();
// creating object of import controller and passing connection object as a parameter
$importCtrl = new ImportController($conn);
?>
<!DOCTYPE html>
<html>
<head>
<title>Import CSV in PHP </title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<form method="post" enctype="multipart/form-data">
<div class="row mt-5">
<div class="col-md-6 m-auto border shadow">
<label> Import Data </label>
<div class="form-group">
<input type="file" name="file" class="form-control">
</div>
<div class="form-group">
<button type="submit" name="import" class="btn btn-success"> Import Data </button>
</div>
</div>
</div>
<div class="row mt-4">
<div class="col-md-10 m-auto">
<?php
$importResult = $importCtrl->index();
?>
</div>
</div>
</form>
</div>
<script src="https://code.jquery.com/jquery-3.2.1.slim.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
</body>
</html>- Save and run the project to see the result. Select the CSV file and click on the Import Data button.
- Here, you can see the data has been imported successfully and the records which have been inserted into the database has displayed before inserting.
Even more, you can see in the database table, the records have been inserted successfully.
Implement jQuery DataTable in PHP MySQL Database
Conclusion
Finally, we have learned how to upload a CSV file in PHP. Also, we have stored the values into the database table of the uploaded CSV file. I hope, this technique will help you a lot in such type of situation when you will have to insert more than one data at a time. For the reason, that single-single record insertion is the time taking process. So, don’t forget to ask through the comment if you find any problem or query in this post. I will be glad to help you.
Hi, thank you for the great code. I am however experiencing an issue… “// if file size is not empty
if (isset($_FILES[“file”]) && $_FILES[“file”][“size”] > 0) {
$file = fopen($fileName, “r”);
”
appears to be causing an error, when this is commented out the code does work and imports the file successfully however on first loading of the page there is a number of errors, which I believe are caused by this being commented out.. please could you advise a solution?
Hi Richard, actually the problem is with the syntax highlighter. The actual code is this –
if (isset($_FILES[“file”]) && $_FILES[“file”][“size”] > 0) {
$file = fopen($fileName, “r”);
The syntax highlighter does not understand the && symbol and it adds & for ampersand symbol. So just modify your code with the above-mentioned code. I am damn sure it will work absolutely fine.
Thank you so much, all working perfect.
Hello,
thanks for your amazing work. One question…
Is it possible to replace the uploaded Data with the old one in the mysql database?
Can you write me the code and where I can put it in? I have no idea but I need this function 🙁
At the moment the new data is added to the old one..
Regards
Sebastian
Yes, you can update the duplicate data from the database. Just do one thing, make a condition and check the data from the database table on the basis of a field that has the same value in both (CSV sheet and database table). Then check the data, if it founds then just create an update query with the same data array.
How can I validate that only allows to upload .CSV files? And when you for example, upload a image or another kind of file I shows me an error and try again.
Another question…
Also works with .TXT files?
Thanks by the way!
You can validate the file type before uploading it. You can use the below validation rule to upload only CSV file.
$fileType = $_FILES['type'];if($fileType == "csv") {
--- your code will go here ------
}
else {
}
Sorry, but where i should put it?
You can use validation before reading the file content.
Is there a way to do live editing of the data which is shown in the preview?