MySQL Icon

PHP Upload a CSV File into MySQL

Sometimes clients want to put all their products online in a database but don’t want to manage them as they may already be doing so in some kind of spreadsheet or offline database (Excel, Numbers, Filemaker, etc). For them, they don’t want to learn any new systems but want to have both areas, offline and online, up to date and mirroring each other. If you run into something like this, it’s very easy to populate your table via a CSV file upload.

CSV File

All spreadsheet programs make it easy to export data as a CSV file. This is a comma separated file of all the data in the spreadsheet, one line per row.

Upload Form

Lets start with a really basic upload form:

<form action="" method="post">
	<input type="file" name="csv_file">
	<input type="submit" name="csv_submit" value="Upload CSV File">
</form>

You will want to have this page authenticated and password protected to access this page.

Uploading the File

//database connect info here

//check for file upload
if(isset($_FILES['csv_file']) && is_uploaded_file($_FILES['csv_file']['tmp_name'])){
	
	//upload directory
	$upload_dir = "csv_dir/";
	
	//create file name
	$file_path = $upload_dir . $_FILES['csv_file']['name'];
	
	//move uploaded file to upload dir
	if (!move_uploaded_file($_FILES['csv_file']['tmp_name'], $file_path)) {
		
		//error moving upload file
		echo "Error moving file upload";

	}
	
}

Here we connect to our database then check to see if $_FILES['csv_file'] variable is set and if it’s a uploaded file. If your interested to see what data is recorded when a file is uploaded do print_r($_FILES['csv_file']);

When a file is uploaded, PHP stores it in a temporary directory so in order to access the file we need to move it to somewhere we can access. This is where move_uploaded_file() comes in. It takes the temporary file and moves it to a location of our choosing. Now that the uploaded file is where we can access it, we can open it and read the data.

Read the File

Here’s where it starts to get a little more complex. First we need a file handle to read the file so we’ll need to open it.

	
	//open the csv file for reading
	$handle = fopen($file_path, 'r');

Now we’ll use a little MySQL trickery to keep us from losing our data if we encounter any problems inserting the CSV data into the database. All the previous product data is wiped from the table. Auto commit is off so we can rollback if we run into any issues.

//turn off autocommit and delete the product table
mysql_query("SET AUTOCOMMIT=0");
mysql_query("BEGIN");
mysql_query("TRUNCATE TABLE product_table") or die(mysql_error());

Here we loop through the CSV file row by row and insert the data into the database. Notice that we are using mysql_escape_string to filter any strings going into our table. We also trigger a ROLLBACK call if any MySQL errors happen. With auto commit off and rollback, this saves us from losing all our previous data if the data insert fails.

	while (($data = fgetcsv($handle, 1000, ',')) !== FALSE) {
		
		//Access field data in $data array ex.
		$name = $data[0];
		$quantity = $data[1];
		
		//Use data to insert into db 
		$sql = sprintf("INSERT INTO product_table (product_name, product_quantity) VALUES ('%s',%d)",
					mysql_real_escape_string($name),
					$quantity
					);
		mysql_query($sql) or (mysql_query("ROLLBACK") and die(mysql_error() . " - $sql"));
	}
	
	

Lets finish off by committing the inserted data into the database, turning auto commit back on, and deleting the CSV file now that we don’t need it anymore.

	
//commit the data to the database
mysql_query("COMMIT");
mysql_query("SET AUTOCOMMIT=1");

//delete csv file
unlink($file_path);

The Final Script

//database connect info here

//check for file upload
if(isset($_FILES['csv_file']) && is_uploaded_file($_FILES['csv_file']['tmp_name'])){
	
	//upload directory
	$upload_dir = "csv_dir/";
	
	//create file name
	$file_path = $upload_dir . $_FILES['csv_file']['name'];
	
	//move uploaded file to upload dir
	if (!move_uploaded_file($_FILES['csv_file']['tmp_name'], $file_path)) {
		
		//error moving upload file
		echo "Error moving file upload";

	}
	
	//open the csv file for reading
	$handle = fopen($file_path, 'r');
	
	//turn off autocommit and delete the product table
	mysql_query("SET AUTOCOMMIT=0");
	mysql_query("BEGIN");
	mysql_query("TRUNCATE TABLE product_table") or die(mysql_error());

	while (($data = fgetcsv($handle, 1000, ',')) !== FALSE) {
		
		//Access field data in $data array ex.
		$name = $data[0];
		$quantity = $data[1];
		
		//Use data to insert into db 
		$sql = sprintf("INSERT INTO product_table (product_name, product_quantity) VALUES ('%s',%d)",
					mysql_real_escape_string($name),
					$quantity
					);
		mysql_query($sql) or (mysql_query("ROLLBACK") and die(mysql_error() . " - $sql"));
	}
	
	//commit the data to the database
	mysql_query("COMMIT");
	mysql_query("SET AUTOCOMMIT=1");
	
	//delete csv file
	unlink($file_path);
}

