Aloha,
Problem:
The data does not go into the database correctly, it skips around, and makes up numbers that dont exist in the data file.
Some information:
I am writing a script that will take the information given it from a text file and insert it into a MySQL database.
this information is comma delimited.
SEX,Frequency,Percent,ValidPercent,CumulativePerce
nt,Valid,F,627,60.5,60.5,60.5,M,409,39.4,39.4,99.9,uk,
1,.1,.1,100.0,Total,1037,100.0,100.0,/ nRES,Frequency,Percent,ValidPercent,CumulativePercent,
Valid,INSTATE,416,40.1,40.2,40.2,OUTOFSTATE,155,14.9,
15.0,55.1,INDISTRICT,299,28.8,28.9,84.0,NONCREDIT,
166,16.0,16.0,100.0,Total,1036,99.9,100.0,
Missing,System,1,.1,Total,1037,100.0,
/nETH,Frequency,Percent,ValidPercent,CumulativePerc
ent,Valid,NONRESIDENTALIEN,2,.2,.2,.2,BLACK,4,.4,.4,
.6,NATIVEAMERICAN,3,.3,.3,.9,ASIAN,18,1.7,1.7,2.6,
HISPANIC,114,11.0,11.0,13.6,WHITE,772,74.4,74.4,
88.0,NORESPONSE,124,12.0,12.0,100.0,Total,1037,
100.0,100.0,/n
as you can see there are some sections of the data that are the same througout the file (this is only a Very small peice of the file).
such as:
SEX,Frequency,Percent,ValidPercent,CumulativePerce
nt,Valid
those sections of the file are first: Tablename, rest: table names, i also insert a field called dataname before Frequency.
then the rest of the data is the data that goes into the file, in this format,
F,627,60.5,60.5,60.5
where f is the data identifier then the rest is the data.
i have a loop that loops from F ot the end of the file and stores the data into arrays, then another loop that inserts the data into the database from the array.
I would put the code, but its rather long, and i didnt want to put a huge message on here if nobody thought they could help me solve my problem.
I will post the code to the board if someone wants to help me out, I would really appreciate it, because this is becoming a real pain.
Have a great day,
-=Levi=-
Very complicated problem here :) please help out
Moderator: General Moderators
-
levi_501_dehaan
- Forum Newbie
- Posts: 10
- Joined: Mon Oct 14, 2002 4:34 pm
- Location: Glenwood Springs
Code: Select all
<?php
$fd = fopen($iFile_name, 'r');
while($line = fgets($fd, 1024))
{
$elements = explode(',', $line);
array_pop($elements); // $line == "....,\n" --> last element == "\n" , get rid of it
//getting the fieldnames
// starting with next() will skip the tablename
$fields = array(); // will hold all fieldnames
while($cField = next($elements))
{
if(is_numeric($cField)) // seems to be the breaking-rule
break;
else
$fieldsї] = $cField;
}
$values = array(); // will hold all rows of a single line from the source-file
/* dirty hack: last loop advanced one too far
the first numerical value is in fact the second value of the first row
But that's a good starting point for the initial value-array,
making it easier to write the next loop
*/
$row = array( array_pop($fields) ); // will hold a database row
// $cField still holds the first numerical value of the source-line
do
{
if(is_numeric($cField))
$rowї] = $cField; // just another value of the same row
else
{ // store current row
// and begin a new one
$valuesї] = $row;
$row = array($cField);
}
} while($cField = next($elements));
echo '<pre>'; print_r($values); echo '</pre>'; // *
}
?>*) now you can decide what to do.
Insert the data into the database
or doing somethin like
Code: Select all
if (isset($allRecordsї$elementsї0]]))
$allRecordsї$elementsї0]]ї] = array($fields, $values);
else
$allRecordsї$elementsї0]] = array(array($fields, $values));-
levi_501_dehaan
- Forum Newbie
- Posts: 10
- Joined: Mon Oct 14, 2002 4:34 pm
- Location: Glenwood Springs
aloha
thanks for the help 
its much appreciated.
i hadnt thought about going at it in that way
, saves alot on the coding part
.
I still have to extract the first string out of the data before the field names to get the table name, but this will make extracting the data much easier.
thanks again,
-=Levi=-
its much appreciated.
i hadnt thought about going at it in that way
I still have to extract the first string out of the data before the field names to get the table name, but this will make extracting the data much easier.
thanks again,
-=Levi=-
-
levi_501_dehaan
- Forum Newbie
- Posts: 10
- Joined: Mon Oct 14, 2002 4:34 pm
- Location: Glenwood Springs
posting the code
Well I am still having a problem with this code,
I got it to work somewhat, it does its job, but some things are still messed up.
when i run it, it creates the first table successfully, then it inserts the data into the database correctly, there are only 4 sets of data to import, but it imports 4 more blank sets, it does this with all the tables, it will import the data, but it always counts to 8, and i cant figure out where that is happening.
the code also creates numbers, in the end field, its strange. it creates them out of the air, because i dont know where they are coming from.
here is the code im using, its a bit long, i would like to use some of volka's code, but i would like to see where i messed up first, so that i dont do it again. and maybe it will help me with my coding better if you guys/gals can point some of my errors to me.
thats the part that im having problems with, if you can help i would greatly appreciate it, or even if you could let me know how to improve my coding/style
thanks much
-=Levi=-
I got it to work somewhat, it does its job, but some things are still messed up.
when i run it, it creates the first table successfully, then it inserts the data into the database correctly, there are only 4 sets of data to import, but it imports 4 more blank sets, it does this with all the tables, it will import the data, but it always counts to 8, and i cant figure out where that is happening.
the code also creates numbers, in the end field, its strange. it creates them out of the air, because i dont know where they are coming from.
here is the code im using, its a bit long, i would like to use some of volka's code, but i would like to see where i messed up first, so that i dont do it again. and maybe it will help me with my coding better if you guys/gals can point some of my errors to me.
Code: Select all
if($database == "mysql" && $yes == "yes")
{
echo "please input your servers address.
<input type="text" name="serveraddy"><br>";
echo"please input your Database's name.
<input type="text" name="databasename"><br>";
echo"please input your username.
<input type="text" name="username"><br>";
echo"please input your password.
<input type="password" name="passwd"><br>";
echo"or Use default settings<br>"."<input name="default" type="checkbox" id="default" value="1"><br>";
for ($i = 0; $i < $manyfiles; $i++)
{
echo"<input type="file" name="filename".$i.""><br>";
}
$dir_name = "./data/";
$dir = opendir($dir_name);
echo "<font color="red"> here is a list of files in the directory <br></font>";
while ($file_name = readdir($dir))
{
if (($file_name != "." && $file_name != ".."))
{
print $file_name."<br>";
}
}
closedir($dir);
echo " <input type="submit" name="Submit" value="Submit">";
if($default == 1)
{
$serveraddy = "cs5";
$databasename = "college";
$username = "";
$passwd = "";
}
//stores the filenames into an array
$filearray1 = array("$filename0", "$filename1", "$filename2", "$filename3", "$filename4", "$filename5", "$filename6", "$filename7", "$filename8", "$filename9", "$filename10");//end of filenames into array
//
//Function to import the data into the database :) pain in my ass.
if(isset($serveraddy) && isset($databasename) && isset($username) && isset($passwd))
{
$h = 1;
while($h <= $manyfiles)
{
$i = 0; //Counter
for($p = 0; $p < $manyfiles; $p++)
{
if(isset($filearray1ї$p]))
{
echo "<br>is set ".$filearray1ї$p]."<br>";
}else{
echo "<br>is not set<br>";
exit;
}
}
for ($i = 0; $i < $manyfiles; $i++)
{
$fd = fopen($filearray1ї$i], "r+b");
$xfsize = filesize($filearray1ї$i]);
if(!isset($fd)){echo"no filename";}
while(!feof($fd))
{
$linez = fread($fd, $xfsize);
$lined = explode("/n", $linez);
$countt = count($lined);
for($t = 0; $t < $countt ; $t++)
{
$line = $linedї$t];
echo "<br>2:".$countt."<br>";
echo "<br>3:".$linedї$t]."<br>";
//if(isset($lined)) {echo $lined;} else {echo "no data from lined";)
$len_line = strlen($line); // Length of line
//get table name
$tablenamecomma = strpos($line, ",", 0); //Position first comma
$tablename = substr($line, 0, $tablenamecomma); //Cut string, name of table
//get field names
$fieldname1 = strpos($line, ",", $tablenamecomma + 1);
$fieldname2 = strpos($line, ",", $fieldname1 + 1);
$fieldname3 = strpos($line, ",", $fieldname2 + 1);
$fieldname4 = strpos($line, ",", $fieldname3 + 1);
$skip = strpos($line, ",", $fieldname4 + 1);
$datanameset =strpos($line, ",", $skip + 1);
$dataname1 = strpos($line, ",", $datanameset + 1);
$dataname2 = strpos($line, ",", $dataname1 + 1);
$dataname3 = strpos($line, ",", $dataname2 + 1);
$dataname4 = strpos($line, ",", $dataname3 + 1);
$field1 = substr($line, $tablenamecomma + 1, $fieldname1 - $tablenamecomma -1); //Cut the first fieldname
$field2 = substr($line, $fieldname1 + 1, $fieldname2 - $fieldname1 - 1); //Cut the second fieldname
$field3 = substr($line, $fieldname2 + 1, $fieldname3 - $fieldname2 - 1); //Cut the third fieldname
$field4 = substr($line, $fieldname3 + 1, $fieldname4 - $fieldname3 - 1); //Cut the fourth fieldname
$dataset = substr($line, $skip + 1, $datanameset - $skip -1);
$data1 = substr($line, $datanameset + 1, $dataname1 - $datanameset -2);
$data2 = substr($line, $dataname1 + 1, $dataname2 - $dataname1 -1);
$data3 = substr($line, $dataname2 + 1, $dataname3 - $dataname2 -1);
$data4 = substr($line, $dataname3 + 1, $dataname4 - $dataname3 -1);
$ctn = substr_count($line, ",");
$cont = ($ctn-10);
$cnt = floor($cont/5);
echo "instances of comma:".$ctn."<br>";
echo "instances of comma:".$cont."<br>";
echo "instances of comma:".$cnt."<br>";
$g = 0;
$star = $dataname4;
echo "location of star str:".$star."<br>";
while($g < $cnt)
{
$datanameset2ї$g] =strpos($line, ",", $star + 1);
$dataname12ї$g] = strpos($line, ",", $datanameset2ї$g] + 1);
$dataname22ї$g] = strpos($line, ",", $dataname12ї$g] + 1);
$dataname32ї$g] = strpos($line, ",", $dataname22ї$g] + 1);
$dataname42ї$g] = strpos($line, ",", $dataname32ї$g] + 1);
$dataset2ї$g] = substr($line, $star + 1, $datanameset2ї$g] - $star -1);
$data12ї$g] = substr($line, $datanameset2ї$g] + 1, $dataname12ї$g] - $datanameset2ї$g] -1);
$data22ї$g] = substr($line, $dataname12ї$g] + 1, $dataname22ї$g] - $dataname12ї$g] -1);
$data32ї$g] = substr($line, $dataname22ї$g] + 1, $dataname32ї$g] - $dataname22ї$g] -1);
$data42ї$g] = substr($line, $dataname32ї$g] + 1, $dataname42ї$g]- $dataname32ї$g] -1);
$star = $dataname42ї$g];
echo "stars new position:".$star;
++$g;
}
$star = 0;
// create Database, Tables, and fields if they dont exist. fun stuff eh?....no...its not.
$global_db = mysql_connect($serveraddy, $username, $passwd);
//Database function !database ? create one : fail
$db_list = mysql_list_dbs();
$cnt = mysql_num_rows($db_list);
if($t < $cnt)
{
if(mysql_db_name($db_list, $t) == $databasename)
{
$databasenametrue = 1;
mysql_select_db($databasename, $global_db);
}
if($databasenametrue !== 1)
{
mysql_create_db($databasename);
mysql_select_db($databasename, $global_db);
$databasenametrue = 1;
$createtable = 1;
}
}
//end of database stuff
// !table ? create one
$sql = "CREATE TABLE ".$tablename." (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, dataset TEXT, ".$field1." TEXT, ".$field2." TEXT, ".$field3." TEXT, ".$field4." TEXT)";
if(mysql_query($sql)) { echo("<P>".$tablename." table successfully created!</P>");} else { echo("<P>Error creating ".$tablename." table: ".mysql_error()."</P>");}
//end of table
echo "<br> INSERT INTO ".$tablename." <br> SET dataset =".$dataset."<br>".$field1."=".$data1."<br> ".$field2."=".$data2."<br> ".$field3."=".$data3."<br> ".$field4."=".$data4."<br>";
$query = "INSERT INTO $tablename SET dataset='$dataset',$field1='$data1', $field2='$data2', $field3='$data3',$field4='$data4'";
$result = mysql_query($query) or die(mysql_error());
$l = 0;
while($l < $cnt)
{
echo "<br> INSERT INTO ".$tablename." <br> SET dataset =".$dataset2ї$l]."<br>".$field1."=".$data12ї$l]."<br> ".$field2."=".$data22ї$l]."<br> ".$field3."=".$data32ї$l]."<br> ".$field4."=".$dataname42ї$l]."<br>";
$query = "INSERT INTO $tablename SET dataset='$dataset2ї$l]',$field1='$data12ї$l]', $field2='$data22ї$l]', $field3='$data32ї$l]',$field4='$dataname42ї$l]'";
$result = mysql_query($query) or die(mysql_error());
++$l;
}
$i = $i + 1;
}
}
}
fclose($fd); //Close the file
}
++$h;
print("".$i." records have been imported succesfully!");
}
}-=Levi=-