Import & Export data to Excel, CSV and MySQL in PHP

Last updated on January 9th, 2020 at 12:29 pm

Exporting data from database to Excel / CSV file is not an easy task to do especially for beginners who just start development in php. This tut for those who want’s to create Excel / CSV file online from database. I am using PHP to do this task and write small but effective code to learn this thing.

Step 1: SQL 

First we create a database where we insert our data.

--
-- Database: `excel`
--

-- --------------------------------------------------------

--
-- Table structure for table `info`
--

CREATE TABLE IF NOT EXISTS `info` (
  `name` text NOT NULL,
  `age` text NOT NULL,
  `work` text NOT NULL,
  `status` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `info`
--

INSERT INTO `info` (`name`, `age`, `work`, `status`) VALUES
('Aneh Thakur', '22', 'Android & PHP', 'Love think Code!!'),
('Amit Rana', '22', 'PHP & Iphone', 'Happy'),
('Mandeep', '23', 'PHP', ''),
('Lucky', '23', 'RJ', 'Sanjh Radio');

Step 2. Create connection to Database

Now we create a file { conn.php } where we can create connection to our database.

<?php
// Connection
$con = mysql_connect("localhost", "root", "");
$db = mysql_select_db("excel", $con);
?>

Step 3. Upload .csv file to server and read data from CSV file

First we need know what is CSV before we are going to do further.

CSV

A comma-separated values (CSV) (also sometimes called character-separated values, because the separator character does not have to be a comma) file stores tabular data (numbers and text) in plain-text form. Plain text means that the file is a sequence of characters, with no data that has to be interpreted instead, as binary numbers. { wiki }.

We need to upload .csv file to server before we read data from file. I assume that you already know how we can upload any file to server using form. After uploading file to server we open that file using PHP fopen() function and read data and insert data to database from file as display below in code.

<?php
include_once('conn.php');
$fo = fopen('file.csv', "r"); // CSV fiile
while (($emapData = fgetcsv($fo, "", ",")) !== FALSE)
{
      $sql = "INSERT into info (name,age,work,status) values ('$emapData[0]','$emapData[1]','$emapData[2]','$emapData[3]')";
      mysql_query($sql);
}
?>

Step 4. Export data from MySQL to CSV file 

Now we can export data from database to .CSV file and trigger download of that file in browser using php header() function as shown in code below.

<?php

// Connection
include_once('conn.php');

$sql = "select * from info";
$qur = mysql_query($sql);

// Enable to download this file
$filename = "sampledata.csv";

header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: text/csv");

$display = fopen("php://output", 'w');

$flag = false;
while($row = mysql_fetch_assoc($qur)) {
    if(!$flag) {
      // display field/column names as first row
      fputcsv($display, array_keys($row), ",", '"');
      $flag = true;
    }
    fputcsv($display, array_values($row), ",", '"');
  }

fclose($display);
?>

Step 5. Export data from MySQL to Excel file

In this step we export or database data to Excel.

<?php
// Connection 
include_once('conn.php');

$filename = "sampledata.xls"; // File Name

// Download file
header("Content-Disposition: attachment; filename=\"$filename\""); 
header("Content-Type: application/vnd.ms-excel");

// Write data to file
$flag = false;
while($row = mysql_fetch_assoc($qur)) {
    if(!$flag) {
      // display field/column names as first row
      echo implode("\t", array_keys($row)) . "\r\n";
      $flag = true;
    }
    echo implode("\t", array_values($row)) . "\r\n";
  }
?>

This is a simple and easy tutorial to create excel or csv file using MySQL database store data.

Thanku!!.