Importing csv

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

I have a new issue as the server configuration has changed. I now need to import a csv to a database that is not on the host. The MySQL database resides on a different server.

This was working before because the db could see the tmp file created and read it into the db, but now it can't.

I only have around 100 records per import so it is not very intensive.

The "uploaded" file is not local to the db so now I need to rethink this.

I was thinking sending all my csv data in a post array using a large textarea.

Column headers are included.

Quote:

ReportNumber,StudentID,StudentName,Total_RS,Total_Percent,Percentile,1st_Subtest_RS,1st_Subtest_Percent
1,000077193 ,"ABOUSOUD, OMA R ",10,100,89,10,100
1,000390918 ,"ABRAHAM, HEIDI ",6,60,7,6,60
1,000385334 ,"ADAMSHARRISON, ANGELA ",6,60,7,6,60
1,000545516 ,"AGIN, ANNIE ",4,40,1,4,40
1,000388419 ,"AMPONSAH, EUGENI ",10,100,89,10,100
1,000553939 ,"ANKROM, SEAN T",9,90,65,9,90
1,000034194 ,"BALCIK, BRENDE ",10,100,89,10,100

I took this from a CSV to SQL convertor script I found on CodeWalkers.

Code:

OpenTable();


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


echo " <table width=\"100%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\">";


echo "<tr><b>Steps to import</b></tr>"


  . "<tr><ul>"


  . "<tr><li>Open the Excel File</tr>"    


  . "<tr><li>Save file as a CSV file. File/Save As/Choose csv format (Comma Delimited) in dropdown box.</tr>"


  . "<tr><li>Copy/Paste all CSV data into text area dispayed.</tr>"


  . "</ul>"; 


echo "<form action='".$_server['php_self']."' method='post'"


   ."<INPUT TYPE=\"HIDDEN\" NAME=\"ref\" VALUE=\"csv2mysql\">"


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


   ."<tr><td>Source Data to import:</td></tr>"


   ."<tr><td><TEXTAREA CLASS=\"DEFAULT\" NAME=\"csv_data\" ROWS=\"120\" COLS=\"40\"></TEXTAREA></td></tr>"


   ."<tr><td align=\"center\"><INPUT CLASS=\"DEFAULT\" TYPE=\"SUBMIT\" VALUE=\"Import Data\"></td></tr>"


   ."<input type='hidden' name='Session_ID' value='$Session_ID'>\n"


     ."</tr>"


     ."</table>"


     ."</form>"


    ."";


 Closetable();   


//-- Input form end -->





// Parse incoming information if above form was posted


if($_POST[ref] == "csv2mysql") {





//Create the Import CSV table


$sql   = "CREATE TABLE IF NOT EXISTS atlas_tl_session_grade_import (


  `ReportNumber` int(3) NULL,


  `Student_ID` varchar(9) NULL,


  `Name_Last` varchar(50) NULL, 


  `Name_First` varchar(30) 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>");}   








   echo "<h2>SQL Query Output:</h2>";





   // Get information from form & prepare it for parsing


   $csv_data   = $_POST[csv_data];


   $csv_array    = explode("\n",$csv_data);


   $column_names = explode(",",$csv_array[0]);





   // Generate base query


   $base_query = "INSERT INTO atlas_tl_session_grade_import (";


   $first      = true;


   foreach($column_names as $column_name)   


   {


      if(!$first)


         $base_query .= ", ";   


      $column_name = trim($column_name);


      $base_query .= "`$column_name`";


      $first = false;


   }


   $base_query .= ") ";





   // Loop through all CSV data rows and generate separate


   // INSERT queries based on base_query + the row information


   $last_data_row = count($csv_array) - 1;


   for($counter = 1; $counter < $last_data_row; $counter++)


   {


      $value_query = "VALUES (";


      $first = true;


      $data_row = explode(";",$csv_array[$counter]);


      $value_counter = 0;


      foreach($data_row as $data_value)   


      {


         if(!$first)


            $value_query .= ", ";   


         $data_value = trim($data_value);


         $value_query .= "'$data_value'";


         $first = false;


      }


      $value_query .= ")";





      // Combine generated queries to generate final query


      //$query = $base_query .$value_query .";";


      $query = $db->sql_query($base_query .$value_query);         


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


   }


}

I am getting the following error:

Quote:

Error performing query: Column count doesn't match value count at row 1

The StudentName is wraped in "ABOUSOUD, OMA R " with commas before and after, except there is also a comma in between.

