Posted by jan on 01/27/06 13:01

I have an csv file which i want to put in a database.
I found therefore a script, but it doesn't work.
Athough its looks good.
From page: http://www.zend.com/codex.php?id=1228&single=1
and here it is too.

Can someone fix it?
That would be great.
jan

<?php
/*------------------------------------------------------------------------------------------*/
/* take a comma delimited file export from excel, and dynamically generate a
MySQL table */
/* assumptions made:
*/
/* We're going to create either 'TEXT' or 'VarChar(255)' fieldtypes
rather than */
/* look at all the data, since this is all we need to get started.
*/
/*
*/
/* The first line of the file is a coma delimited list of the field
names */
/*
*/
/*I'm sure this can be done quicker, easier, faster, and prettier.
*/
/*I just had to get it done for a project, and here's what I came up with.
*/
/*This is a modification of a user submitted script on php.net
*/
/*--Charles
*/
/*------------------------------------------------------------------------------------------



/*replace with your mysql hostname*/
$dbhost = "";
/*----------------------*/
/*replace with your mysql username*/
$dbuser = "";
/*----------------------*/
/*replace with your mysql password*/
$dbpass = "";
/*----------------------*/
/*replace with your mysql database name*/
$database = "";
/*----------------------*/
/*replace with your full path filename to the comma delimited file make sure
the file is readable*/
$openfile1 = "";
/*----------------------*/
/*replace with the name of the table you want created*/
$openfile = "";
/*----------------------*/
/*uncomment if you need to extend the runtime of the script for a large
file*/
//set_time_limit(0);
/*----------------------*/
/*define what field type we're going to create here
since we can't determine numeric on the fly, we must decide
which type of character field we're going to use*/
//text
//$fieldType = "TEXT";
//or VarChar
$fieldType = "VarChar(255)";
/*----------------------*/

$connection = @mysql_connect($dbhost,$dbuser,$dbpass) or die(mysql_error());
$db = @mysql_select_db($database,$connection) or die(mysql_error());
if($handle = fopen ($openfile1,"r"))
{
$sql = "CREATE TABLE $openfile (`ID` INT (11) UNSIGNED NOT NULL
AUTO_INCREMENT, PRIMARY KEY(`ID`), UNIQUE(`ID`), INDEX(`ID`))";
$result = @mysql_query($sql,$connection) or die(mysql_error());
$result_fields = @mysql_query("DESCRIBE $openfile", $connection) or
die(mysql_error());
$numFields = mysql_num_rows($result_fields);
$fields = mysql_list_fields("tax", "$openfile", $connection);
$columns = mysql_num_fields($fields);
$row = 0;
}
while ($data = fgetcsv ($handle, 1000, ","))
{
//row 0 holds the fieldnames
if($row == '0')
{
$num1 = count ($data);
if($num1 != $numFields)
{
for ($c=0; $c < $num1; $c++)
{
$data2 = str_replace(" ", "", $data[$c]);
$data2 = str_replace("#", "", $data2;


$sql = "ALTER TABLE $openfile ADD $data2 $fieldType";
$result = @mysql_query($sql,$connection) or
die(mysql_error());
}
}
//anything other than row 0 should be data
$row = '32';
} else
{
$row++;
$num = count ($data);
if($num == $num1)
{
$sql = "INSERT INTO $openfile VALUES(""";
for ($c=0; $c < $num; $c++)
{
$sql .= ",";
$sql .= ""$data[$c]"";
}
$sql .= ")";
$result = @mysql_query($sql,$connection) or die(mysql_error());
}
}
}
fclose ($handle);

?>

[Back to original message]