<?php
/*
##############################################################################
# PLEASE DO NOT REMOVE THIS HEADER!!!
#
# COPYRIGHT NOTICE
#
# Csv2SQL.php v1.4
# Copyright 2000,2001 Ai Graphics and Joe Lumbroso (c) All rights reserved.
# Created 13/5/00 Last Modified 10/6/00 6:45PM
# Joseph Lumbroso, http://www.aigraphics.com
# http://www.dtheatre.com/formmail.php
#
#
# updated by John Lim (hide@address.com) to fix importing from Access and
# other databases
#
##############################################################################
#
# This cannot and will not be inforced but I would appreciate a link back
# to any of these sites:
# http://www.dtheatre.com
# http://www.aigraphics.com
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
# THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR
# OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
# ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
# OTHER DEALINGS IN THE SOFTWARE.
#
##############################################################################
#
# I have this terrible habit of not putting comments in.. I will try to add
# comments before the next release.
#
*/
$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>Jack's 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> 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> 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">Joe Lumbroso</a>.<br><a href="http://www.lumbroso.com/scripts/">http://www.lumbroso.com/scripts/</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> 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>";
}
?>