WebsiteInteract

OK we did that and on my localhost machine it stopped at 900,000 records. Here is my code. If there is a way to parse or buffer this... I would appreciate any tips. Thanks <?php $con = mysql_connect("localhost", "root", ""); mysql_select_db("cars", $con); $csvfile = 'usedcars.txt'; $csv_upload_directory_name = 'upload'; $table = 'cars'; //------------------------------------------------------------------ // Full system path to the directory with the csv file //------------------------------------------------------------------ $realpath = realpath('.'); //------------------------------------------------------------------ // Make sure our upload directory exists //------------------------------------------------------------------ if (!file_exists("./$csv_upload_directory_name")) { echo "<div class=\"error\">The required directory $csv_upload_directory_name does not exist at $realpath</div>\n"; //include("page_footer.php"); exit; } //------------------------------------------------------------------ // Get our directory permissions //------------------------------------------------------------------ $directory_permissions = substr(sprintf('%o', fileperms("$csv_upload_directory_name")), -4); //------------------------------------------------------------------ // Directory needs enough permissions to delete our csv file after database update //------------------------------------------------------------------ //echo substr(sprintf('%o', fileperms('/tmp')), -4); //echo substr(sprintf('%o', fileperms('/etc/passwd')), -4); if ($directory_permissions != '0777') { echo "<div class=\"error\">Incorrect permissions on folder $realpath/$csv_upload_directory_name. Permissions are $directory_permissions. They must be 0777.</div>\n"; // include("page_footer.php"); exit; } //------------------------------------------------------------------ // Make sure our csv file exists //------------------------------------------------------------------ if (!file_exists("$realpath/$csv_upload_directory_name/$csvfile")) { echo "<div class=\"error\">The csv file $csvfile does not exist in the directory \"$csv_upload_directory_name\" at $realpath/$csv_upload_directory_name/</div>\n"; //include("page_footer.php"); // echo $realpath; exit; } //------------------------------------------------------------------ // Open csv file and make sure row header matches db columns //------------------------------------------------------------------ $fh = fopen("$realpath/$csv_upload_directory_name/$csvfile", "r"); $csv_header = fgets($fh); fclose($fh); $csv_header = rtrim($csv_header); //------------------------------------------------------------------ // Update DB //------------------------------------------------------------------ echo $realpath/$csv_upload_directory_name/$csvfile; //initialize the buffer $buffer = null; //set xmode to `read`, r = read, rb = read binary, w = write, wr = read write, a = append, x = create and write $handle = fopen('$realpath/$csv_upload_directory_name/$csvfile', 'r') or trigger_error('Fatal Error: Could not open file', E_USER_ERROR) && exit(); //we need to ensure validity to prevent an infinite loop with feof() //iterate through the file until EOF (end of file) is reached while(!feof($handle)) { //we allocate and read 4096 bytes (4 Megabytes) of the file into memory at a time until EOF $buffer .= fread($handle, 4096); } //we close the stream wrapper to free resources fclose($handle); //do something with the buffer print "File is " . strlen($buffer) . " bytes."; exit; $sql = "LOAD DATA CONCURRENT LOCAL INFILE '$csv_upload_directory_name/$csvfile'"; $sql .= " INTO TABLE ripples"; $sql .= " FIELDS TERMINATED BY '\t'"; $sql .= " LINES TERMINATED BY '\n'"; $sql .= " IGNORE 1 LINES"; $sql .= " (InventoryID, VehicleConditionDescription, VIN, StockNumber, Year, Make, Model, Trim, SellingPrice, Mileage, ExteriorColor, InteriorColor, NumberOfDoors, Engine, Features, Comments, DealerName, DealerPhone, DealerAddress, DealerCity, DealerState, DealerZipCode, DealerRadius, CertificationName, NumberOfImages, AllImages, PayoutPrice)"; mysql_query($sql) or die(mysql_error()); //mysqli_query($con,$sql); //echo $con."<br>"; //exit; //------------------------------------------------------------------ // Delete CSV file //------------------------------------------------------------------ //unlink("$realpath/$csv_upload_directory_name/$csvfile");// Uncomment this for final //------------------------------------------------------------------ // Show how many records added //------------------------------------------------------------------ //printf("Records updated: %d\n", mysql_affected_rows()); mysql_close($con); ?>

We already have a table set up, and we have a large txt file that we are FTPing to our server. The file has over one million rows of data with 27 fields all tab delimited. I have never processed such a large file and was wondering the best method to buffer it so that it does not bog down my server. Could someone point me to a good tutorial or provide information on how to best do this?