PHP CSV Import into MySQL

[wiggst3r]

Hi

 

I have a CSV file which I want to import into mysql, which has approx 10,000 rows.

 

The mysql database has 5 (id, name, post_code, email, created_at) fields, but the CSV only has 2 fields (name, email).

 

I'm looking to create a PHP script, that will loop through each row of the CSV and then import each row into mysql.

 

The only problem is, that some rows have an empty field. E.g. one row may have the name but no email address.

 

How could I import the CSV and make sure that even though there maybe a blank field in the CSV that it still gets imported?

 

Thanks

[DarkWater]

What's the point of having 5 fields if you're going to have 10,000 rows of NULL values for 3 of those fields?

[wiggst3r]

The database already has approx 20,000 rows, that have come via a form.

The extra 10,000 rows only need to have name and email address. The fact that they have null values for 3 fields isn't important.

The database is used to send out emails, so I only need 2 fields. The other fields (post_code and created_at) is from the form and is not used for the email send outs.

 

I've got the following so far:

 

<?php
  $connection = mysql_connect("localhost", "root", "") or die ("Unable to connect to server");
  $db = mysql_select_db("test", $connection) or die ("Unable to select database");

      
		$handle = fopen ('./test.csv', 'r');
				while (($data = fgetcsv($handle, 1500, ',', '"')) !== FALSE)
				{

					$query = "INSERT INTO entries VALUES ( \"". implode("\",\"", $data)."\")";
					//echo $query;
					echo "<br />";
		 			$query = mysql_query($query);
				}

       ?>

 

This works fine. Echoing out the query, I get

 

INSERT INTO entries VALUES ( " Test","test@test.com")

 

I want to change the code above, so the query is:

INSERT INTO entries VALUES ( "", "Test","", "test@test.com", now())

 

So you see, I want it to insert the 5 fields, rather than 2 as I'm getting an error due to the query trying to insert 2 fields, instead of the 5 it requires.

 

Thanks

 

[DarkWater]

<?php
$connection = mysql_connect("localhost", "root", "") or die ("Unable to connect to server");
$db = mysql_select_db("test", $connection) or die ("Unable to select database");
$filename = './test.csv';

foreach (file($filename) as $line) {
$data = explode(',', $line);
$query = sprintf('INSERT INTO entries (name, email) VALUES ("%s")', implode('","'$data));
mysql_query($query) or die(mysql_error());
}
?>

[wiggst3r]

I get an error on line 8...

Parse error: syntax error, unexpected T_VARIABLE

[DarkWater]

Missed a ,

<?php
$connection = mysql_connect("localhost", "root", "") or die ("Unable to connect to server");
$db = mysql_select_db("test", $connection) or die ("Unable to select database");
$filename = './test.csv';

foreach (file($filename) as $line) {
   $data = explode(',', $line);
   $query = sprintf('INSERT INTO entries (name, email) VALUES ("%s")', implode('","', $data));
   mysql_query($query) or die(mysql_error());
}
?>

 

Next time you get a syntax error from code posted on the forums, at least try to fix it.

[wiggst3r]

Ok, my apologies.

Running the code above, fixes the syntax error.

 

I now get the following:

 

INSERT INTO entries (name, email) VALUES ("" Test","test@test.com" ")You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Test","test@test.com" ")' at line 1

 

There is an extra " at the beginning and end of the query. Is this due to the ("%S") ?

 

Thanks

 

 

[DarkWater]

That's weird.  It should NOT be adding extra quotes; that's why I put the " " around %s.  Try taking them off of %s.  Weird.