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!
|
|
|
|
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!
|
|
|
|
Effective governance for lean development isn’t about command and control. Instead, the focus is on enabling the right behaviors and practices through collaborative and supportive techniques. Hear from Scott Ambler on how it is far more effective to motivate people to do the right thing than it is to force them to do so. Learn how to form a lightweight, collaboration-based framework that reflects the realities of modern IT organizations.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download a free trial version of WebSphere Business Modeler Advanced V6.1.1, IBM’s premier business process modeling and analysis tool for business users that offers process modeling, simulation, and analysis capabilities. IBM WebSphere Business Modeler helps you visualize, understand, and document business processes for continuous improvement.
FREE! Go There Now!
|
|
|
|
In this tutorial, you can learn how to install and configure the IBM Rational Asset Manager Eclipse client, explore the different views in the Asset Management perspective, learn various search techniques, work with existing assets, and submit a new asset.
FREE! Go There Now!
|
|
|
|
Learn how Rational Build Forge can extend a simple compile and package build process by adding customization and deployment capability. Go from a manual method to automating: checking for code changes; getting the latest source; compiling and packaging; customizing; copying to and restarting a deployment server; and sending e-mail notification that a new version is available.
FREE! Go There Now!
|
|
|
|
You can now evaluate IBM Rational Asset Manager V7.0 online without installing or configuring it on your own system! Rational Asset Manager helps create, modify, govern, find, and reuse any type of development assets, including SOA and systems development assets. Rational Asset Manager helps you reduce software development costs and improve quality by facilitating the reuse of all types of software development-related assets. Visit developerWorks to learn more about this product and register to explore its capabilities online.
FREE! Go There Now!
|
|
|
|
As businesses grow increasingly dependent upon Web applications, these complex entities grow more difficult to secure. Most companies equip their Web sites with firewalls, Secure Sockets Layer (SSL), and network and host security, but the majority of attacks are on applications themselves – and these technologies cannot prevent them. This paper explains what you can do to help protect your organization, and it discusses an approach for improving your organization’s Web application security.
FREE! Go There Now!
|
|
|
|
The discipline of assembling and delivering software is maturing beyond standard developer-centric compile/test software builds. The end-to-end software development lifecycle is emerging as the new focus moves “Beyond the Build.” Join this on demand webcast to learn about methods for streamlining software delivery and key capabilities of the IBM Rational Build Forge framework for automating build and release management in environments of any size.
FREE! Go There Now!
|
|
|
|
IBM Lotus Notes 8 provides a wide range of developers the ability to provide customized, integrated user interfaces via composite applications and via custom sidebar and toolbar plug-ins. This webcast provides you with tips and techniques to use with out-of-the-box capabilities of Lotus Notes 8, and survey how you can share useful components within your own company and within a larger community.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
