How to upload a CSV file using PHP

Hey,

Does anyone know how i can insert the content from a CVS file into a database table?

For example my table has the following fields:

ID (Autoincrement), catID, title, body, address, postcode, tel, website

In the backend admin system my client wants to be able to upload large amounts of data rather than one by one.

Any ideas how i can do this?

Thanks again

With PHPMyAdmin?

Or you could google for it: http://www.google.com/#hl=en&q=csv+mysql+php&fp=1&cad=b

Hey,

Sorry i didn’t explain properly. I don’t want to do it via phpMyAdmin, it would need to be an insert statement on a website.

I need to implement a method in a website administration section where the user can upload a csv file.

So again it would need to be some sort of insert statement…

Is this possible?

Yes its possible and as guido2004 says search google and check hotscripts.

I have done it in the past but no longer have the code.

Ok i have had a look around and have implemented something… code is shown below:


    public function InsertCSVFileToDB(){
		
		$table = array(
		'tbl_reviews',
		);

		$columnheadings = 0;
				
		# perform the required operations for every table listed in the table array
		foreach ($table as $tablename) {
				
		$pass = 0;
		$fail = 0;
		
		$csvfile = mysql_real_escape_string($_FILES['csvfile']['name']); # gets the name of the csv file from the file upload

		$filecontents = file ($csvfile); # .csv is added to the table name to get the name of the csv file
		
		for($i=$columnheadings; $i<sizeof($filecontents); $i++) {
		$insertrecord = "Insert Into '$tablename' Values ($filecontents[$i])";
		mysql_query($insertrecord);
		if(mysql_error()) {
			$fail += 1; # increments if there was an error importing the record
			} else {
				$pass += 1; # increments if the record was successfully imported
			}
		}
		
		}
    }

Now the only thing i can’t work out is how to get the csv file from the file upload box?

In my front end where i will upload the CSV i have this:



if(isset($_POST['uploadCSV'])){
    $message = Review::InsertCSVFileToDB();
}

			<form enctype="multipart/form-data" action="" method="post" id="add-courses">
			    <table>
			        <tr>
			        	<td class="width"><label for="image">Upload CSV file : </label></td>
			        	<td><input type="hidden" name="MAX_FILE_SIZE" value="10000000" /><input type="file" name="csvfile" id="csvfile" value=""/></td>
						<td><input type="submit" name="uploadCSV" value="Upload" /></td>
					</tr>
				</table>
			</form>

But when i do the insert, i get this error:

Warning: file(tbl_reviews.csv) [function.file]: failed to open stream: No such file or directory in /domains/freemanholland.com/http/babies/classes/Review.class.php on line 211

Referring to this line:


		$filecontents = file ($csvfile); # .csv is added to the table name to get the name of the csv file

Any ideas what the problem is?

Thanks

Just to be sure, my csv filename is tbl_courses…

It just can’t seem to find it, could it be something wrong with the file itself?

Use ‘tmp_name’ to get the name of the file once it’s stored on the server:

$_FILES['csvfile']['tmp_name']

Ok now it doesn’t give me an error message but it doesn’t do the insert…

How can i do a die() to check this??

Thanks

The following script will help you to manage yourself your CSV upload.


