Quick_CSV_import class

Client Joined: Oct 07, 2003 Posts: 735 Location: Ohio

I downloaded this from PHPClasses. I need a good CSV import feature to import testing scantrons we get from the university.

In the example page they have the following:

Code:




<?php


include "Quick_CSV_import.php";





//connect to database


mysql_connect("localhost", "root", "1");


mysql_select_db("test"); //your database





$csv = new Quick_CSV_import();

Which I need to change to use the $db connection variable in Nuke.

Could I not just add

require_once("db/db.php");

Then make $db global?

What is the proper way to "port" standalone code for use with Nuke?

The Quick_CSV_Import.php page has the following:

Code:

function import()


  {


    if($this->table_name=="")


      $this->table_name = "temp_".date("d_m_Y_H_i_s");


    


    $this->table_exists = false;


    $this->create_import_table();


    


    if(empty($this->arr_csv_columns))


      $this->get_csv_header_fields();


    


    /* change start. Added in 1.5 version */


    if("" != $this->encoding && "default" != $this->encoding)


      $this->set_encoding();


    /* change end */


    


    if($this->table_exists)


    {


      $sql = "LOAD DATA INFILE '".@mysql_escape_string($this->file_name).


             "' INTO TABLE `".$this->table_name.


             "` FIELDS TERMINATED BY '".@mysql_escape_string($this->field_separate_char).


             "' OPTIONALLY ENCLOSED BY '".@mysql_escape_string($this->field_enclose_char).


             "' ESCAPED BY '".@mysql_escape_string($this->field_escape_char).


             "' ".


             ($this->use_csv_header ? " IGNORE 1 LINES " : "")


             ."(`".implode("`,`", $this->arr_csv_columns)."`)";


      $res = @mysql_query($sql);


      $this->error = mysql_error();


    }


  }

$this I take it is a large array that is filled with all the variables needed throughout the code.

All I would need to do is change each instance of

Code:

@mysql_query

to

Code:

$db->sql_query

or

$db-> whatever the mysql equivalent is.

Correct?

Again is there any preferred way of "porting" standalone code to use with Nuke.

Why is the author using @mysql_query? Are they trying to suppress errors?

Posted: Tue Dec 11, 2007 1:22 pm

I've been playing with this. Trying to make it simpler.

I have a cvs file:

Quote:

1,000077193 ,"ABOUSOUD, OMAR ",9,90,35,9,90
1,000390918 ,"ABRAHAM, HEIDI ",9,90,35,9,90
1,000385334 ,"ADAMSHARRISON, ANGELA ",8,80,14,8,80
1,000545516 ,"AGIN, ANNIE ",8,80,14,8,80
1,000388419 ,"AMPONSAH, EUGEN ",10,100,75,10,100
1,000553939 ,"ANKROM, SEAN T",8,80,14,8,80
1,000034194 ,"BALCIK, BRENDE ",9,90,35,9,90
1,000065368 ,"BARNES, NATHAN ",10,100,75,10,100
1,000345478 ,"BARTON, LUCAS ",10,100,75,10,100

And trying to import it with the following:

Code:




if(!defined('TL_ADMIN')) { die("Illegal Access Detected!!!"); }


$modname = "Team_Learning";


include("header.php");


TLAdminMenu();


global $admin_file, $db, $prefix;


$table_name = "_tl_session_grade_import";





if(isset($_POST["Go"]) && ""!=$_POST["Go"]) //form was submitted


{


$file_name = $HTTP_POST_FILES['file_source']['tmp_name'];        





$emptytable = "TRUNCATE TABLE ".$prefix."$table_name";


$emptyresult = $db->sql_query($emptytable);





$sql = "LOAD DATA LOCAL INFILE '".mysql_real_escape_string($file_name)."'


    INTO TABLE ".$prefix."$table_name 


    FIELDS TERMINATED BY ','


    OPTIONALLY ENCLOSED BY '\"' 


    LINES TERMINATED BY '\r\n'"; 


if (!$sql) {echo("<p>Error performing query: " . mysql_error() . "</p>");}


   $result = $db->sql_query($sql);   


}

I'm only getting the first record imported to the table, and I don't know why.

Here is the structure of my table:

Code:

#


# Table structure for table `atlas_tl_session_grade_import`


#





  `ReportNumber` int(3) NOT NULL default '0',


  `StudentID` varchar(9) character set latin1 default '0',


  `StudentName` varchar(50) character set latin1 default '0',


  `Total_RS` tinyint(4) NOT NULL default '0',


  `Total_Percent` tinyint(3) NOT NULL default '0',


  `Percentile` tinyint(3) NOT NULL default '0',


  `1st_Subtest_RS` tinyint(3) NOT NULL default '0',


  `1st_Subtest_Percent` tinyint(3) NOT NULL default '0'

The CHARSET is = utf8

Any help is appreciated.

registered · Posted: Tue Dec 11, 2007 6:22 pm

BTW, take a look at your IF statement again... the "query" hasn't happened yet and $sql really is just a string, not a db connection handle.

But, the sql_query($sql) should still execute.

Wrap the if around the actual $result line so you can see the error that is being produced (at least I suspect there is an error, but you are not trapping for it).

Posted: Wed Dec 12, 2007 9:24 am

Well I kinda did this my own way, which of course is a way to learn. Smile

My issue now is in two parts.

I first create the import table, import the csv file, alter the table and add a field then try to update the table and add a Session_ID to the table.

I can't get the Session_ID to write to the table even through it is available thru a _GET as it is passed in the url.

The second part is a field called StudentID varchar(9) which is a University ID (UID) used on campus. The students enter this on the scantron when they take a test. It is highly likely they will enter the wrong number or maybe only the last 4 of their UID. The scantron is fed into a machine that records the test and spits out a grade.

