Import and Export to CSV file using PHP and MySql

by  
Share:
Import and Export to CSV file using PHP and MySql

One of the most common feature you will find in almost all website/ web-store is the ability to backup or save database to external csv file. It’s also very helpful in many ways say like you want to share your products information or you want to bulk upload products via a csv file rather than inserting on one-on-one basis. In this tutorial I will be explaining how to import as well as export csv file using php and mysql database.
View Demo

CSV stands for comma separated values (although the separator character does not need to be comma).

Export database records to csv file

Lets first create a table name tbl_products1 And then add some dummy data to it.

CREATE TABLE IF NOT EXISTS `tbl_products1` (
  `products_id` int(11) NOT NULL auto_increment,
  `products_name` varchar(255) NOT NULL,
  `products_quantity` int(11) NOT NULL,
  `products_model` varchar(255) NOT NULL,
  `products_price` double NOT NULL,
  `products_weight` double NOT NULL,
  `products_status` enum('A','I') NOT NULL default 'A',
  PRIMARY KEY  (`products_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `tbl_products1` (`products_id`, `products_name`, `products_quantity`, `products_model`, `products_price`, `products_weight`, `products_status`) VALUES
(1, 'Peter England', 125, 'XP123', 400, 10, 'A'),
(2, 'Arrow', 360, 'PP123', 900, 12, 'A'),
(3, 'Allen Solly', 456, 'OP78456', 520, 3, 'A'),
(4, 'Raymond', 756, 'SS789465', 1022, 36, 'A'),
(5, 'Grasim', 899, 'GS132645', 640, 55, 'A'),
(6, 'Levis', 885, 'LL123465', 1500, 36, 'A'),
(7, 'Lee', 74, 'Lee4556', 960, 44, 'A');

Create a file configure.php that will have error reporting settings and database connection code, so you can include it in any file.
<?php
/*
 * @author Shahrukh Khan
 * @website https://aarafacademy.com
 * @facebbok https://www.facebook.com/Thesoftwareguy7
 * @twitter https://twitter.com/thesoftwareguy7
 * @googleplus https://plus.google.com/+thesoftwareguyIn
 */

// display all error except deprecated and notice  
error_reporting( E_ALL & ~E_DEPRECATED & ~E_NOTICE );
// turn on output buffering 
ob_start();

define('DB_DRIVER', 'mysql');
define("DB_HOST", "localhost");
define("DB_USER", "root");
define("DB_PASSWORD", "");
define("DB_DATABASE", "sampletest");

// basic options for PDO 
$dboptions = array(
    PDO::ATTR_PERSISTENT => FALSE,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);

//connect with the server
try {
    $DB = new PDO(DB_DRIVER . ':host=' . DB_HOST . ';dbname=' . DB_DATABASE, DB_USER, DB_PASSWORD, $dboptions);
} catch (Exception $ex) {
    echo($ex->getMessage());
    die;
}

?>

Create a file export-csv.php that will fetch data from the database, export to csv file and forces the file to download.
<?php
$filename = "testing-exports.csv";
        
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=$filename");
header("Pragma: no-cache");
header("Expires: 0");

$sql = "SELECT * FROM tbl_products1 WHERE 1";

try {
    $stmt = $DB->prepare($sql);
    $stmt->execute();
    $results = $stmt->fetchAll();
} catch (Exception $ex) {
    printErrorMessage($ex->getMessage());
}

$content = array();
$title = array("Name", "Quantity", "Model", "Price", "Weight", "Status");
foreach ($results as $rs) {
    $row = array();

    $row[] = stripslashes($rs["products_name"]);
    $row[] = stripslashes($rs["products_quantity"]);
    $row[] = stripslashes($rs["products_model"]);
    $row[] = stripslashes($rs["products_price"]);
    $row[] = stripslashes($rs["products_weight"]);
    $row[] = ($rs["products_status"] == "A") ? "Active" : "Inactive";
    
    $content[] = $row;
    
}

$output = fopen('php://output', 'w');
fputcsv($output, $title);
foreach ($content as $con) {
    fputcsv($output, $con);
}

?>

Import from local CSV file and insert to database.

Let’s create a table where you will insert the records. It’s schema is same as the previous table.

CREATE TABLE IF NOT EXISTS `tbl_products2` (
  `products_id` int(11) NOT NULL auto_increment,
  `products_name` varchar(255) NOT NULL,
  `products_quantity` int(11) NOT NULL,
  `products_model` varchar(255) NOT NULL,
  `products_price` double NOT NULL,
  `products_weight` double NOT NULL,
  `products_status` enum('A','I') NOT NULL default 'A',
  PRIMARY KEY  (`products_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

You must have the csv file present in the server to run the below code. ( Dont’t worry you can download the sample from download area below).
<?php
$row = 0;
if (($handle = fopen("testing-exports.csv", "r")) !== FALSE) {
	while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
		if ($row > 0) {
			try {
				$sql = "INSERT INTO tbl_products2 (products_name, products_quantity,products_model,products_price,products_weight,products_status) values ( :pname, :qty, :model, :price, :weight, :status  ) ";
				$stmt = $DB->prepare($sql);
				$stmt->bindValue(":pname",  $data[0]);
				$stmt->bindValue(":qty",  $data[1]);
				$stmt->bindValue(":model",  $data[2]);
				$stmt->bindValue(":price",  $data[3]);
				$stmt->bindValue(":weight",  $data[4]);
				$stmt->bindValue(":status",  $data[5]);
				
				$stmt->execute();
				
			} catch (Exception $ex) {
				echo($ex->getMessage());
			}
		}
		$row++;
	}
	
	fclose($handle);
}
?>

Import data by uploading  CSV file.

Create a form with file field and a submit button.

<form name="myform" method="post" enctype="multipart/form-data" action="">
<input type="hidden" name="mode" value="import" >
<input type="file" name="uploadFile"> &nbsp;<input type="submit" name="sub" value="Upload">
</form>

When you attach a csv file and submit the code, the server must process the csv file and parse it.  Check the code below.
<?php
$tempFileName = time() . ".csv";
if (is_uploaded_file($_FILES["uploadFile"]['tmp_name'])) {
	$fileUploaded = move_uploaded_file($_FILES["uploadFile"]['tmp_name'], $tempFileName);

	if ($fileUploaded) {

		if (($handle = fopen($tempFileName, "r")) !== FALSE) {
			while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
				if ($row > 0) {
					try {
						$sql = "INSERT INTO tbl_products2 (products_name, products_quantity,products_model,products_price,products_weight,products_status) values ( :pname, :qty, :model, :price, :weight, :status  ) ";
						$stmt = $DB->prepare($sql);
						$stmt->bindValue(":pname", $data[0]);
						$stmt->bindValue(":qty", $data[1]);
						$stmt->bindValue(":model", $data[2]);
						$stmt->bindValue(":price", $data[3]);
						$stmt->bindValue(":weight", $data[4]);
						$stmt->bindValue(":status", $data[5]);

						$stmt->execute();
					} catch (Exception $ex) {
						echo($ex->getMessage());
					}
				}
				$row++;
			}
		}
	}
}
?>

