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") {
// 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]);
//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.
Joined: Aug 29, 2004 Posts: 7330 Location: Arizona
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).
$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
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;
View next topic View previous topic
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
![[Valid RSS]](http://ravenphpscripts.com/images/valid-rss.png "Validate my RSS feed"){kind=small}
:: 
::
