How To Import/Export CSV Data Using MySQLi and PHP 7

This tutorial help to import the CSV data into MySQL and export data from MySQL to csv file. The CSV file is used to import and export data for moving/exchange data information between web applications. You can use rest API or web application to import/export CSV data.

The web application data is stored, accessed and exchanged between various components using CSV format. The CSV(comma-separated values) most popular file format to store data in plain text.

You can also check other tutorials of Export Data with PHP

How To Import and Export CSV data in PHP

In this tutorial, I will show you how to import and export data from MySQL database to/from CSV file using PHP. I will not create HTML form to upload CSV file, I’ll provide a PHP script that will load and save CSV file data into PHP.

Create MySQL Database Connection

Let’s create an employees Table in MySQL Database, that ll use for import and export CSV data into the database.
The following SQL creates an employees table with some basic fields in the MySQL database. The employees table holds the employee information which needs to be exported.

The following SQL creates an employees table with some basic fields in the MySQL database. The employees table holds the employee information which needs to be exported.

CREATE TABLE `employees` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `age` int(11) NOT NULL,
 `salary` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Now, I’ll create a connection.php file and add the below code to this file. The below code helps to connect to the MySQL databases, Also use to select the database using PHP and MySQL.

//DB details
 $dbHost     = 'localhost';
 $dbUsername = 'root';
 $dbPassword = '*';
 $dbName     = 'phpflow';//Create connection and select DB
 $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 
//Create connection and select DB
 $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

if($db->connect_error){

     die("Unable to connect database: " . $db->connect_error);

 }

Export to CSV File using PHP

Let’s export data from the database using PHP and MySQL. We’ll create export_csv.php file and write code to fetch data from employees table and save it into the emp.csv file. We’ll store all employee exported data into employee.csv file.

This file will have the following steps to export data into MySQL.

Fetch data from the employees table.

Open the file using fopen() function.
Define the header columns and put them into the CSV file.
Read one by one each row of the data, and write to the file pointer.

We’ll force the browser to download the CSV file using Content-Type and Content-Disposition.

Let’s add the below code into export_csv.php file.

query("SELECT * FROM employees ORDER BY id DESC");

if($query->num_rows > 0){
    $delimiter = ",";
    $filename = "employee.csv";

    //create a file pointer for write
    $f = fopen('php://memory', 'w');

    //set column headers
    $fields = array('ID', 'Name', 'Age', 'Salary');
    fputcsv($f, $fields, $delimiter);

    //write to file
    while($row = $query->fetch_assoc()){
        $lineData = array($row['id'], $row['name'], $row['age'], $row['salary']);
        fputcsv($f, $lineData, $delimiter);
    }
    fseek($f, 0);

    //set headers to download file
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="' . $filename . '";');

    fpassthru($f);
}
exit;

Import CSV data into the MySQL Database

Let’s import emp.csv file data into the MySQL database. I will use the employees table to insert data from the CSV file. Create import_csv.php file and added below code.

I am assuming you have file upload HTML form and want to script to store CSV file data into MySQL database.

This file will have the following steps to export data into MySQL.

Create a read file pointer using fopen() function..

Parse line by line CSV file data using fgetcsv() function.
Inserted CSV data into the employees table.

The CSV data is stored in memory using fgetcsv() method. The while loop is used to save data in $emps variable. Once the process has been completed, the data is sorted column-wise and inserted in the employees table.

Conclusion

In this tutorial, We have discussed how to import/export data from and to CSV file using PHP 7 and MySQLi. You can extend the import/export functionality as per your requirements/need. You can also create export and import data functionality easily using JavaScript –Export HTML Table Data to CSV using JavaScript

How To Import and Export CSV data in PHP

Create MySQL Database Connection

Export to CSV File using PHP

Import CSV data into the MySQL Database

Conclusion

Leave a Reply Cancel reply