Inserting from CSV to MySQL timesout

[budimir]

Guys,

 

I'm using the code below, but it timesout or crashes my browser. I guess the query that is being built gets to big and spends all memory. I'm inserting about 160.000 lines. How could I insert the by blocks of 2000 lines at a time? I guess that wouldn't crash the browser.

 

	//Import uploaded file to Database
	$handle = fopen($_FILES['filename']['tmp_name'], "r");
	
	$values = array();	
	while (($data = fgetcsv($handle, 300000, ";")) !== FALSE) {
	
			//Create record inserts as array elements
			$values[] = sprintf("('%s','%s','%s','%s','%s','%s','%s','%s','%s')",
								mysql_real_escape_string($id_cjenika),
								mysql_real_escape_string($vrijeme),
								mysql_real_escape_string($godina),
								mysql_real_escape_string($data[0]),
								mysql_real_escape_string($data[1]),
								mysql_real_escape_string($data[2]),
								mysql_real_escape_string($data[3]),
								mysql_real_escape_string($data[4]),
								mysql_real_escape_string($data[5])
							  );
	
	}
	
		$query="INSERT INTO kalkulacija_import_cjenik_stavke (id_cjenika, vrijeme, godina, kataloski_broj, naziv, cijena_EUR, valuta, cijena_DD, valuta_dd) 
		VALUES " . implode(",\n", $values);
		//echo "<pre>{$query}</pre><br>";
		mysql_query($query) or die (mysql_error());

	fclose($handle);

 

Any help would be appreciated...

[Barand]

Fastest way is use LOAD DATA INFILE query

 

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

[budimir]

Barand,

 

That is very helpful, but I have one more question regardingyou're advice. I can't seem to find a proper answer so I wonder is it possible. In my code below, I'm able to insert all the lines from CSV to the database, but I would like to join some variables to that insert like date, time, id of linked table... Is it possible to do that?

 

	$q_load =   'LOAD DATA LOCAL INFILE "upload/'.$_FILES['filename']['name'].'"
				INTO TABLE kalkulacija_import_cjenik_stavke
				FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY ""
				LINES TERMINATED BY "\r"
				IGNORE 1 LINES
				(kataloski_broj, naziv, cijena_EUR, valuta, cijena_DD, valuta_DD)';
				
	mysql_query($q_load) or die (mysql_error());

 

Can I insert variables that don't belong to CSV file?

[budimir]

Barand,

 

I managed to get it. but it seems I have a problem with inserting datetime. Is there any problems with inserting datetime?

 

This is my code, now...

 

$vrijeme = date("Y.m.d H:s");

$q_load =   'LOAD DATA LOCAL INFILE "upload/'.$_FILES['filename']['name'].'"
				INTO TABLE kalkulacija_import_cjenik_stavke
				FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY ""
				LINES TERMINATED BY "\r"
				IGNORE 1 LINES
				(kataloski_broj, naziv, cijena_EUR, valuta, cijena_DD, valuta_DD)
				SET id_cjenika = '.$id_cjenika.' , vrijeme = '.$vrijeme.' , godina = '.$godina.'';
				
	mysql_query($q_load) or die (mysql_error());

[Barand]

The example in the manual uses CURRENT_TIMESTAMP

 

 

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

You must also specify a column list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match input fields with table columns.

The column list can contain either column names or user variables. With user variables, the SET clause enables you to perform transformations on their values before assigning the result to columns.

User variables in the SET clause can be used in several ways. The following example uses the first input column directly for the value of t1.column1, and assigns the second input column to a user variable that is subjected to a division operation before being used for the value of t1.column2:

LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, @var1)
SET column2 = @var1/100;

The SET clause can be used to supply values not derived from the input file. The following statement sets column3 to the current date and time:

LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, column2)
SET column3 = CURRENT_TIMESTAMP;

You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:

LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, @dummy, column2, @dummy, column3);

Use of the column/variable list and SET clause is subject to the following restrictions:

• Assignments in the SET clause should have only column names on the left hand side of assignment operators.

• You can use subqueries in the right hand side of SET assignments. A subquery that returns a value to be assigned to a column may be a scalar subquery only. Also, you cannot use a subquery to select from the table that is being loaded.

[budimir]

OK, got it.

 

Thanks for the help!

 

This is reallllyyy fast...