This gives the client one easy method for updating their data whenever they like.

Continue Reading

  • CR

    Thanks a lot for this info. Do you know the best way to create csv from a form and email it as an attachment? I found some good info on it here but am getting caught up

    http://hubpages.com/hub/PHP-form-inserts-data-into-CSV-file

    Thanks in advance for any help and/or direction

    CR

    • Skye

      That shouldn’t be too hard. Where are you getting hung up? On the csv file or the attachment? Drop me a message through the contact form if you want some help.

  • maxbux

    I get a parse error around line 39

    Parse error: syntax error, unexpected ‘;’ in C:\wamp\www\sandbox\upload2.php on line 39

    here is the area around line 39

    //Use data to insert into db
    $sql = sprintf(“INSERT INTO product_table (product_name, product_quantity) VALUES (‘%s’,%d)”,
    mysql_real_escape_string($name),
    $quantity,
    );
    mysql_query($sql) or (mysql_query(“ROLLBACK”) and die(mysql_error() . ” – $sql”));
    }

    • Skye

      My bad. Extra comma after $quantity. Fixed.

  • http://www.welidz.nl Allan

    I really got no idea what Im doing wrong.
    I copied the code, made a form to upload and then the uploadscript.
    Then when I check the database i see no new line.

    tbl name is ok
    file = csv
    Even gave the form GET method.
    and I put in an extra line for the cmod options (777).
    Is there something I dont do right?

    ty!

  • http://www.welidz.nl Allan

    sorry was to quick….didn’t read properly….my excuse. forgo to change the tablecolumnnames :)

  • http://www.welidz.nl Allan

    Nice!
    it works :) only now im having te problem that the script deleted all the information in de table! LOL!!!!
    anyway, nice script! thanks!

  • http://www.welidz.nl Allan

    Is it possible not to use TRUNCATE ?

  • http://www.welidz.nl Allan

    I Also get Undefined offset errors..?

  • Skye

    If you just want to insert new rows without truncating the table just remove lines 24-26 and 43-44.

    Do a print_r($data) inside the while to see exactly what data is in which fields. I’m assuming manipulating the $data array is where you are getting the undefined offsets.

  • Matt

    Thanks, great tutorial!

  • Matt M

    Anyway to show the first few rows of data in the CSV and let them map the CSV columns to the database columns?

    • Skye

      You could print out the first row with all the header info then ask them to map it to the columns they want using textfields or something. It shouldn’t be too hard.

  • EnriqueCM

    Great tutorial, but i have some problem to add more fields.

    any ideas?

    while (($data = fgetcsv($handle, 1000, ‘,’)) !== FALSE) {

    //Access field data in $data array ex.
    $time = $data[0];
    $id = $data[1];
    $name = $data[2];
    $department = $data[3];
    $terminalid = $data[4];
    $terminalname = $data[5];
    $auth = $data[6];
    $result = $data[7];
    $key = $data[8];

    //Use data to insert into db
    $sql = sprintf(“INSERT INTO acceso (time, id, name, department, terminalid, terminalname, auth, result, key) VALUES (‘%s’,%d)”,
    mysql_real_escape_string($time),
    $id,
    $name,
    $department,
    $terminalid,
    $terminalname,
    $auth,
    $result,
    $key
    );
    mysql_query($sql) or (mysql_query(“ROLLBACK”) and die(mysql_error() . ” – $sql”));
    }

    //commit the data to the database
    mysql_query(“COMMIT”);
    mysql_query(“SET AUTOCOMMIT=1″);

    //delete csv file
    unlink($file_path);
    }
    ?>

    Regards

    • Skye

      What’s the error?

  • Mark Cooper

    Is there any way to skip the firs few rows in the csv file, the CSV i would like to use has a title and blank rows before the data starts

    • Skye

      Just call fgetcsv($handle, 1000, ‘,’) before the loop for each row you want to skip.