This website uses cookies

Our website, platform and/or any sub domains use cookies to understand how you use our services, and to improve both your experience and our marketing relevance.

📣 Try the fastest hosting platform with pay-as-you-go pricing & 24/7 expert support! MIGRATE NOW →

How to Import and Export CSV Files Using PHP and MySQL

Updated on December 9, 2021

7 Min Read

PHP is widely used for building a wide range of products ranging from web apps to enterprise level applications. The key to efficient PHP code is to follow proper workflows and automate processes. The result is high quality and bug-free code.

In almost all PHP applications, data is stored, accessed and exchanged between various components of the app. To make sure that this exchange and access to data goes smoothly and without any issues, the development team must make sure that the databases and data dumps are in proper format.

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.

In this article, I will explain how to save tables from CSV files to MySQL and vice versa. You need to signup at Cloudways to launch a server and PHPstack application. Before signing up, looking at all the pricing options from the world-class hosting providers like AWS, DigitalOcean, Linode, Vultr and GCP is a good idea so you can find the one that perfectly fits your needs.

Host PHP Websites with Ease [Starts at $10 Credit]

  • Free Staging
  • Free backup
  • PHP 8.0
  • Unlimited Websites

TRY NOW ⇒

Create a Database in MySQL

The first step in this tutorial is the creation of a MySQL database. Since Cloudways provides the custom mysql manager in the platform which contains a database for app. you can create tables by running SQL queries. Create a table `employeeinfo` in database using the following SQL query.

CREATE TABLE employeeinfo(
emp_id VARCHAR(50) UNSIGNED PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date VARCHAR(50)
)

This will create a new table `employeeinfo` in the database. I will use this table to insert data from the CSV file.

Stop Wasting Time on Servers

Cloudways handle server management for you so you can focus on creating great apps and keeping your clients happy.

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
function getdb(){
$servername = "localhost";
$username = "huscqxzwaw";
$password = "2WWKxxxxHr";
$db = "huscqxzwaw";

try {
   
    $conn = mysqli_connect($servername, $username, $password, $db);
     //echo "Connected successfully"; 
    }
catch(exception $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
    return $conn;
}
?>

Related: How To Connect MySQL Database With PHP Websites

Import CSV to MySQL in PHP

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.

First, I will add Bootstrap CDN to index.php.

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" crossorigin="anonymous">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" crossorigin="anonymous">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" crossorigin="anonymous"></script>

Next, in the `body` tag,  add the following HTML code for the Bootstrap form.

<!DOCTYPE html>
<html lang="en">

<head>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" crossorigin="anonymous">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" crossorigin="anonymous">
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" crossorigin="anonymous"></script>

</head>

<body>
    <div id="wrap">
        <div class="container">
            <div class="row">

                <form class="form-horizontal" action="functions.php" method="post" name="upload_excel" enctype="multipart/form-data">
                    <fieldset>

                        <!-- Form Name -->
                        <legend>Form Name</legend>

                        <!-- File Button -->
                        <div class="form-group">
                            <label class="col-md-4 control-label" for="filebutton">Select File</label>
                            <div class="col-md-4">
                                <input type="file" name="file" id="file" class="input-large">
                            </div>
                        </div>

                        <!-- Button -->
                        <div class="form-group">
                            <label class="col-md-4 control-label" for="singlebutton">Import data</label>
                            <div class="col-md-4">
                                <button type="submit" id="submit" name="Import" class="btn btn-primary button-loading" data-loading-text="Loading...">Import</button>
                            </div>
                        </div>

                    </fieldset>
                </form>

            </div>
            <?php
               get_all_records();
            ?>
        </div>
    </div>
</body>

</html>

import form

You might notice that I have set an action to `functions.php` file. In the next step, I will create this file and add code to it. I have also include a method `get_all_records()` near the end of the file. This method fetches all the records from the database and display the records in the table on the index page.

Next up, I will create `functions.php` file and add the following code in it.

<?php


 if(isset($_POST["Import"])){
		
		$filename=$_FILES["file"]["tmp_name"];		


		 if($_FILES["file"]["size"] > 0)
		 {
		  	$file = fopen($filename, "r");
	        while (($getData = fgetcsv($file, 10000, ",")) !== FALSE)
	         {


	           $sql = "INSERT into employeeinfo (emp_id,firstname,lastname,email,reg_date) 
                   values ('".$getData[0]."','".$getData[1]."','".$getData[2]."','".$getData[3]."','".$getData[4]."')";
                   $result = mysqli_query($con, $sql);
				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);	
		 }
	}	 


 ?>

When the upload button is clicked, the temporary file name will be stored in memory and using the `while` loop the data is saved in $getData variable. Once the process has been completed, the data is sorted column wise and then finally inserted in the `employeeinfo` table.

Note that `fgetcsv()` parses lines from the open file, checking for CSV fields and `fopen()` opens a file or a URL. This code could be tested by importing a CSV file with test data.

Display the Saved Records

Once the CSV file has been imported, I will display the data through a simple function, `get_all_records()`, initialized in `index.php`. Copy this function to `function.php`.

function get_all_records(){
    $con = getdb();
    $Sql = "SELECT * FROM employeeinfo";
    $result = mysqli_query($con, $Sql);  


    if (mysqli_num_rows($result) > 0) {
     echo "<div class='table-responsive'><table id='myTable' class='table table-striped table-bordered'>
             <thead><tr><th>EMP ID</th>
                          <th>First Name</th>
                          <th>Last Name</th>
                          <th>Email</th>
                          <th>Registration Date</th>
                        </tr></thead><tbody>";


     while($row = mysqli_fetch_assoc($result)) {

         echo "<tr><td>" . $row['emp_id']."</td>
                   <td>" . $row['firstname']."</td>
                   <td>" . $row['lastname']."</td>
                   <td>" . $row['email']."</td>
                   <td>" . $row['reg_date']."</td></tr>";        
     }
    
     echo "</tbody></table></div>";
     
} else {
     echo "you have no records";
}
}

In this really simple method, I simply selected all the records and displayed these records on the index page through the method. Whenever the user uploads a CSV file, the records will get saved in the table and then displayed on the index page.

Export MySQL to CSV With PHP

Exporting data from  MySQL database to a CSV file is similarly very easy. To demonstrate this, I will use the index.php that I created earlier.

Add the following code to the file.

 <div>
            <form class="form-horizontal" action="functions.php" method="post" name="upload_excel"   
                      enctype="multipart/form-data">
                  <div class="form-group">
                            <div class="col-md-4 col-md-offset-4">
                                <input type="submit" name="Export" class="btn btn-success" value="export to excel"/>
                            </div>
                   </div>                    
            </form>           
 </div>

After adding this HTML markup, the Export button will appear below the table. Now add the following condition in functions.php.

 if(isset($_POST["Export"])){
		 
      header('Content-Type: text/csv; charset=utf-8');  
      header('Content-Disposition: attachment; filename=data.csv');  
      $output = fopen("php://output", "w");  
      fputcsv($output, array('ID', 'First Name', 'Last Name', 'Email', 'Joining Date'));  
      $query = "SELECT * from employeeinfo ORDER BY emp_id DESC";  
      $result = mysqli_query($con, $query);  
      while($row = mysqli_fetch_assoc($result))  
      {  
           fputcsv($output, $row);  
      }  
      fclose($output);  
 }  

When the `Export` button is clicked, the headers `Content-Type: text/csv` with an attachement `data.csv` is sent.

Since `php://output` is a write-only stream that allows write access to the output buffer mechanism, I selected all data from table in the next line, and passed it to `fputcsv()` method. This method formats a line (passed as a fields array) as CSV and write it (terminated by a newline) to the specified file. Finally, the file with all the desired data is downloaded.

Finally, after integrating all the code, you will see the following final shape of application.

cloudways import excel

Supercharged Managed PHP Hosting – For 10X Speed and Ironclad Security

Unleash the true power of your website with PHP hosting – where speed, security, and seamless performance come together for an exceptional online experience!

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 also create test cases to verify the code and Integerate with GitHub using PHP Continuous Integeration Tools. If you wish to add to the discussion or would like to ask a question, leave a comment below and embark on your coding journey empowered by best PHP hosting service.

How do I import and export CSV using php and MySQL?

  • Approve the submitted record, whether a substantial CSV file.
  • Inspect the CSV file transfer status utilizing PHP is_uploaded_file() function.
  • Access the CSV file utilizing PHP fopen() function.
  • Parse data from the CSV record utilizing PHP fgetcsv() function.
  • Insert or updade data into the database based on the member’s e-mail.

Are there any PHP libraries or functions specifically designed for importing and exporting CSV data?

Yes. When importing or exporting large CSV files with PHP and MySQL, here are important considerations to keep in mind:

  • fgetcsv(): A built-in PHP function that reads a line from a CSV file and returns an array of fields.
  • League\Csv: A library offering a simple API for importing and exporting CSV data, with features like reading and writing CSV files,
  • handling headers, and managing encoding.
  • PhpSpreadsheet: A comprehensive library supporting various file formats, including CSV.
  • ParseCsv: A lightweight PHP library dedicated to handling CSV data.

Are there any limitations or considerations when importing or exporting large CSV files with PHP and MySQL?

When importing or exporting large CSV files with PHP and MySQL, keep these considerations in mind:

  • Memory usage: Allocate sufficient memory.
  • Execution time: Adjust time limits or process in smaller chunks.
  • Chunking and pagination: Divide the file into smaller sections.
  • Database constraints: Ensure the database can handle the data size and structure.
  • Data validation and sanitization: Validate and sanitize the data.
  • File encoding and format: Match the expected encoding and format.
  • Error handling and logging: Implement robust error handling and logging mechanisms.

What are some alternatives to PHP and MySQL for importing and exporting CSV data?

Here are some alternatives to PHP and MySQL for importing and exporting CSV data:

  • Python with pandas: Offers powerful data manipulation capabilities.
  • R programming language: Ideal for statistical computing and data analysis.
  • Java with Apache Commons CSV: Provides flexible CSV handling options.
  • Command-line tools (e.g., awk, sed, grep): Powerful text processing tools.
  • Spreadsheet software (e.g., Excel, Google Sheets): User-friendly interfaces for smaller datasets.
Share your opinion in the comment section. COMMENT NOW

Share This Article

Shahroze Nawaz

Shahroze is a PHP Community Manager at Cloudways - A Managed PHP Hosting Platform. Besides his work life, he loves movies and travelling.

×

Get Our Newsletter
Be the first to get the latest updates and tutorials.

Thankyou for Subscribing Us!

×

Webinar: How to Get 100% Scores on Core Web Vitals

Join Joe Williams & Aleksandar Savkovic on 29th of March, 2021.

Do you like what you read?

Get the Latest Updates

Share Your Feedback

Please insert Content

Thank you for your feedback!

Do you like what you read?

Get the Latest Updates

Share Your Feedback

Please insert Content

Thank you for your feedback!

Want to Experience the Cloudways Platform in Its Full Glory?

Take a FREE guided tour of Cloudways and see for yourself how easily you can manage your server & apps on the leading cloud-hosting platform.

Start my tour

CYBER WEEK SAVINGS

  • 0

    Days

  • 0

    Hours

  • 0

    Mints

  • 0

    Sec

GET OFFER

For 4 Months &
40 Free Migrations

For 4 Months &
40 Free Migrations

Upgrade Now