What is the best way to fix this?

Could I just delete the column headers and import the data?

If importing just data what would I change in the code?

registered · Site Admin Joined: Jun 04, 2004 Posts: 6437

You might have to escape the student name. Try it manually with "ABOUSOUD\, OMAR"

If that works, you may need to find a function that can escape or replace the comma with \,.

Posted: Wed Feb 27, 2008 9:06 am

Well I'm back to trying to upload a file to the server and I have that working.

I'm then trying to make sure I can define the correct path to this csv.

Code:

$uploadServer = $_SERVER["HTTP_HOST"];


$uploadFolder = $_SERVER['DOCUMENT_ROOT'] . "/atlas/modules/$modname/uploadcache/";   


$fileName = basename($_FILES['file_source']['name']);

Code:

//Move the file      


move_uploaded_file($_FILES['file_source']['tmp_name'], $uploadFolder.$fileName);

Code:




//Import the file


$sql = "LOAD DATA INFILE '".$uploadServer."".$uploadFolder."".$fileName."'


    INTO TABLE ".$prefix."$table_name 


    FIELDS TERMINATED BY ','         


    LINES TERMINATED BY '\r'";

I don't think I have the concatenation correct.

The db user has FILE privileges.

This is all because now the db is on a different server than the website. It is located across a network and I can't get the import function working.

registered · Posted: Thu Feb 28, 2008 6:13 am

I don't know one way or the other if it is possible to use the loader over a remote connection. sorry, have never had to use it to learn it.

What you might have to do, unless you find out its possible with LOAD, is to do this in PHP, make your connection, read your file, and start populating the db. This does, by the way, also give you complete control over the processing of your input file which can have its advantages, but the disadvantages are you have to write the code and its probably a much slower way to go (I am just guessing on that one but its an educated guess at least).

Posted: Mon Mar 03, 2008 9:05 am

My latest version of this script with help from a guy at PHPBuilder.

Code:




<?php


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


/* Academic Team Learning & Assessment System                           */


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


/* Author: Steven Donovan                                               */


/* Web Developer                                                        */


/* Boonshoft School of Medicine                                         */


/* Project Start Date 10/25/2007                                        */


/*                                                                      */


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


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();


Opentable();


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


echo " <table width=\"100%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\">";


echo "<tr><b>Steps to import</b></tr>"


  . "<tr><ul>"


  . "<tr><li>Open the Excel File</tr>" 


  . "<tr><li>Remove the first row of header information containing ReportNumber, UID, StudentName, etc.</tr>" 


  . "<tr><li>Save file as a csv file. File/Save As/Choose csv format (Comma Delimited) in dropdown box.</tr>"


  . "</ul>"; 


echo "<form action='".$_server['php_self']."' method='post' enctype=\"multipart/form-data\">"


  . "   <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=\"Next Step\" 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"


  . "<input type='hidden' name='Session_ID' value='$Session_ID'>\n"


  . "     </tr>"


  . "   </table>"


  . " </form>"


 ."";


 Closetable();


if(isset($_POST["Go"]) && ""!=$_POST["Go"]) {





$insert_array=array();


$uploadFolder = $_SERVER['DOCUMENT_ROOT'] . "/atlas/modules/$modname/uploadcache/";


$fileName = basename($_FILES['file_source']['name']);


move_uploaded_file($_FILES['file_source']['tmp_name'], $uploadFolder.$fileName);


$handle = fopen("$uploadFolder\$fileName", "r");


if (!$handle) {echo("<p>Error opening file:</p>");


die();


}





$table_name = "_tl_session_grade_import";





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


  `ReportNumber` int(3) NULL,


  `Student_ID` varchar(9) NULL,


  `Name_Last` varchar(50) NULL, 


  `Name_First` varchar(30) 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>");}   





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


$table_total = $db->sql_numrows($checktable);


//We already have records in table


if ($table_total > 0) {


echo "<form action='".$_server['php_self']."' method='post'";


OpenTable();


echo"<tr><td>There are records already loaded from a previous file (TL Session).</td></tr>";


echo"<tr><td>You can view these IRAT grades <a href='".$admin_file.".php?op=TLImportIratView&Session_ID=$Session_ID'>Here!</a></td></tr>";


echo"<tr><td>Or click Continue to empty this table and start over.</td></tr>";


echo"<tr>"


  . "<td align=\"center\"><input type=\"Submit\" name=\"TruncateTable\" value=\"Continue\"></td>"  


  . "</tr>";


Closetable();


echo"</form>";


die();


}





if ($handle) {


  while (!feof($handle))  {


    $buffer = fgets($handle, 4096);


    $insert_explode_array=explode(",",$buffer);


    $insert_item="(\"".$insert_explode_array[0]."\",


      \"".$insert_explode_array[1]."\",


      \"".$insert_explode_array[2]."\")";


    array_push($insert_array,$insert_item);


    }


  fclose($handle);  





  $query="INSERT INTO ".$prefix."$table_name ('ReportNumber', 'Student_ID', 'Name_Last', 'Name_First', 'Total_RS', 'Total_Percent', 'Percentile', 'Subtest_RS', 'Subtest_Percent') VALUES";


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


  $item_count=count($insert_array);


  for($i=1;$i<=$item_count-1;$i++)


    {


    $query .= array_pop($item_array) .","; // adds items to the insert statement


    }


  $query .= array_pop($item_array); // finishes the insert statement


  }


  


  if(mysql_affected_rows() != count($item_count)) {


  die('MySQL Insert failed to insert all entries.');


}else{


  header("Location: ".$admin_file.".php?op=TLScantronIratInsert&Session_ID=$Session_ID");


  }


  }








?>

this does not give me an error:

Code:

if (!$handle) {echo("<p>Error opening file:</p>");


die();

The table gets created.

If I insert a record manually for testing and run the script again I get the proper response:

Code:




echo"<tr><td>There are records already loaded from a previous file (TL Session).</td></tr>";


echo"<tr><td>You can view these IRAT grades <a href='".$admin_file.".php?op=TLImportIratView&Session_ID=$Session_ID'>Here!</a></td></tr>";


echo"<tr><td>Or click Continue to empty this table and start over.</td></tr>";

I still do not get any records inserted.

If I wanted to place this:

die('<br />$query = '.$query.'<br />');

somewhere here to check the value of the $query where would I place it?

Code:




$query="INSERT INTO ".$prefix."$table_name ('ReportNumber', 'Student_ID', 'Name_Last', 'Name_First', 'Total_RS', 'Total_Percent', 'Percentile', 'Subtest_RS', 'Subtest_Percent') VALUES"; 


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


  $item_count=count($insert_array);


  for($i=1;$i<=$item_count-1;$i++)


    {


    $query .= array_pop($item_array) .","; // adds items to the insert statement


    }


  $query .= array_pop($item_array); // finishes the insert statement

registered · Posted: Mon Mar 03, 2008 9:51 am

You are executing the query before you have your VALUES clause built up.

Posted: Mon Mar 03, 2008 11:05 am

I don't know how to fix it.

I still don't know why LOAD DATA INFILE would not work. It seems much simpler.

I created a path:

Code:

$uploadFolder = $_SERVER['DOCUMENT_ROOT'] . "/atlas/modules/$modname/uploadcache/";

then

Code:

$fileName = basename($_FILES['file_source']['name']);      


move_uploaded_file($_FILES['file_source']['tmp_name'], $uploadFolder.$fileName);

then I checked it with this.

Code:




if (file_exists($uploadFolder.$fileName)) {


   echo "The file $fileName exists";


   die();


} else {


   echo "The file $fileName does not exist";


   die();


}

It came back "The file $fileName exists"

So I ran this

Code:




$sql = "LOAD DATA INFILE '".$uploadFolder."".$fileName."'


    INTO TABLE ".$prefix."$table_name 


    FIELDS TERMINATED BY ','         


    LINES TERMINATED BY '\r'"; 


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


 //die('<br />$sql = '.$sql.'<br />'); 


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


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

but all I get are

MySQL returned an empty result set (i.e. zero rows).

I am going with the assumption that this is true:

Quote:

Specifying the Datafile Location without using LOCAL

1. Default location assumed to be local to the server as MySQL reads the file directly.
* Absolute path on server: LOAD DATA INFILE '/path/to/file.txt' INTO TABLE table_name;
* If using a default database then relative path is relative to the default database: LOAD DATA INFILE 'file.txt' INTO TABLE table_name;
* If not using a default database then the relative path is relative the the specific database data directory: LOAD DATA INFILE './data_directory/file.txt' INTO TABLE table_name;

So this should have worked:

Code:

LOAD DATA INFILE '".$uploadFolder."".$fileName."'