How to upload a CSV file using PHP

Do it something like this:


$sql = "INSERT INTO tbl_reviews SET
            title='" . htmlentities($items[1]) . "', 
            body='" . htmlentities($items[2]) . "', 
            address='" . htmlentities($items[3]) . "', 
            postcode='" . htmlentities($items[4]) . "', 
            tel='" . htmlentities($items[5]) . "', 
            website='" . htmlentities($items[6]) . "', 
            admission='" . htmlentities($items[7]) . "', 
            other='" . htmlentities($items[8]) . "', 
            image1='" . htmlentities($items[9]) . "', 
            image2='" . htmlentities($items[10]) . "', 
            image3='" . htmlentities($items[11]) . "', 
            image4='" . htmlentities($items[12]) . "'";

And for euro sign it is better find and replace would be fine AFAIK.

Hey,

This worked fine, it converted the £ symbols correctly however the apostrophes don’t work. See this page:

http://www.freemanholland.com/babies/reviews/?ID=1

So would a good idea be to use str_replace ??

Looking back at my Insert statement, i have altered it and put it within a function:


    public function InsertCSVFileToDB(){
		
	$has_title_row = true;
	$not_done = array();
	
	    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
	                            catID='{$items[0]}',
	                            title='" . htmlentities($items[1]) . "',
					            body='" . htmlentities($items[2]) . "',
					            address='" . htmlentities($items[3]) . "',
					            postcode='" . htmlentities($items[4]) . "',
					            tel='" . htmlentities($items[5]) . "',
					            website='" . htmlentities($items[6]) . "',
					            admission='" . htmlentities($items[7]) . "',
					            other='" . htmlentities($items[8]) . "',
					            image1='" . htmlentities($items[9]) . "',
					            image2='" . htmlentities($items[10]) . "',
					            image3='" . htmlentities($items[11]) . "',
					            image4='" . htmlentities($items[12]) . "',
	                            date_added = now()";
	                    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.');
	    }
    }

Where can i use str_replace or htmlentities to fix the apostrophe issue?

Thanks

Billy, can you please try to see the functions that are used in the script before you use them what parameters they need for what? The function htmlentities has second parameter for handling single/double quotes. Please visit the manual page once and try to find out how to do that?

http://www.php.net/manual/en/function.htmlentities.php

I think you should use ENT_QUOTES as second parameter to convert the single quotes.

Erm, why aren’t you using [fphp]mysql_real_escape_string[/fphp] on those values? :rolleyes:

Rajug, you should know better! :stuck_out_tongue:

Hey,

Thanks Anthony. I have tried with this:


    public function InsertCSVFileToDB(){
		
	$has_title_row = true;
	$not_done = array();
	
	    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
	                            catID='{$items[0]}',
								title = mysql_real_escape_string(htmlentities($items[1])),					
								body = mysql_real_escape_string(htmlentities($items[2])),					
								address = mysql_real_escape_string(htmlentities($items[3])),					
								postcode = mysql_real_escape_string(htmlentities($items[4])),					
								tel = mysql_real_escape_string(htmlentities($items[5])),					
								website = mysql_real_escape_string(htmlentities($items[6])),					
								admission = mysql_real_escape_string(htmlentities($items[7])),					
								other = mysql_real_escape_string(htmlentities($items[8])),					
								image1 = mysql_real_escape_string(htmlentities($items[9])),					
								image2 = mysql_real_escape_string(htmlentities($items[10])),					
								image3 = mysql_real_escape_string(htmlentities($items[11])),					
								image4 = mysql_real_escape_string(htmlentities($items[12])),					
	                            date_added = now()";
	                    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.');
	    }
    }

But the INSERT now does not work…

Any ideas ?

Thanks

Ahh yes that can be used to escape the quotes but to convert the special characters like euro signs he has to use htmlentities() AFAIK.

Edit:
Billy use in this way:


$sql = "INSERT INTO tbl_reviews SET
            title='" . mysql_real_escape_string(htmlentities($items[1])) . "', 
            body='" . mysql_real_escape_string(htmlentities($items[2])) . "', 
            address='" . mysql_real_escape_string(htmlentities($items[3])) . "', 
            postcode='" . mysql_real_escape_string(htmlentities($items[4])) . "', 
            tel='" . mysql_real_escape_string(htmlentities($items[5])) . "', 
            website='" . mysql_real_escape_string(htmlentities($items[6])) . "', 
            admission='" . mysql_real_escape_string(htmlentities($items[7])) . "', 
            other='" . mysql_real_escape_string(htmlentities($items[8])) . "', 
            image1='" . mysql_real_escape_string(htmlentities($items[9])) . "', 
            image2='" . mysql_real_escape_string(htmlentities($items[10])) . "', 
            image3='" . mysql_real_escape_string(htmlentities($items[11])) . "', 
            image4='" . mysql_real_escape_string(htmlentities($items[12])) . "'";

FIEO. :wink:

The entity encoding should be upon output, what if the OP wanted to offer a non-html version of the data?

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

This was asked by the OP.

First idea, is to find out what is in $sql. Echo it out and look for issues in the SQL statement.

If you can’t find any, run that SQL statement directly on your test database, as that can often provide you with more information about what the trouble is.

Hey,

