Mega Code Archive
Comma parse to upload table
<![CDATA[
<?php
//open newly uploaded file
//$tempfile = "C:/DirectoryFileWasUploadedTo"."/".$uploadedfile_name;
$dbcnx = mssql_connect("localhost", "user", "password");
mssql_select_db("someDB");
//You can use this line to just test an individual file, rather than having to upload one first
$tempfile = "C:/SomeDirectory"."/somefile.ext";
$tempopen = fopen($tempfile, "r");
$servertable = "newTableName";//name of the new table to be created
//Note: to have a dynamic table creating script you can just pass
//the variable $servertable to this script from another page where you
//previously decided what the "newTableName" should be, same thing with
//the variable $tempfile
//Parse to find where double quotes are to eliminate commas
$i=0;
$holdi = 0;
//Open the file and begin reading
while(!feof($tempopen)){
//Read each line of the file one at a time
$templine = fgets($tempopen);
if(strlen($templine) == 1){
//skip blank lines
}else{
$numoflines++;//basically keeps track of the number of rows for the new table
//this is useful for giving each row a RowNumber, to maintain order.
//If this table is to be edited at a later time to, for instance,
//using an InsertRow function. This is more easily done, by adding the
//"inserted record" to the end of the table and changing the RowNumbers
//accordingly.
$linearray = explode(",", $templine);
$count = count($linearray);
$i=0;
$holdi=0;
if($count == 1){//check if line is blank
}else{
while($i < $count){
$newString = $linearray[$i];
//remove white space
$newString = trim($newString);
//Check if the current String begins with a " and does end with a "
if(eregi("^[\"]", $newString) && !eregi("[\"]$", $newString)){
$i++;
//If true, then concatenate a comma and the next item in the array until
//we find an item that ends with a " (this is because the line was split on a , )
while(!eregi("[\"]$", $linearray[$i]) && !eregi("\n", $linearray[$i])){
$newString = $newString . "," . $linearray[$i];
$i++;
}
//Do the last concatenation
$newString = $newString . "," . $linearray[$i];
}
$newlinearray[$holdi] = $newString;//add the newly constructed string to the new line array
//or $newString directly, if no "'s were part of this string
$i++;//increment $i to get the next item in $linearray
$holdi++;//increment $holdi so the $newlinearray is properly updated
}
}
if($numoflines==1){//when numoflines = 1, then the table is created and the first record is entered
//echo("There should be " . $holdi. " columns");
$j=0;
$column = "column";//generically title columns
//So columns will be title as follows "column0, column1,...,column($holdi-1)"
//Create query statement to create the table
$sql = "CREATE TABLE $servertable (ID INT NOT NULL PRIMARY KEY IDENTITY , RowNumber INT, ";
while($j != $holdi){
//Check if this is the last column
if($j == $holdi-1){
//if yes, do not put a comma at the end
$column = $column.$j . " TEXT NULL";
}else{
//if no, put a comma at the end
$column = $column.$j . " TEXT NULL, ";
}
$sql = $sql.$column;//concatenate the new Column declaration to the CREATE TABLE query
$j++;//go to next column
$column = "column";//reset $column
}
$sql = $sql.")";//concatenate the final closing parenthesis
$sqlGo = mssql_query($sql);//Run the query to create the table
if($sqlGo){
echo("<p><b>New Table " . $servertable . " created succesfully!!!</b></p>");
//*Note: As part of my content management system I found it necessary to store each
//table's name and the number of columns in a separate table. This became especially useful
//when using scripts to edit the rows, columns, or individual cells of a table.
//Just remove this part of the code if you find no use for it.
//INSERT into dataTables to hold the number of columns associated with this table.
//This value will be updated upon the insertion and deletion of columns from the editTables page.
$sql = "INSERT INTO dataTables (Title, NumColumns) values ('$servertable','$holdi')";
mssql_query($sql);
}else{
echo("<p><b>Error creating new Table!!!</b></p>");
exit();
}
//Insert the first record
$j = 0;
$recordnum = $numoflines;
$column = "column";
$column = $column."0";
$firstrecord = $newlinearray[0];
//Remove the unwanted double quotes
$firstrecord = eregi_replace("\"","",$firstrecord);
//Remove the unwanted single quotes (if you want single quotes, just add slashes before doing the insert)
$firstrecord = eregi_replace("'","",$firstrecord);
//Insert the first record
$sql = "INSERT INTO $servertable ($column, RowNumber) values ('$firstrecord','$recordnum')";
$sqlGo = @mssql_query($sql);
if(!$sqlGo){
echo("<p><b>Data could not be inserted from the file!!! - Try again!!!</b></p>");
exit();
}
$column = "column";//prepare to update each column of the newly inserted row
while($j != $holdi){
$column = $column.$j;
$rowdata = trim($newlinearray[$j]);
//$rowdata = rtrim($newlinearray[$j]);
//Same principle as above
$rowdata = eregi_replace("\"","",$rowdata);
$rowdata = eregi_replace("'","",$rowdata);
//Update each row, one at a time
//Notice that ID will always correspond to the correct $recordnum
$sql = "UPDATE $servertable SET $column='$rowdata' WHERE ID=$recordnum";
//Check to make sure the updates are successful
if(@mssql_query($sql)){
$ok = 1;
}else{
$ok = 0;
}
$column = "column";//reset $column
$j++;//go to the next column
}
}else{//numoflines != 1, thus all records after the first one can now be inserted
$column = "column";//set $column to prepare for inserts
$recordnum = $numoflines;//set $recordnum appropriately
$j = 0;
$column = $column."0";//set first column
$firstrecord = $newlinearray[0];//grab data for first column
//Remove the unwanted double quotes
$firstrecord = eregi_replace("\"","",$firstrecord);
//Remove the unwanted single quotes
$firstrecord = eregi_replace("'","",$firstrecord);
//INSERT the new record
$sql = "INSERT INTO $servertable ($column, RowNumber) values ('$firstrecord','$recordnum')";
$sqlGo = @mssql_query($sql);
if(!$sqlGo){
echo("<p><b>Data could not be inserted from the file!!! - Try again!!!</b></p>");
exit();
}
$column = "column";
while($j != $i){
$column = $column.$j;
$rowdata = rtrim($newlinearray[$j]);
//Remove unwanted double quotes
$rowdata = eregi_replace("\"","",$rowdata);
//Remove unwanted single quotes
$rowdata = eregi_replace("'","",$rowdata);
//UPDATE each column place of the new record
$sql = "UPDATE $servertable SET $column='$rowdata' WHERE ID=$recordnum";
//Check to make sure the updates are successful
if(@mssql_query($sql)){
$ok = 1;
}else{
$ok = 0;
}
$column = "column";//reset $column
$j++;//move to the next column
}
}
}
}
//Check if indeed everything was fine
if($ok == 1){
echo("<p><b>Data inserted correctly!!!</b></p>");
}else{
echo("<p><b>Data could not be inserted from the file!!! - Try again!!!</b></p>");
}
?>
]]>