hi friends.......
i hope you like this script...
<html>
<head>
<title>Transferring of Excel file to Database</title>
</head>
<body>
<?
/ Keep in mind i already gets the values of excel filename,
total number of columns,starting row and ending row values
on another form. remember i used a table named maindbbk
whereas you can get the table's value from the user.
/
$fs=new COM("scripting.filesystemobject");
if ($filename=="")
{
$error="The File name cannot be blank";
}
elseif (!$fs->fileexists($filename))
{
$error="the file not found on given path";
}
elseif ($numcolumns==0)
{
$error="The number of columns cannot be 0";
}
elseif ($startrow==0)
{
$error="The starting row cannot be 0";
}
elseif ($endrow==0)
{
$error="the ending row cannot be 0";
}
else
{
$error="";
}
if (!$error=="")
{
echo "<font face='courier' size=4 color='RED'><b>".$error."</b></font>";
}
else
{
$filename=stripslashes($filename);
$excelobj= new COM("excel.application");
$excelobj->workbooks->open($filename);
$counter = 0;
$qry[$counter] = "insert into maindbbk values(";
for ($i=$startrow;$i<=$endrow;$i++)
{
for ($j=1;$j<=$numcolumns;$j++)
{
if ($j == 1)
{
$cell=$excelobj->cells($i,$j);
$qry[$counter] = $qry[$counter]."'".$cell->value."'";
}
else
{
$cell=$excelobj->cells($i,$j);
$qry[$counter] = $qry[$counter].",'".$cell->value."'";
}
}
$qry[$counter] = $qry[$counter].")";
echo $qry[$counter]."<br>";
if ($counter <> ($endrow - $startrow))
{
$counter = $counter + 1;
$qry[$counter] = "insert into maindbbk values(";
}
}
if (!isset($Host))
{
$Host="localhost";
$connid=mssql_connect($Host,"userid","password") or die ("Could not connect to database");
}
$db=mssql_select_db("Accounts");
for ($counter=0;$counter<count($qry);$counter++)
{
$result=mssql_query($qry[$counter]) or die("cannot perform query");
}
echo "Successfully transferred all records";
$excelobj->quit;
}
?>
</body>
</html>
thanks...
qasim
