Read large tab delimited text file & put in MYSQL database table

[WebsiteInteract]

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?  

 

[ignace]

fopen

fgetcsv

fclose

 

It will open a file handler to the file (without reading it's contents into memory) then using a while read a line and parse as csv and return it as an array for you to process. Very memory efficient.

Edited February 9, 2014 by ignace

[Barand]

Use LOAD DATA INFILE statement

 

http://dev.mysql.com/doc/refman/5.6/en/load-data.html

[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);
	?>

[Barand]

Could be the web server timing out.

 

Have you tried running the LOAD DATA statement from the MySQL CLI or using a db front end like Mysql Workbench?

 

Failing that, try splitting the csv file.