I am trying to insert it with this code as there are some symbols in the csv file that are preventing the insert to work…


    public function InsertCSVFileToDB(){
		
	$has_title_row = true;
	$not_done = array();
	
	    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;
	                    }

						$body = $items[2];
						$body = preg_replace("/[^\\x9\\xA\\xD\\x20-\\x7F]/", "", $body);
						$body = mysql_real_escape_string(htmlentities($body));
												
	                    $sql = "INSERT INTO tbl_reviews SET
	                            catID='{$items[0]}',
					            title='" . mysql_real_escape_string(htmlentities($items[1])) . "',
					            body = '$body',
					            address='" . mysql_real_escape_string(htmlentities($items[3])) . "',
					            postcode='" . mysql_real_escape_string(htmlentities($items[4])) . "',
					            tel='" . mysql_real_escape_string(htmlentities($items[5])) . "',
					            website='" . mysql_real_escape_string(htmlentities($items[6])) . "',
					            admission='" . mysql_real_escape_string(htmlentities($items[7])) . "',
					            other='" . mysql_real_escape_string(htmlentities($items[8])) . "',
					            image1='" . mysql_real_escape_string(htmlentities($items[9])) . "',
					            image2='" . mysql_real_escape_string(htmlentities($items[10])) . "',
					            image3='" . mysql_real_escape_string(htmlentities($items[11])) . "',
					            image4='" . mysql_real_escape_string(htmlentities($items[12])) . "',	
					            date_added = now()";	
	                    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.');
	    }
    }

This is the CSV file i am using, any ideas what could be preventing the code to work?

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

I don’t get any errors… nothing happens.

Any ideas?

Thanks

Also my form and code execution is like so:


if(isset($_POST['uploadCSV'])){
	$message = Review::InsertCSVFileToDB();
}
?>
		<div id="right-content" class="right">
       	<h1>reviews</h1>
       	        	       	        	
       	 <div style="padding:10px; background:#efefef">
			<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="1000000000000" /><input type="file" name="csvfile" id="csvfile" value=""/></td>
						<td><input type="submit" name="uploadCSV" value="Upload" /></td>
					</tr>
				</table>
			</form>
       	 </div>

I have just completed a similar task and hit the limit with my POWWEB ISP. I Googled and came up with this solution:



//===============================================
function _php_solution_3($local_csv, $first_record=0)
{
echo jj, 'Truncating table: ';
		$sql 		= 'TRUNCATE TABLE books_5';
		$result = mysql_query($sql);

	if (($handle = fopen($local_csv, "r")) !== FALSE)
	{
		// Load all records into table 1
		$sql = "
						LOAD DATA LOCAL INFILE '$local_csv' 
						REPLACE INTO TABLE books_5 
						FIELDS TERMINATED BY ',' 
						"
					." OPTIONALLY ENCLOSED BY '\\"' LINES TERMINATED BY '\\\
' IGNORE 1 LINES";
	mysql_query($sql);
	}//endif
		fclose($handle);

echo jj, 'All Done now Creating Database Relationships';
		// create author and group relationships
		$this->truncate_author_groups_tables_set_book_relationships();
		echo jj, 'All Done and ready to go';
	
}//endfunc	


and here is the set relation stuff:


//===============================================
// DOWNLOAD REMOTE FILE
// this is a-lot more complicated than it looks
// $url is the url of the file, must have fopen wrappers enabled
// $local_csv is the local_csvectory to save the file to relative to the current working local_csvectory
// file is saved with the same file name from its source
// function get_remote_csv($remote_csv='', $local_csv='')
//===============================================
function truncate_author_groups_tables_set_book_relationships($remote_csv='', $local_csv='')
{
	// $updated = $this-&gt;_get_file_difference($remote_csv, $local_csv);
	
	// check to see if file exists
	if (TRUE OR file_exists($local_csv))
	{
		// 
		$sql = 'TRUNCATE TABLE authors';
		$result = mysql_query($sql);
		$sql = '
						INSERT INTO authors (`author`)
						SELECT DISTINCT `author` 
						FROM `books_5` 
						WHERE 1 
						ORDER BY `author`
						';
		$result = mysql_query($sql);
		

		$sql = 'TRUNCATE TABLE groups';
		$result = mysql_query($sql);
		$sql = '
						INSERT INTO groups (`group`)
						SELECT DISTINCT `category` 
						FROM `books_5` 
						WHERE 1 
						ORDER BY `category`
						';
		$result = mysql_query($sql);

		// Update books_5
		$sql = '
						UPDATE books_5, authors
						SET books_5.id_author = authors.id
						WHERE books_5.author 	= authors.author
					';
		$result = mysql_query($sql);

		// Update books_5
		$sql = '
						UPDATE books_5, groups
						SET books_5.id_category = groups.id
						WHERE books_5.category 	= groups.group
					';
		$result = mysql_query($sql);
	}
	
	
}//endfunc


And here is the site that uses the 15,696 books:
http://www.dasabookcafe.anetizer.com/

.

Hey,

I appreciate your assistance, however i would prefer if i could get the code working using my method?

It actually does insert now, however i need to convert the symbols into the relevant text. For example, on this page:

http://freemanholland.com/babies/reviews/?ID=8

If you look at the reviews, you will notice quite a lot of symbols…

I am actually using this:

              $body = $items[2];
              $body = str_replace('&Ocirc;', '&#39;', $body); // O symbol
              $body = str_replace('&Otilde;', '&#39;', $body); // O symbol
              $body = str_replace('&ETH;', '', $body); // D symbol
              $body = str_replace('&#x17D;', '&#101;', $body); // Z symbol
              $body = mysql_real_escape_string(htmlentities($body));

Any idea why this may not work?

You do know that your reviews are inaccessible to non-registered visitors… Folk cannot see what you’re describing. :wink:

It’s ok, i had to start another thread on str_replace as that is what was causing the problem…

If you visit that link it should give you some of the data from the review… Or you can register :slight_smile: