SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    971
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    CSV upload script mixing up columns

    I wrote a script that uploads a CSV and appends a database. It is posted below for reference.

    PROBLEM
    When I uploaded a CSV last night, One of the rows was jumbled. One of the columns in the row was broken in the middle the other half was put into a different column. Strangely, not the next one in sequence but one that seemed random.

    None of the other rows were affected. My assumption was that there was a character in the field that was interpreted as a delimiter
    , but I couldn't anything when I opened it in a text editor.

    Any Ideas?

    Thanks

    Code PHP:
    <?
     
     ini_set('auto_detect_line_endings', true);
     
    function CSV_Form(){
     
    if ($_GET['errors']){$errors="<p >".$_GET['errors']."</p>";}
     
    echo '
    <h1>Add New Products Via CSV File Upload</h1>'.stripslashes($errors).'
     
    <form id="form1" name="form1" enctype="multipart/form-data" method="post" action="?action=CSV_upload">
      <label>
      <input type="file" name="csv" id="csv" />&nbsp;&nbsp;
      </label>
      <br /><br />
      <input name="" type="submit" />
    </form>';
     
    }
     
    function CSV_upload(){
     
    	$target_path = $_SERVER['DOCUMENT_ROOT']."/csv/";
     
     
    	$target_path = $target_path ."products_". basename( date('m-d-y_H:i')).".csv"; 
     
    	if(move_uploaded_file($_FILES['csv']['tmp_name'], $target_path)) {
    		//echo "The file ".  basename( $_FILES['csv']['name']). 
    		" has been uploaded";
    	} else{
    		//echo "There was an error uploading the file, please try again!";
    	}
     
    	$filename = $target_path;
    	$file_handle = fopen($filename, "r");
    	$x=1;
    	while (!feof($file_handle) ) {
     
    		$line_of_text = fgetcsv($file_handle, 1024);
     
    		//print_r( $line_of_text);
     
    		if ($x==1){
     
    			$errors=validateHeaders($line_of_text); 
    			if ($errors!=''){ die( header('Location: ?go=upload_csv&errors='.$errors) ); }
     
    		} else { insertText($line_of_text); }
     
    		//echo $line_of_text[0]." ".$line_of_text[1]." ".$line_of_text[2]."<br/>";
     
    		$x++;
    	}
     
    	fclose($file_handle);
     
     
    }
     
    function validateHeaders($headers){
    //21 headers
     
    $c=0;
    foreach ($headers as $h){
     
    	//echo $c.":".$h."<br>";
    $c++;
    }
     
    $error;
    	if ($headers[0]!="product" ){$error="column 1 incorect '".$headers[0]."' found 'product' expected  <br>";}
    	if ($headers[1]!="sku" ){$error.$error="column 2 incorect '".$headers[1]."' found 'sku' expected <br>";}
    	if ($headers[2]!="price" ){$error.$error="column 3 incorect '".$headers[2]."' found 'price' expected<br>";}
    	if ($headers[3]!="description" ){$error.$error="column 4 incorect '".$headers[3]."' found 'description' expected<br>";}
    	if ($headers[4]!="photo" ){$error.$error="column 5 incorect '".$headers[4]."' found 'photo' expected<br>";}
    	if ($headers[5]!="latin_name" ){$error.$error="column 6 incorect '".$headers[5]."' found 'latin_name' expected<br>";}
    	if ($headers[6]!="Featured_animal" ){$error.$error="column 7 incorect '".$headers[6]."' found 'Featured_animal' expected<br>";}
    	if ($headers[7]!="top" ){$error.$error="column 8 incorect '".$headers[7]."' found 'top' expected<br>";}
    	if ($headers[8]!="new" ){$error.$error="column 9 incorect '".$headers[8]."' found 'new' expected<br>";}
    	if ($headers[9]!="menu" ){$error.$error="column 10 incorect '".$headers[9]."' found 'menu' expected<br>";}
    	if ($headers[10]!="active" ){$error.$error="column 11 incorect '".$headers[10]."' found 'active' expected<br>";}
    	if ($headers[11]!="category" ){$error.$error="column 12 incorect '".$headers[11]."' found 'category' expected<br>";}
    	if ($headers[12]!="keywords" ){$error.$error="column 13 incorect '".$headers[12]."' found 'keywords' expected<br>";}
    	if ($headers[13]!="sequence" ){$error.$error="column 14 incorect '".$headers[13]."' found 'sequence' expected<br>";}
    	if ($headers[14]!="link" ){$error.$error="column 15 incorect '".$headers[14]."' found 'link' expected<br>";}
    	if ($headers[15]!="link_name" ){$error.$error="column 16 incorect '".$headers[15]."' found 'link_name' expected<br>";}
     
    	return $error;	
     
    	header('location: ?manage_products');
    }
     
     
     
     
    function insertText($theValues){
    	//print_r($theValues);exit();
        $x=0;
    	/*foreach($theValues as $v){
     
    		$theValues[$x]=mysql_escape_string($v);
    		$x++;
    	}
    */
     
    	if ($theValues[0]){
     
    		$query="
    		INSERT INTO products 
    		( id,product, sku, price,description, image,latin_name,	Featured_animal, top, new, menu, active,category, keywords,sequence, link, linkname)
     
    		VALUES (
    		'', 
    		'".mysql_escape_string($theValues[0])."',
    		'".mysql_escape_string($theValues[1])."',
    		'".mysql_escape_string($theValues[2])."', 
    		'".mysql_escape_string($theValues[3])."', 
    		'".mysql_escape_string($theValues[4])."', 
    		'".mysql_escape_string($theValues[5])."', 
    		'".mysql_escape_string($theValues[6])."', 
    		'".mysql_escape_string($theValues[7])."', 
    		'".mysql_escape_string($theValues[8])."', 
    		'".mysql_escape_string($theValues[9])."', 
    		'".mysql_escape_string($theValues[10])."', 
    		'".mysql_escape_string($theValues[11])."', 
    		'".mysql_escape_string($theValues[12])."', 
    		'".mysql_escape_string($theValues[13])."', 
    		'".mysql_escape_string($theValues[14])."', 		
    		'".mysql_escape_string($theValues[15])."'
    		)";
     
    		//echo $query;
     
    		dbconnect();
    		mysql_query($query)or die(mysql_error()." line: ". __LINE__);
     
    	}
     
    }
     
    ?>

  2. #2
    SitePoint Zealot
    Join Date
    Apr 2009
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't think the problem it in the script, I think you were correct that the problem is in the CSV.

    Look more carefully at the CSV, look also at the line(s) preceeding where the apparent error occurred.

    Use an editor capable of handling hex files to preclude the possibility that a text editor masks what might cause the script error.

  3. #3
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    971
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks, Ill have to make sure the text editor I'm using can handle hex files.

    I was hoping to be able to identify the problem and validate/self correct.

  4. #4
    SitePoint Zealot
    Join Date
    Apr 2009
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't think that is a likely problem, but there is a possibility there is something in what looks like a text file that gets munged by a common "text" editor, or that throws the csv importer out of whack.

  5. #5
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    971
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It was edited in and exported from Excel.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •