Code bellow will be helpfull for you for importing and exporting csv files.
Copy the code bellow and save it as "import_export_csv.php" then open it in your host e.g. 'localhost' and start importing, exporting csv files.
<?php
//don't forget to include your database connectivity.
include('db_connection.php');
if ($REQUEST_METHOD=="POST" && $_POST['btnSubmit']== 'Export') //this code is for exporting data when you click on "Export" this part of code will be executed.
{
// if you want to export data from a database table. You have to give your_table name in bellow query.
$select = "SELECT * FROM Your_table";
$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );
// code bellow will get names of the fields and set as header of the csv.
$fields = mysql_num_fields ( $export );
for ( $i = 0; $i < $fields; $i++ )
{
$header .= '"'.mysql_field_name( $export , $i ).'"';
if($i != $fields-1)
$header .= ",";
}
//if data found in database arrange it in csv format and pass to a variable $data.
while( $row = mysql_fetch_row( $export ) )
{
$line = '';
foreach( $row as $value )
{
if ( ( !isset( $value ) ) || ( $value == "" ) )
{
$value = '"'."\t".'",';
}
else
{
$value = str_replace( '"' , '""' , $value );
$value = '"' . $value . '"' . ",";
}
$line .= $value;
}
$line = substr($line, 0, -1);
$data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );
//if data=='' not found in database
if ( $data == "" )
{
$data = "\n(0) Records Found!\n";
}
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=products_csv.csv");
header("Pragma: no-cache");
header("Expires: 0");
// display fields name and data here
print "$header\n$data";
exit;
}
elseif ($REQUEST_METHOD=="POST" && $_POST['btnSubmit']== 'Import') //this code is for importing data when you click on "Import" this part of code will be executed.
{
if ($_FILES[csv][size] > 0)
{
//get the csv file
$file = $_FILES[csv][tmp_name];
$handle = fopen($file, "r");
$row=1;
//fgetcsv — Gets line from file pointer and parse for CSV fields
while (($line_of_data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
//If your first line from csv contains fields names ignore it.
if($row!=1)
{
// $line_of_data is numaric array has your impoted data $line_of_data[0] has your first record from csv which is fields name normaly.
$query = "insert into yourtable (username, email) values ('$line_of_data[0]', '$line_of_data[1]')";
mysql_query ($query) or die ( "Sql error : " . mysql_error( ) );
}
$row++;
}
echo "File imported successfuly.";
}
}
?>
<div style="margin:0 auto; width:100%">
<form method="post" name="form1" action="import_export_csv.php">
click here for export a csv file: <input type="submit" name="btnSubmit" value="Export" />
</form>
</div>
<div style="margin:10px auto; width:100%">
<form method="post" name="form2" enctype="multipart/form-data" action="import_export_csv.php">
select your .csv file: <input type="file" name="csv" />
<input type="submit" name="btnSubmit" value="Import" />
</form>
</div>
<?php
//don't forget to include your database connectivity.
include('db_connection.php');
if ($REQUEST_METHOD=="POST" && $_POST['btnSubmit']== 'Export') //this code is for exporting data when you click on "Export" this part of code will be executed.
{
// if you want to export data from a database table. You have to give your_table name in bellow query.
$select = "SELECT * FROM Your_table";
$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );
// code bellow will get names of the fields and set as header of the csv.
$fields = mysql_num_fields ( $export );
for ( $i = 0; $i < $fields; $i++ )
{
$header .= '"'.mysql_field_name( $export , $i ).'"';
if($i != $fields-1)
$header .= ",";
}
//if data found in database arrange it in csv format and pass to a variable $data.
while( $row = mysql_fetch_row( $export ) )
{
$line = '';
foreach( $row as $value )
{
if ( ( !isset( $value ) ) || ( $value == "" ) )
{
$value = '"'."\t".'",';
}
else
{
$value = str_replace( '"' , '""' , $value );
$value = '"' . $value . '"' . ",";
}
$line .= $value;
}
$line = substr($line, 0, -1);
$data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );
//if data=='' not found in database
if ( $data == "" )
{
$data = "\n(0) Records Found!\n";
}
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=products_csv.csv");
header("Pragma: no-cache");
header("Expires: 0");
// display fields name and data here
print "$header\n$data";
exit;
}
elseif ($REQUEST_METHOD=="POST" && $_POST['btnSubmit']== 'Import') //this code is for importing data when you click on "Import" this part of code will be executed.
{
if ($_FILES[csv][size] > 0)
{
//get the csv file
$file = $_FILES[csv][tmp_name];
$handle = fopen($file, "r");
$row=1;
//fgetcsv — Gets line from file pointer and parse for CSV fields
while (($line_of_data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
//If your first line from csv contains fields names ignore it.
if($row!=1)
{
// $line_of_data is numaric array has your impoted data $line_of_data[0] has your first record from csv which is fields name normaly.
$query = "insert into yourtable (username, email) values ('$line_of_data[0]', '$line_of_data[1]')";
mysql_query ($query) or die ( "Sql error : " . mysql_error( ) );
}
$row++;
}
echo "File imported successfuly.";
}
}
?>
<div style="margin:0 auto; width:100%">
<form method="post" name="form1" action="import_export_csv.php">
click here for export a csv file: <input type="submit" name="btnSubmit" value="Export" />
</form>
</div>
<div style="margin:10px auto; width:100%">
<form method="post" name="form2" enctype="multipart/form-data" action="import_export_csv.php">
select your .csv file: <input type="file" name="csv" />
<input type="submit" name="btnSubmit" value="Import" />
</form>
</div>
