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. |
More File Manipulation Code Articles
More By Codewalkers
 developerWorks - FREE Tools!
|
You'll get answers to many questions and more from David Barnes, Lead Evangelist for IBM Emerging Internet Technologies. David will discuss aspects of Web 2.0 that bring value to corporations, academia, and government. He'll also discuss IBM's vision around Web 2.0, including the importance of remixability and consumability. The discussion will culminate with examples of various IBM Software Group solutions you can use to get ahead of the Web 2.0 adoption curve.
FREE! Go There Now!
|
|
|
|
Join this webcast, to learn how the Rational Process Library can help with compliance issues, drive process improvement, and assist in service-oriented architecture (SOA) or Agile development. We will take a peek into the Rational Process Library with content around software and systems engineering (including RUP), operations and systems management, program and portfolio management, and asset and SOA governance.
FREE! Go There Now!
|
|
|
|
David Barnes, Lead Evangelist for IBM Emerging Internet Technologies will discuss aspects of Web 2.0 that bring value to corporations, academia, and government. He'll also discuss IBM's vision around Web 2.0, including the importance of remixability and consumability. The discussion will culminate with examples of various IBM Software Group solutions you can use to get ahead of the Web 2.0 adoption curve.
FREE! Go There Now!
|
|
|
|
Join us for this on demand webcast to learn about developing complex systems more quickly and efficiently. We'll cover market drivers for developing, governing and reusing systems software assets and how you can develop system software assets with Rational Asset Manager.
FREE! Go There Now!
|
|
|
|
Download a free trial version of IBM Rational Developer for System i V7.1, which provides a complete development environment for traditional i5/OS application development. IBM Rational Developer for System i is a new eclipse-based workstation offering for i5/OS application development that provides a comprehensive Integrated Development Environment for edit/compile/debug of traditional RPG/COBOL/C/C++ i5/OS applications.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download a free trial of the Rational Host Access Transformation Services (HATS) Toolkit. The HATS toolkit provides a set of plug-ins for the IBM Rational Software Delivery Platform to help you easily extend your legacy applications. HATS makes your 3270 and 5250 applications available as HTML through the most popular Web browsers, while converting your host screens to a Web look and feel and it also enables you to develop new Web, portal, and rich-client applications.
FREE! Go There Now!
|
|
|
|
Join this Rational Talks to You teleconference on December 6 at 1:00 pm ET to participate in an agile application development discussion and get your questions answered on using IBM Rational Method Composer in a distributed environment.Get your questions answered!
FREE! Go There Now!
|
|
|
|
Regression testing -- in which code is thoroughly tested to ensure that changes have not produced unexpected results -- is an important part of any development process. But many testing environments neglect the terminal-based applications that still form the backbone of many industries. In this tutorial, you'll learn how the Rational Functional Tester Extension for Terminal-Based Applications works with other Rational Functional Tester to help test terminal-based applications quickly and easily.
FREE! Go There Now!
|
|
|
|
In this webcast, IBM Rational will discuss the importance of Web application security and will share techniques and best practices to introduce application security testing into current QA processes including: understanding common security vulnerabilities and techniques to integrate security testing with defect tracking and remediation systems in an effort to safeguard sensitive online information.
FREE! Go There Now!
|
|
|
|
Explore how Rational and WebSphere software enable enterprise documentation in SOA environments. Specifically, a new integration between IBM WebSphere® Business Modeler and IBM Rational® Method Composer software can help technical writers more easily keep enterprise operations manuals in sync with changes that are made to business processes, resulting in more accurate and timely documentation that benefits the entire enterprise.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
