Location: PHPKode > projects > Esp php p2p project > p2p/other/csv2sql_senthil.php
<?php 

$seperator = stripslashes($seperator); 
$seperator = stripcslashes($seperator); 
if (!$seperator) 
   $seperator = ","; 


if (!$send) { 
?> 
<body link="#003366" vlink="#003366"> 
<style type="text/css"> 
<!-- 
BODY 
  { 
  font-family:    Verdana, Arial, Helvetica, sans-serif; 
  font-size:      10pt; 
  color:          #000000; 
  margin-top:     0; 
  margin-left:    0; 
  margin-right:   0; 
  margin-bottom:  0; 
  padding-top:    20; 
  padding-left:   20; 
  padding-right:  0; 
  padding-bottom: 0; 
  border-top:     0; 
  border-left:    0; 
  border-bottom:  0; 
  border-right:   0; 
  width:  auto; 
  background-color: #ffffff; 
} 
a:link, a:visited { text-decoration: none } 
a:hover {color:#ff0000;} 
TD { 
   font-family:    Geneva, Arial, Helvetica; 
   font-size :     10pt; 
} 
.header { 
   font-family:    Geneva, Arial, Helvetica; 
   font-size :     14pt; 
   color :         #dddddd; 
} 
// --> 
</style><center> 
<?} 
$version = "1.4b"; 
$path_to_temp = "/temp/"; 

function char2array($string) { 
   $len = strlen($string); 
   for ($j=0;$j<$len;$j++){ 
       $char[$j] = substr($string, $j, 1); 
   } 
   return ($char); 
} 

function sqlify_line($line, $splitseperator,$seperator,$ncols) { 
   $line = chop($line); 
   $line_chunks = split ($splitseperator, $line); 
   if ($ncols != sizeof($line_chunks)) print "<br>#  number of fields do not match<br>"; 
   for($i=0;$i<count($line_chunks);$i++) { 
      $s = trim($line_chunks[$i]); 
      if ($s[0] == $s[strlen($s)-1] && ($s[0] == "'" || $s[0] == '"')) { 
        $s = substr($s,1,strlen($s)-2); 
     } 
      $line_chunks[$i] = '<font color="#000BA">'.addslashes($s).'</font>'; 
   } 
   for($i=0;$i<count($line_chunks);$i++) { 
      if ($i == (count($line_chunks)-1)) 
         $comma = ""; 
      else 
         $comma = $seperator." "; 
      $final_line  .= "'$line_chunks[$i]'$comma"; 
   } 
   return $final_line; 
} 

function field_fix($line) { 
   $line = eregi_replace("[[:space:]]+", "", $line); 
   $letterarray = char2array($line); 
   for ($i=0;$i<count($letterarray);$i++) { 
      if (eregi("^[_a-z0-9-]+", $letterarray[$i])) 
         $fieldname .= $letterarray[$i]; 
   } 
   return $fieldname; 
} 

if (!$go) { 
?> 
<title>Senthil's Modified Csv2SQL.php</title> 
<table border="0" cellpadding="0" cellspacing="0" align="center"><tr bgcolor="#003366"><td> 
<table width="*" border="0" cellpadding="4" cellspacing="1"> 
  <form enctype="multipart/form-data" method="post"> 
  <input type="hidden" name="csv2sql" value="1"> 
  <tr> 
   <td colspan="2"><span class="header"><b>&nbsp;CSV 2 SQL:</b></span><input type="hidden" name="max_file_size" value="100000"></td> 
  </tr> 
  <tr> 
   <td bgcolor="#ffffff">Add Unique ID Field: <input type="checkbox" name="unique"></td> 
   <td bgcolor="#ffffff">Save as File: <input type="checkbox" name="send"></td> 
  </tr> 
  <tr> 
    <td bgcolor="#ffffff">File to Convert:</td> 
    <td bgcolor="#ffffff"><input name="csv_file" type="file"></td> 
  </tr> 
    <tr> 
   <td bgcolor="#ffffff">Seperate by:</td> 
   <td bgcolor="#ffffff"><input type="text" name="seperator" value="," size="1"> (use \t for tab) </td> 
  </tr> 
  <tr> 
    <td colspan="2" align="center" bgcolor="#ffffff"><input type="submit" value="convert file" name="go"> <input type="reset" value="reset"></td> 
  </tr> 
  </form> 
  <form enctype="multipart/form-data" method="post"> 
  <input type="hidden" name="sql2csv" value="1"> 
  <tr> 
    <td colspan="2"><span class="header"><b>&nbsp;SQL 2 CSV:</b></span></td> 
  </tr> 
  <tr> 
   <td bgcolor="#ffffff">Seperate by:</td> 
   <td bgcolor="#ffffff"><input type="text" name="seperator" value="," size="1"> (use \t for tab) </td> 
  </tr> 
  <tr> 
    <td bgcolor="#ffffff" colspan="2">Save as File: <input type="checkbox" name="send"></td> 
  </tr> 
  <tr> 
    <td bgcolor="#ffffff">Database Host: </td> 
    <td bgcolor="#ffffff"><input type="text" name="db_host" value="localhost"></td> 
  </tr> 
  <tr> 
    <td bgcolor="#ffffff">Username: </td> 
    <td bgcolor="#ffffff"><input type="text" name="user_nm"></td> 
  </tr> 
  <tr> 
    <td bgcolor="#ffffff">Password:</td> 
    <td bgcolor="#ffffff"><input type="password" name="password"></td> 
  </tr> 
  <tr> 
    <td bgcolor="#ffffff">Database: </td> 
    <td bgcolor="#ffffff"><input type="text" name="database"></td> 
  </tr> 
  <tr> 
    <td bgcolor="#ffffff">Table:</td> 
    <td bgcolor="#ffffff"><input type="text" name="table"></td> 
  </tr> 
  <tr> 
    <td colspan="2" align="center" bgcolor="#ffffff"><input type="submit" value="dump csv" name="go"> <input type="reset" value="reset"></td> 
  </tr> 
  </form> 
</table> 
</td></tr></table> 
<center> 
<b>CSV 2 SQL</b> ver <? echo $version; ?> by <a href="mailto:hide@address.com">Senthil Nayagam</a>.<br><a href="http://www.nellainet.com/">http://www.nellainet.com </a> 
</center> 
<? 
   exit; 
} 

if (($go) && ($csv2sql)) { 

   $splitseperator = $seperator; 
   $seperator = ','; 

   $table_nm = split ("\.", $csv_file_name); 
   $table_name = strtolower($table_nm[0]); 
   $out_header = "# csv2sql Dump\n# http://www.".$HTTP_SERVER_VARS["HTTP_HOST"].$HTTP_SERVER_VARS["REQUEST_URI"]."\n#\n# Source File: $csv_file_name\n\n# --------------------------------------------------------\n#\n# Table structure for table '$table_name'\n#\n\n"; 


   if ($send) { 
      header("Content-disposition: filename=$table.csv"); 
      header("Content-type: application/octetstream"); 
      header("Pragma: no-cache"); 
      header("Expires: 0"); 
      $client=getenv("HTTP_USER_AGENT"); 
      if (ereg('[^(]*\((.*)\)[^)]*',$client,$regs)) { 
         $os = $regs[1]; 
         if (eregi("Win",$os)) $crlf="\r\n"; 
      } 
   } 


   if ($csv_file != "none") { 
      $location = $path_to_temp.mktime().".csv"; 
      copy($csv_file,$location); 
      unlink($csv_file); 
      $file_content = file($location); 

      $fields = explode ($splitseperator, $file_content[0]); 

      for($i=0;$i<count($fields);$i++) { 
         $fields[$i] = field_fix($fields[$i]); 
      } 
      $table_create = "CREATE TABLE $table_name (\n"; 
      if ($unique) $table_create .= "   id smallint(6) DEFAULT '0' not null auto_increment,\n"; 
      $numfields = count($fields); 
      for ($i=0;$i<$numfields;$i++) { 
         if ($i == ($numfields-1)) 
            $comma = ""; 
         else 
            $comma = $seperator; 
         $table_create .= "   $fields[$i] CHAR(225) not null$comma\n"; 
         $field_names  .= "$fields[$i]$comma "; 
      } 
      if ($unique) $table_create .= "   PRIMARY KEY (id),\n   UNIQUE ID (id)\n"; 
      $table_create .= ");\n\n"; 
      if (!$send) 
         echo "</center><p align=\"left\"><pre>"; 
      echo $out_header; 
      echo $table_create; 
      if ($unique) $unique_field = "id, "; 
      for($i=1;$i<count($file_content);$i++) { 
         echo "INSERT INTO $table_name ($unique_field".trim($field_names).")<br> &nbsp; VALUES ("; 
         if ($unique_field) $uniqq = "'$i', "; 
         echo $uniqq.sqlify_line($file_content[$i], $splitseperator,$seperator,$numfields).");\n"; 

      } 
      if (!$send) 
         echo "</pre><p>"; 
   } else { 
      echo "I need a CSV file to convert"; 
      exit; 
   } 
   unlink($location); 
} 
if (($go) && ($sql2csv)) { 
   mysql_pconnect($db_host, $user_nm, $password) or die( "Unable to connect to SQL server"); 
   @mysql_select_db($database) or die( "Unable to select database"); 
   $sqlcont = "select * from $table"; 
   $result = mysql_query($sqlcont); 
   echo mysql_error(); 

   function make_csv_happy($string,$seperator) { 
      $string = trim($string); 
      if (eregi("\$seperator",$string)) { 
         $string = ereg_replace("\"", "\"\"", $string); 
         $string = "\"".$string."\""; 
      } 
      $string = ereg_replace(10, "", $string); 
      $string = ereg_replace("\r", "", $string); 
      return $string; 
   } 

   if (mysql_fieldname($result, 0) == "id") $first_field = "iD"; 

   while($col < mysql_numfields($result)) { 
      $fname  = mysql_fieldname($result, $col); 
      if ($col < mysql_numfields($result)-1)$comma = $seperator; 
      else $comma = ""; 
      if (($col == 0) && ($first_field)) 
         $names .= $first_field.$comma; 
      else 
         $names .= make_csv_happy(strtoupper($fname),$seperator).$comma; 
      $col++; 
   } 
   $final = $names."\n"; 
   while($row < mysql_numrows($result)) { 
      $col=0; 
      $line = ""; 
      while($col < mysql_numfields($result)) { 
         $fname = mysql_fieldname($result, $col); 
         if ($col < mysql_numfields($result)-1)$comma = $seperator; 
         else $comma = ""; 
         $line .= make_csv_happy(mysql_result($result,$row,$fname),$seperator).$comma; 
         $col++; 
      } 
      $final .= $line."\n"; 
      $row++; 
   } 
   if ($send) { 
      header("Content-disposition: filename=$table.csv"); 
      header("Content-type: application/octetstream"); 
      header("Pragma: no-cache"); 
      header("Expires: 0"); 
      $client=getenv("HTTP_USER_AGENT"); 
      if (ereg('[^(]*\((.*)\)[^)]*',$client,$regs)) { 
         $os = $regs[1]; 
         if (eregi("Win",$os)) $crlf="\r\n"; 
      } 
   } else { 
      echo "</center><p align=\"left\"><pre>"; 
   } 

   echo $final; 
   if (!$send) echo "</pre></p>"; 
} 

?>
Return current item: Esp php p2p project