This script will take a comma-delimited file, parse it and upload it dynamically to the database...
By : BubbaBelly
<?php /**************************************************************************/ /* Author: Simeon P. Williams */ /* DATE: Spring 2003 */ /* This script will open a newly uploaded comma-delimited file and inserts*/ /* it into the database as a table. */ /* KNOWN PROBLEMS: Sometimes the last row of a table is inserted a couple */ /* of times. This may be due to trailing blank lines in the file. */ /**************************************************************************/ //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>"); } ?>
DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.
