Php Programming Code Examples

Php > File Manipulation Code Examples

Comma parse to upload table

Comma parse to upload table

<?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>"); 
    } 
?>