The format of the csv looks like this.

Quote:

1,000077193 ,"ABOUSOUD, OMAR ",9,90,35,9,90
1,000390918 ,"ABRAHAM, HEIDI ",9,90,35,9,90
1,000385334 ,"ADAMSHARRISON, ANGELA ",8,80,14,8,80
1,000545516 ,"AGIN, ANNIE ",8,80,14,8,80
1,000388419 ,"AMPONSAH, EUGEN ",10,100,75,10,100

The 9 character number is the StudentID or UID. Sometimes I will see a StudentID in this file as

Quote:

xxxx75481

The UID stored in my students table looks like this

Quote:

U00025856
U00020784
U00518132
etc

In the imported table from the scantron I need to trim leading x's from the variable.

Code:

SELECT TRIM(LEADING 'x' FROM 'StudentID');

...and replace with zeros.

I need to update the UID in the imported table and replace the first character with a U

I think I can isolate the first character with a substring

Code:




SELECT SUBSTRING('StudentID',1);

Or something like that. Then somehow append a capital U to the beginning.

Then do a match for StudentID from the imported table to my student table UID and if not found set a flag in that record for a needed correction. Then try to match on `StudentName` varchar(50) to my student table Last_Name with a LIKE to see if I get a match.

Code:




<?php


/************************************************************************/


/* Academic Team Learning & Assessment System                           */


/* ==========================================                           


/************************************************************************/


global $admin_file, $db, $prefix;


if(!defined('TL_ADMIN')) { die("Illegal Access Detected!!!"); }


$modname = basename(str_replace("/admin", "", dirname(__FILE__)));


require_once("mainfile.php");


include("header.php");


TLAdminMenu();


$table_name = "_tl_session_grade_import";


$Session_ID = $_GET['Session_ID'];


if(isset($_POST["Go"]) && ""!=$_POST["Go"]) //form was submitted


{


$file_name = $HTTP_POST_FILES['file_source']['tmp_name'];


//Create the Import CSV table


$sql   = "CREATE TABLE IF NOT EXISTS ".$prefix."$table_name (


  `ReportNumber` int(3) NULL,


  `StudentID` varchar(9) NULL,


  `StudentName` varchar(50) NULL,


  `Total_RS` tinyint(4) NULL,


  `Total_Percent` tinyint(3) NULL,


  `Percentile` tinyint(3) NULL,


  `Subtest_RS` tinyint(3) NULL,


  `Subtest_Percent` tinyint(3) NULL


  )";    


$result = $db->sql_query($sql);   


if (!$result) {echo("<p>Error performing query: " . mysql_error() . "</p>");}


   if ( $result ) {


      echo "Table Created ".$prefix."$table_name Successful";


      echo "</p>\n";


   } else {


      echo "Table Created ".$prefix."$table_name was Unsuccessful";


      echo "</p>\n";


   }





echo "<p>\n";





//$emptytable = "TRUNCATE TABLE ".$prefix."$table_name";


//$emptyresult = $db->sql_query($emptytable);





$sql = "LOAD DATA LOCAL INFILE '".mysql_real_escape_string($file_name)."'


    INTO TABLE ".$prefix."$table_name 


    FIELDS TERMINATED BY ','


    OPTIONALLY ENCLOSED BY '\"' 


    LINES TERMINATED BY '\r'"; 


if (!$sql) {echo("<p>Error performing query: " . mysql_error() . "</p>");}


   $result = $db->sql_query($sql);   





$altertable = $db->sql_query("ALTER TABLE ".$prefix."$table_name ADD Session_ID INT(11) NOT NULL AFTER StudentID");


if (!$altertable) { echo "Alter ".$prefix."$table_name failed<br>\n"; } else { echo "Alter ".$prefix."$table_name succeeded<br>\n"; }





$updateSessionID = $db->sql_query("UPDATE ".$prefix."$table_name SET Session_ID = '$Session_ID'");


if (!$updateSessionID) {echo("<p>Error performing query: " . mysql_error() . "</p>");}


//if (!$updateSessionID) { echo "Updating Session_ID ".$prefix."$table_name failed<br>\n"; } else { echo "Updating Session_ID ".$prefix."$table_name succeeded<br>\n"; }





$import_total = $db->sql_numrows($db->sql_query("SELECT * FROM ".$prefix."$table_name"));


Opentable();


echo" $import_total records were imported \n";


echo "<p><a href=\"admin.php?op=TLImportIratView\">View Imported Irat Grades</a></p>";


Closetable();


}   


Opentable();


echo" <h2 align=\"center\">IRAT CSV import</h2>";


echo "<form method='post' action='".$admin_file.".php' enctype=\"multipart/form-data\">\n" 


  . "   <table border=\"1\" align=\"center\">"


  . "     <tr>"


  . "        <td>Source CSV file to import:</td>"


  . "        <td rowspan=\"30\" width=\"10px\">&nbsp;</td>"


  . "        <td><input type=\"file\" name=\"file_source\" id=\"file_source\" class=\"edt\" value=\"$file_source\"></td>"


  . "     </tr>      "


  . "     <tr>"


  . "        <td colspan=\"3\" align=\"center\"><input type=\"Submit\" name=\"Go\" value=\"Import it\" onClick=\" var s = document.getElementById('file_source'); if(null != s && '' == s.value) {alert('Define file name'); s.focus(); return false;}\"></td>"


  ."<input type='hidden' name='op' value='TL_CSV_Import'>\n"


  . "     </tr>"


  . "   </table>"


  . " </form>"


 ."";


 Closetable();


?>

I didn't think cleansing these scantron records would be so difficult. Mad