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

tellysk 0 1K Views

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

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;
	
	
 
}

Dani AI

Generated

This thread shows the right idea but the original snippets are dated and risky for modern PHP. The mysql_* extension was removed in PHP 7.0 (2015); use PDO or mysqli, and parse CSVs with fgetcsv (handles commas, quotes and embedded newlines). Use prepared statements for every insert, and avoid echoing raw CSV content into SQL. As warned, remove any automatic TRUNCATE unless you truly want to delete the table.

A minimal import pattern (conceptual) — open the uploaded file, skip an optional header row, then execute a prepared statement per line:

$db = new PDO(..., [PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION]);
// fopen + fgetcsv loop
// $stmt = $db->prepare('INSERT INTO tablename(col1,col2) VALUES (?,?)');
// $stmt->execute([$col1, $col2]);

For export prefer streaming to php://output with fputcsv() so you do not exhaust memory; or use SELECT ... INTO OUTFILE when the DB server has filesystem access. Always send proper headers (Content-Type: text/csv) and include a header row if consumers need it.

Practical checklist for using this thread’s examples (answers and ): (1) switch to PDO/mysqli, (2) validate and trim CSV fields, handle BOM/encoding (UTF-8), (3) use transactions and batch commits for large files, (4) consider LOAD DATA LOCAL INFILE for bulk imports (server permitting), (5) secure uploads (size limits, move_uploaded_file) and error handling. These changes make import/export reliable, fast, and safe.

Member Avatar for willempie
willempie 0 Newbie Poster

Good Job !!

Member Avatar for shar82
shar82 0 Newbie Poster

how to use this script in PHP code?

Member Avatar for Biiim
Biiim 215 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

Member Avatar for Anvesh_1
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 developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Edit Preview