View Demo
Download
I hope these script will help you to get a better understanding the flow. So now you can import and export csv file easily. All your suggestion and comments are welcome.

Get the latest updates directly in your inbox for FREE: Subscribe here

Share your feedback / let me know your doubts regarding this tutorial in the comment box given below.

Share:
Hasan

Shahrukh Khan (Hasan)

A software engineer who's a die-hard coder, blogger, dreamer and mentor with years of expertise in web development. Know more...

Related Posts

Vikash Kumar on

useful script, uses most of times.

Reply
thesoftwareguy on

Thank You

Reply
saudahmed on

i want export selected record from a grid ……. can you help me ?

Reply
Shahrukh Khan on

Yes explain your issue briefly.. so I can understand..

Reply
saudahmed on

i want to show data from mysql to a php page like you did , what i wanted to do is i want to search particular data and then export it to cvs and pdf , can you do it ?

Shahrukh Khan on

Try the code that I have used to import data and then displaying on the page. Also you just have to send the search parameter and perform a wild card searching using LIKE %keyword% and then return the data.

saudahmed on

Thank you i’ll try it , Do you have any quiz managment in php ?

Shahrukh Khan on

If you have any more queries or need any other help, feel free to contact me through the contact us page.

saudahmed on

I’ve more than 200 rows in my database and i tried you code but it’s not showing any pagination …….. do you have any idea how to add pagination into it ?

Shahrukh Khan on

Please send me a mail from the contact form. I will email you the the pagination script

saudahmed on

Thank you so much shahrukh i’ve received your email , appreciate your effort it really works for me ….. thanks once again 🙂

sumiti on

hi your script is very good but i want to add comma in coloum for example have more than one values in one feild i want to seperate with comma in csv and i want to export particular data i can do this please help me

reply me asap

Thanks

Reply
Shahrukh Khan on

Hi. try this one it will help you
[code]
$contents = array(“data,121”, “data2”, “data3”);
$fp = fopen(“abc.csv”, ‘w’);
foreach ($contents as $con) {
fputcsv($fp, $con);
}
[/code]

Reply
sumiti on

my import is not working i didnot get values from csv please help i am using your code

Shahrukh Khan on

check the new fgetcsv and fputcsv functions.

pushp on

what are the solution in case of exporting image.
How can we display image in csv file.

Reply
Shahrukh Khan on

CSV is a text file, so you get the image path, you have to download the images folder via ftp or using the hosting cpanel ( if its online).

Reply
Kayyum on

bhai its very useful for my project. thanks

Reply
simbarashe zimunya on

thanks man.it rely helped on my project

Reply
Fred on

Hi Shahrukh Khan,
this is really a great script for import / export!
A question:
Is it possible to insert an error message?
If the import already has a record that has exactly the same content in a particular column.
Where and how would the message be inserted into your project?

greetings from Germany
Fred

Reply
Shahrukh Khan on

if the record is duplicate check it on server side and ignore it before inserting.

Reply
johnson edem on

please can you do a tutorial on how to validate the data from csv before uploading it to the server

Reply
Shahrukh Khan on

sure will try.

Reply
Parth on

csv image url show how to save folder in database image name insert plz help me

Reply
Shahrukh Khan on

csv only import/export text not file.
you need to write code that get file contents and write file as a whole.

Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.