How to Import and Export CSV Files Using PHP and MySQL

What is CSV? This is a comma-separated value file that stores large amounts of tabular data in plain text format. CSV files are mainly used to import or export data from a table.

This allows us to easily exchange large amounts of data exchange from a MySQL database server, because this requires less space than other spreadsheet formats like Excel. After exchanging data, we can use it according to our needs.

Most enterprise-level application data is imported and exported in CSV file format because we can quickly exchange data from a database server.

In this article we show you how to import data from CSV files and paste them into MySQL database tables using PHP Script. In this post, you will learn how to extract data from uploaded CSVs and then insert data into MySQL tables.

If you want to enter a large amount of data stored under a CSV file and put it in a MySQL database, you can use this type of concept at this time. To retrieve data from CSV data files, we use the simple function fgetcsv () to extract all data from CSV files, and then run an insert query to enter data into the table.

See also:

  1. Import Excel File into MySQL Database using PHP
  2. How to Export data to Excel sheet in PHP MySQL
  3. Generate PDF File From MySQL Database Using PHP

How to Import and Export CSV files using PHP and Mysql

Import and export of data to and from databases is a common enough procedure in PHP development. Another important activity is the backup and transfer of databases.

Create a Database in MySQL

The first step in this tutorial is the creation of a MySQL database. you can create tables by running SQL queries. Create a table ‘users’ in database using the following SQL query.

--
-- Database: `testing`
-- Table structure for table `users`
--
CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREAMENT,
  `name` varchar(50) NOT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `age` int(50) NOT NULL
  `city_name` int(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;  

This will create a new table ‘users’ in the database. I will use this table to insert data from the CSV file.

Create MySql Connection in PHP

For importing and exporting database in MySql will make a separate file ‘config.php’. Add the following code and replace the database credentials with yours. You can find your db credentials in Application Access details:

<?php
	// Database configuration 
	$dbHost     = "localhost"; 
	$dbUsername = "root"; 
	$dbPassword = ""; 
	$dbName     = "testing"; 
	 
	// Create database connection 
	$con = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName); 
	 
	// Check connection 
	if ($con->connect_error) { 
	    die("Connection failed: " . $con->connect_error); 
	} 
?>

Create a HTML page display records

After the database has been created, I next need an HTML file  that could upload CSV file. For this HTML file, I will use HTML File uploader in a simple bootstrap form.

Create a file and name it ‘index.php’. This is a simple form for uploading CSV file. This file will also show the results in a simple table on the same page. When the user submits the form,  all records will be saved in the database.

index.php

<!Doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>How to Import and Export CSV Files Using PHP and MySQL</title>
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container" style="margin-top:50px">
<h1 style="text-align:center;">How to Import and Export CSV Files Using PHP and MySQL</h1><br>
  <div class="row">
    <!--- this form is for the Import data--->
    <form action="import_data.php" method="POST" enctype="multipart/form-data" style="margin-left: 500px;">
      <div class="form-group">
        <label>Select File</label>
        <input type="file" name="file" class="form-control" required="">  
      </div>
      <div class="form-group">
        <input type="submit" name="import" class="btn btn-primary" value="Upload">  
      </div>
    </form>  
    
    <!--- This form is for the Export data--->
    <form action="export_data.php" method="POST" style="margin-left:1010px;margin-bottom: 20px;">
      <input type="submit" class="btn btn-success" name="export" value="Export to Excel">
    </form>

    <table class="table table-striped">
      <thead>
        <tr>
          <th>Id</th>
          <th>Name</th>
          <th>Email</th>
          <th>Username</th>
          <th>Age</th>
          <th>City</th>
        </tr>
      </thead>
      <?php
          // include database connectivity 

          include_once('config.php');

          // fetch data from users table 

          $query  = "SELECT * FROM users";
          $result = mysqli_query($con, $query);
          if (mysqli_num_rows($result) > 0) {
            while ($data = mysqli_fetch_assoc($result)) {
          ?>
          <tr>
            <td><?php echo $data['id']?></td>
            <td><?php echo $data['name']?></td>
            <td><?php echo $data['email']?></td>
            <td><?php echo $data['username']?></td>
            <td><?php echo $data['age']?></td>
            <td><?php echo $data['city_name']?></td>
          </tr>
        <?php } } ?> 
    </table>
  </div>
</body>
</html>

Create PHP code for Export data in CSV from Database Table

export_data.php

<?php

    // Include database connectivity 

    include_once('config.php');

    // Export file in php

    if (isset($_POST['export'])) {
    	$query  = "SELECT * FROM users";
    	$result = mysqli_query($con, $query);
    if (mysqli_num_rows($result) > 0) {
        $output  = "";
        $output .= "<table class='table table-striped'>
		        <thead>
			       <tr>
			         <th>Id</th>
			         <th>Name</th>
				     <th>Email</th>
			         <th>Username</th>
			         <th>Age</th>
			         <th>City Name</th>
			       </tr>
			    </thead>";
        while ($data = mysqli_fetch_assoc($result)) {
        $output.= "<tr>
			    	<td>".$data['id']."</td>
			    	<td>".$data['name']."</td>
			    	<td>".$data['email']."</td>
			    	<td>".$data['username']."</td>
			    	<td>".$data['age']."</td>
			    	<td>".$data['city_name']."</td>
				</tr>";
            }
            $output .="</table>";
            header('Content-Type: application/xls');
  	        header('Content-Disposition: attachment; filename=download.xls'); 
            echo $output;
        }else{
        	echo "No record found";
        }
    }
?>

Create PHP code for Import data in Database Table

import_data.php

<?php

	// include database connectivity 

    include_once('config.php');

    // Import file in php

    if (isset($_POST['import'])) {
    	
    	$fileName = $_FILES["file"]["tmp_name"];

    	if ($_FILES['file']['size'] > 0) {

    		$file = fopen($fileName, "r");

            while (($importData = fgetcsv($file, 10000, ",")) !== FALSE) {

                $name = "";
                if (isset($importData[0])) {
                    $name = mysqli_real_escape_string($con, $importData[0]);
                }
                $email = "";
                if (isset($importData[1])) {
                    $email = mysqli_real_escape_string($con, $importData[1]);
                }
                $username = "";
                if (isset($importData[2])) {
                    $username = mysqli_real_escape_string($con, $importData[2]);
                }
                $age = "";
                if (isset($importData[3])) {
                    $age = mysqli_real_escape_string($con, $importData[3]);
                }
                $city_name = "";
                if (isset($importData[4])) {
                    $city_name = mysqli_real_escape_string($con, $importData[4]);
                }

                $query = "INSERT INTO users (name, email, username, age, city_name) 
                VALUES('".$name."','".$email."','".$username."','".$age."','".$city_name."')";
                
                $result = mysqli_query($con, $query);

                if (!isset($result)) {
                    echo "<script type=\"text/javascript\">
                              alert(\"Invalid File:Please Upload CSV File.\");
                              window.location = \"index.php\"
                          </script>";
                }else{
                    echo "<script type=\"text/javascript\">
                              alert(\"CSV File has been successfully Imported.\");
                              window.location = \"index.php\"
                          </script>";
                }
            }
            fclose($file);
        }
    }

?>

Output

How to Import and Export CSV Files Using PHP and MySQL

Conclusion

In this article, I discussed how you could export data from and to CSV files using PHP and MySQL. This is a simple example you can Add more complex logic and validations as per your requirements.

You can always support by sharing on social media or recommending my blog to your friends and colleagues. If you have any suggestions / problems about this tutorial, please comment on the  form below.😊

Leave a Reply

Your email address will not be published. Required fields are marked *