<?php
$has_title_row = true;
if ($_SERVER['REQUEST_METHOD'] == 'POST'){
    if(is_uploaded_file($_FILES['csvfile']['tmp_name'])){
        $filename = basename($_FILES['csvfile']['name']);
        
        if(substr($filename, -3) == 'csv'){
            $tmpfile = $_FILES['csvfile']['tmp_name'];
            if (($fh = fopen($tmpfile, "r")) !== FALSE) {
                $i = 0;
                while (($items = fgetcsv($fh, 10000, ",")) !== FALSE) {
                    if($has_title_row === true && $i == 0){ // skip the first row if there is a tile row in CSV file
                        $i++;
                        continue;
                    }
                    print_r($items);
                    $i++;
                }
            }
        }
        else{
            die('Invalid file format uploaded. Please upload CSV.');
        }
    }
    else{
        die('Please upload a CSV file.');
    }
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
	<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
	<meta name="author" content="Raju Gautam" />
	<title>Test</title>
</head>
<body>
    <form enctype="multipart/form-data" action="" method="post" id="add-courses"> 
        <table cellpadding="5" cellspacing="0" width="500" border="0"> 
            <tr> 
                <td class="width"><label for="image">Upload CSV file : </label></td> 
                <td><input type="hidden" name="MAX_FILE_SIZE" value="10000000" /><input type="file" name="csvfile" id="csvfile" value=""/></td> 
                <td><input type="submit" name="uploadCSV" value="Upload" /></td> 
            </tr> 
        </table> 
    </form>
</body>
</html>

Ok i think this is working, it prints out the array:


if(isset($_POST['uploadCSV'])){
    $message = Review::InsertCSVFileToDB();

    $has_title_row = true;
	if(is_uploaded_file($_FILES['csvfile']['tmp_name'])){
        $filename = basename($_FILES['csvfile']['name']);

        if(substr($filename, -3) == 'csv'){
            $tmpfile = $_FILES['csvfile']['tmp_name'];
            if (($fh = fopen($tmpfile, "r")) !== FALSE) {
                $i = 0;
                while (($items = fgetcsv($fh, 10000, ",")) !== FALSE) {
                    if($has_title_row === true && $i == 0){ // skip the first row if there is a tile row in CSV file
                        $i++;
                        continue;
                    }
                    print_r($items);
                    $i++;
                }
            }
        }
        else{
            die('Invalid file format uploaded. Please upload CSV.');
        }
    }
    else{
        die('Please upload a CSV file.');
    }
}

It prints out this:

Array ( [0] => 1 [1] => SEA LIFE [2] => Well worth a visit is the Sea Life centre on Blackpool�s Golden Mile. [3] => SEA LIFE, Blackpool Promenade, Blackpool, Lancashire [4] => FY1 5AA [5] => 01253 621258 [6] => www.reddishvalefarm.co.uk [7] => Adult �12.50, Child 3-14 years �9.95, Under 3s free [8] => OTHER INFO [9] => baby.jpg [10] => baby.jpg [11] => baby.jpg [12] => baby.jpg )

These are the correct values. Now i need to insert this into the database, my existing insert statement was like so:


    public function InsertCSVFileToDB(){
		
		$table = array(
		'tbl_reviews',
		);

		$columnheadings = 0;
		
		# perform the required operations for every table listed in the table array
		foreach ($table as $tablename) {
				
		$pass = 0;
		$fail = 0;
		
		$csvfile = mysql_real_escape_string($_FILES['csvfile']['tmp_name']); # gets the name of the csv file from the file upload

		$filecontents = file ($csvfile); # .csv is added to the table name to get the name of the csv file
		
		for($i=$columnheadings; $i<sizeof($filecontents); $i++) {
		$insertrecord = "Insert Into '$tablename' Values ($filecontents[$i])";
		mysql_query($insertrecord);
		if(mysql_error()) {
			$fail += 1;
			} else {
				$pass += 1;
			}
		}
	  }
    }

How can i change this to get the insert working properly?

Thanks

Can you show me your table structure?

Table Structure is as follows:

catID, title, body, address, postcode, tel, website, admission, other, image1, image2, image3, image4, date_added

However catID is auto-increment, so i won’t need that. Also date_added is not in the csv file i will add this myself…

Hope you can help :blush:

My CSV file can be located here:

http://www.freemanholland.com/babies/csv/tbl_reviews.csv

Can i achieve what i am trying to do?


$has_title_row = true;
$not_done = array();
if ($_SERVER['REQUEST_METHOD'] == 'POST'){
    if(is_uploaded_file($_FILES['csvfile']['tmp_name'])){
        $filename = basename($_FILES['csvfile']['name']);
        
        if(substr($filename, -3) == 'csv'){
            $tmpfile = $_FILES['csvfile']['tmp_name'];
            if (($fh = fopen($tmpfile, "r")) !== FALSE) {
                $i = 0;
                while (($items = fgetcsv($fh, 10000, ",")) !== FALSE) {
                    if($has_title_row === true && $i == 0){ // skip the first row if there is a tile row in CSV file
                        $i++;
                        continue;
                    }
                    //print_r($items);
                    $sql = "INSERT INTO tbl_reviews SET
                            title='{$items[1]}', 
                            body='{$items[2]}', 
                            address='{$items[3]}', 
                            postcode='{$items[4]}', 
                            tel='{$items[5]}', 
                            website='{$items[6]}', 
                            admission='{$items[7]}', 
                            other='{$items[8]}', 
                            image1='{$items[9]}', 
                            image2='{$items[10]}', 
                            image3='{$items[11]}', 
                            image4='{$items[12]}'";
                    if(!mysql_query($sql)){
                        $not_done[] = $items;
                    }
                    $i++;
                }
            }
            // if there are any not done records found:
            if(!empty($not_done)){
                echo "<strong>There are some records could not be inseted</strong><br />";
                print_r($not_done);
            }
        }
        else{
            die('Invalid file format uploaded. Please upload CSV.');
        }
    }
    else{
        die('Please upload a CSV file.');
    }
}

That’s worked perfectly, however it only inserted one row, I had 2 rows in the csv file and the second row did not insert…

Any ideas?

I’m almost sure the first row didn’t insert.
Do you have a first line with the column headers in your csv file? Or only data?
If you don’t have a first line with the column headers, read rajug’s code carefully, you should be able to spot the problem and eliminate it.

Sorry, my mistake it missed out the first row and inserted the second one. (I had both rows the same so i couldn’t tell…)

Anyway, reading the code it looks like this:


                    if($has_title_row === true && $i == 0){ // skip the first row if there is a tile row in CSV file
                        $i++;
                        continue;
                    }

Is skipping the first row? I don’t mind this, as i will have headings in the first row and the actual data from the second row onwards.

Also, is there a way i can control apostrophes and £ symbols??

I need to check to see if there is a £ symbol and convert it to £

Is this doable?

Run htmlentities() on each of the $items would be how I would do it

That’s what it’s supposed to do :wink:

If you set the $has_title_row to false, the first row will be inserted as well.

If possible can you show an example of this being used in an insert statement like mine?

:confused: