ILuvJohn

Most of the time people think that they need to just go out and buy a program, or download a program to get stuff done. This is not always the case. With just a simple understanding of some PHP code, you can convert any csv file to or from an sql file. It is not as hard as you think it might be.

For those that do not know, a CSV file is a Comma Separated Value file. Because of the way the file is put together so simply, it is already in a extremely easy format to manipulate.

Let’s start off with the script then just comment our way through it.

Let’s put all these files in a folder csv. CHMOD the folder to 777. If you don’t do this the script wont save our converted csv file, it will instead give an error.

config.php
<?php

$db_host = "localhost";
$db_user = "username";
$db_pass = "password";
$db_database = "database";

$connect_to_db = mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error());
mysql_select_db($db_database) or die(mysql_error());

?>

Let’s convert a csv file to sql first. Make sure you have your csv file in the same folder as index.php

csv.csv

name, email
John, john {at} iluvjohn {dot} com
Will, will {at} iluvjohn {dot} com

Now your main file, csv_to_sql.php

<?php

//include("config.php"); // connect to database, etc

$date = date("U");

$file = "csv.csv"; // the csv file we are going to open 

$table_name = "johns_table"; // the name of the table the csv will go into

$sql_file = $table_name."_".$date."_sql.sql";

// let's open the csv file, $content will hold all the contents of the csv file.
$fh = fopen($file, 'r');
$content = fread($fh, filesize($file));
fclose($fh);

// now break up the file at the end of every line, so every entry is seperated.
$line = explode("\n",$content);

// Now we will loop through every line
foreach ($line as $k => $v){

$linecount++; // we use this for primarily one thing,
//to keep track of the first time we go through
//everything...you will see why below near line 37 

// for every line, we need to seperate each entry
$vs = explode(",",$v); 

// now lets loop trough each of the entries
foreach ($vs as $kk => $vv){
$vv = addslashes($vv); // prepair te data before it goes into the database
$vv = trim($vv); // remove any extra white space before and after the entries.

if($linecount == "1"){

// the first line in a csv file, at least for our pourposes,
// should ALWAYS be the line that contains the names of the columns.
// We will need this to create the names of the column in sql
// no quotes, apostrophies, slashes, etc and only commas between the entries.

$vv = str_replace(" ","",$vv); // column names can;t have quotes, apostrophies, or spaces, etc
$vv = str_replace("'","",$vv);
$vv = str_replace("\"","",$vv);

$vv = strtolower($vv); // convert to lowercase, much easier to handle

$defining_line .= "`$vv` text NOT NULL,\n"; // compile the data into an sql entry, we will finish doing this next
$cols .= "$vv,"; // column names
}else{
$this_line .= "'$vv',"; // compile the data into an sql entry, we will finish doing this next
}
}

if($linecount != "1"){ // if the content is not the column names....

$this_line = substr($this_line,0,-1); // remove the last char, a comma

$entries .= "INSERT INTO $table_name (id,$cols) VALUES (id,$this_line);\n"; // Here are the queries to insert our information

}else{

$cols = substr($cols,0,-1); // take the comma off of the end of the list of columns.

}

unset($this_line); // unset $this_line so that it doesn't keep adding to what we have.

}

// here is where the sql to create the table is created, as well as the queries at the end.
$sql = "CREATE TABLE IF NOT EXISTS `$table_name` (
`id` int(255) NOT NULL auto_increment,
$defining_line"."PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Data for the $table_name
--

$entries

";

// you can change how you want things above, or down here, but this is basically it.

// lets just save the sql to a file for import.....
// $sql_file = table_name_date_sql.sql

// the file will be overwritten every time the script is accessed...
// that is why why put te unix time stamp in there, so the file
// doesn't get over written. You can also use this technique for
// backing up your database to a file without overwriting previous backups..

$fh_two = fopen($sql_file, 'w') or die("can't open file");
fwrite($fh_two, $sql);
fclose($fh_two);
echo "Your file CSV file <b>$file</b> was successfully converted to <a href=\"$sql_file\">$sql_file</a>.";
?>

And that’s about it. Clicking the link at the end of the script should let you view of download the file. If you wanted to make sure that you can click it and download AND you have an Apache Web server then you can add the following to a file called .htaccess. We are going to add the file download prompt for csv files and sql files in the same directory as the .htaccess file. If you don’t have the .htaccess file in the folder already, then just make it.

.htaccess

AddType application/octet-stream .sql
AddType application/octet-stream .csv

Now, if you want to convert MySQL to CSV, you have at least 2 ways of going about it. One way is if the sql is already in the database…then we just loop through all the entries and save them in a csv file. This is the easiest way to do it. The other way would be if the sql file is already in a file, like a backup. Even if there was a back up file of the sql, we could always import it into the database and then go with the first idea.

sql_to_csv.php

<?php
include("../config.php"); // connect to the database
$date = date("U");
$table = "users"; // What table we are gonna pull from...
$csv_file = $table."_".$date."_csv.csv";
$get_content = mysql_query("SELECT * FROM $table");
while($array_get_content = mysql_fetch_assoc($get_content)){
$count++;
foreach ($array_get_content as $k=>$v){
if($count == "1"){
if(empty(${$k})){
$keys .= "$k,";
${$k} = $k;
}
}
$values .= "$v,";
}
$values = substr($values,0,-1);
$values = $values."\n";
}
$keys = substr($keys,0,-1);
$keys = $keys."\n";
$csv = $keys.$values;
$csv = nl2br($csv);
// now lets save our results to a file...
$fh = fopen($csv_file, 'w') or die("can't open file");
fwrite($fh, $csv);
fclose($fh);
echo "The MySQL Table <b>$table</b> was successfully converted to the CSV File <a href=\"$csv_file\">$csv_file</a>.";
?>

From here you can run the script and download your table’s CSV file. If you create an array with all your table names in it, of find a php/mysql function that will give you all the tables names then you can put this entire script in a loop and save the entire database to a csv file.

I might write some more functions on this for you later.

Until then, questions and comments are welcome!

Thanks