Export CSV TO Mysql and Import to CSV from MYsql in PHP

tellysk

Export CSV TO Mysql and Import to CSV from MYsql in PHP

1,028 Views
About the Author
Import To mysql

$contents = file ('filename.csv');
for($i=0; $i<sizeof($contents); $i++)
{
$string = "remove value";
$no = str_replace($string, "/", $contents[$i]);
Print $no; 
$sql = mysql_query("insert into tablename (id) values ('$no')");
echo "<br>";
}


Export to CSV 

require 'exportcsv.inc.php';

$table="tablename"; // This is the tablename that you want to export to csv from mysql.

exportMysqlToCsv($table);




function exportMysqlToCsv($table,$filename = 'filename.CSV')
{
    $csv_terminated = "\n";
    $csv_separator = ",";
    $csv_enclosed = '"';
    $csv_escaped = "\\";
    $sql_query = "select * from $table";
 
    // Gets the data from the database
    $result = mysql_query($sql_query);
    $fields_cnt = mysql_num_fields($result);
 
 
    $schema_insert = '';
 
  /*  for ($i = 0; $i < $fields_cnt; $i++)
    {
        $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, stripslashes(mysql_field_name($result, $i)));
        $schema_insert .= $l;
       $schema_insert .= $csv_separator;
    } */// end for
 
   // $out = trim(substr($schema_insert, 0, -1));
   // $out .= $csv_terminated;
 
    // Format the data
    while ($row = mysql_fetch_array($result))
    {
        $schema_insert = '';
        for ($j = 0; $j < $fields_cnt; $j++)
        {
            if ($row[$j] == '0' || $row[$j] != '')
            {
 
                if ($csv_enclosed == '')
                {
                    $schema_insert .= $row[$j];
                } else
                {
                    $schema_insert .= $csv_enclosed . 
					str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;
                }
            } else
            {
                $schema_insert .= '';
            }
 
            if ($j < $fields_cnt - 1)
            {
                $schema_insert .= $csv_separator;
            }
        } // end for
 
        $out .= $schema_insert;
        $out .= $csv_terminated;
    } // end while
 
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Length: " . strlen($out));
    // Output to browser with appropriate mime type, you choose ;)
    header("Content-type: text/x-csv");
    //header("Content-type: text/csv");
    //header("Content-type: application/csv");
    header("Content-Disposition: attachment; filename=$filename");
	
		if($out)
		{
			echo $out;
			$table="table_csv";
			mysql_query("TRUNCATE $table");
		}
   
	
	
    exit;
	
	
 
}
willempie 0 Newbie Poster

Good Job !!

shar82 0 Newbie Poster

how to use this script in PHP code?

Biiim 133 Junior Poster

For the mysql import copy paste line 3-11 into a php file, update the file/table names

I dont quite get how the mysql import works.

For the csv export copy paste lines 16-103 into a php file, and update the file & table name at the top

I should also warn about this on line 94, mysql_query("TRUNCATE $table"); that will delete all data from the table - i don't know why that is there. i would comment it out unless thats what you want

the csv one looks like it should work though

Anvesh_1 0 Newbie Poster

Nice Article !

Really this will help to people of PHP & MySQL Community.
I have also prepared small demonstration on, how to import and export csv data with headers using MySQL.
You can visit my article using below link.

http://www.dbrnd.com/2015/09/mysql-import-and-export-csv-data-with-headers/

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.