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 application.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 tutorial 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 PHP script that will load and save CSV file data into php.

Create MySQL Database Connection

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

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

Now, I ll create connection.php file and added below code into this file. The below code help to connect to the MySQL databases, Also use to select the database using PHP and MySQL.

Export to CSV File using PHP

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

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

  • Fetach data from the employees table.
  • Open the file using fopen() function.
  • Define the header columns and put into the CSV file.
  • Read one by one each row of the data, and write to file pointer.
  • We ll force the browser to download the CSV file using Content-Type and Content-Disposition.

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

Import CSV data into the MySQL Database

Let’s import emp.csv file data into the MySQL database. I will use 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 following steps to export data into the 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 stored in memory using fgetcsv() method.The while loop is use to saved 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