SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
-
May 9, 2009, 08:11 #1
- 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" /> </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__); } } ?>
-
May 10, 2009, 00:47 #2
- 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.
-
May 10, 2009, 16:34 #3
- 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.
-
May 10, 2009, 18:55 #4
- 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.
-
May 10, 2009, 23:22 #5
- Join Date
- Jan 2007
- Posts
- 971
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
It was edited in and exported from Excel.
Bookmarks