NerdConcepts Posted June 8, 2007 Share Posted June 8, 2007 Ok, this is getting on my last nerve. I have been using this same script to import CSV files into MySQL with 70 different fields. I have moved to using the same code, just the table has changed and the number of fields is only 10. This CSV file normally uses a tab to seperate columns, I have PHP replace all tabs with commas after it uploads the file. Here are the two files that are needed to run the script dn_inventory_csv.php <?PHP //date_default_timezone_set('America/Chicago'); class Quick_CSV_import { public $table_name; //where to import to public $file_name; //where to import from public $use_csv_header; //use first line of file OR generated columns names public $field_separate_char; //character to separate fields public $field_enclose_char; //character to enclose fields, which contain separator char into content public $field_escape_char; //char to escape special symbols public $error; //error message public $arr_csv_columns; //array of columns public $table_exists; //flag: does table for import exist public $encoding; //encoding table, used to parse the incoming file. Added in 1.5 version function Quick_CSV_import($file_name="") { $this->file_name = $file_name; $this->arr_csv_columns = array(); $this->use_csv_header = true; $this->field_separate_char = ","; $this->field_enclose_char = "\""; $this->field_escape_char = "\\"; $this->table_exists = true; } function import() { $this->table_name = "dn_inventory_temp"; if(empty($this->arr_csv_columns)) $this->get_csv_header_fields(); if($this->table_exists) { $sql = "LOAD DATA INFILE '".@mysql_escape_string($this->file_name). "' INTO TABLE `".$this->table_name. "` FIELDS TERMINATED BY '".@mysql_escape_string($this->field_separate_char). "' OPTIONALLY ENCLOSED BY '".@mysql_escape_string($this->field_enclose_char). "' ESCAPED BY '".@mysql_escape_string($this->field_escape_char). "' ". ($this->use_csv_header ? " IGNORE 1 LINES " : "") ."(`".implode("`,`", $this->arr_csv_columns)."`)"; $res = @mysql_query($sql) or trigger_error("Query: $sql\n<br />MySQL Error: " . mysql_error()); $this->error = mysql_error(); } } //returns array of CSV file columns function get_csv_header_fields() { $this->arr_csv_columns = array(); $fpointer = fopen($this->file_name, "r"); if ($fpointer) { $arr = fgetcsv($fpointer, 10*1024, $this->field_separate_char); if(is_array($arr) && !empty($arr)) { if($this->use_csv_header) { foreach($arr as $val) if(trim($val)!="") $this->arr_csv_columns[] = $val; } else { $i = 1; foreach($arr as $val) if(trim($val)!="") $this->arr_csv_columns[] = "column".$i++; } } unset($arr); fclose($fpointer); } else $this->error = "file cannot be opened: ".(""==$this->file_name ? "[empty]" : @mysql_escape_string($this->file_name)); return $this->arr_csv_columns; } } ?> dn_inventory_upload.php <?PHP require('includes/init.php'); include("classes/dn_inventory_csv.php"); include($theme . 'header.php'); $csv = new Quick_CSV_import(); if (isset($_POST['submitted'])) { $target = "csv_storage/"; $target = $target . basename($_FILES['file_source']['name']) ; $ok=1; if(move_uploaded_file($_FILES['file_source']['tmp_name'], $target)) { echo '<span class="title">Upload Successful.<br /><br /></span>'; } else { echo "Sorry, there was a problem uploading your file.<br /><br />"; } // Read Uploaded File $fcontents = "csv_storage/" . $_FILES['file_source']['name']; $fh = fopen($fcontents, 'r'); $filecontents = fread($fh, filesize($fcontents)); // Modify Data $lines = explode("\n", $filecontents); $lines[0] = str_replace(" ", "_", $lines[0]); $lines = str_replace("\t",",", $lines); //$lines = str_replace("\"","", $lines); $filecontents = join("\n", $lines); fclose($fh); // Write New Data $fw = fopen($fcontents, 'w'); $stringData = $filecontents; fwrite($fw, $stringData); fclose($fw); // Import CSV // //$csv->file_name = "/home/rewerkd1/public_html/csvtophp/csv_storage/" . $_FILES['file_source']['name']; $csv->file_name = "c:\\wamp\\www\\csvtophp\\csv_storage\\" . $_FILES['file_source']['name']; $csv->import(); } ?> <form enctype="multipart/form-data" action="dn_inventory_import.php" method="post"> <fieldset> <legend>Dish Network Record Import</legend> Select Record Output File <input type="file" name="file_source" /> <br /> <input type="hidden" name="submitted" value="TRUE" /> <input type="submit" name="submit" value="Import" /> </fieldset> </form> <?PHP include($theme . 'footer.php'); ?> Here is a the file before and after import. Before: Part Description Waybill Part Number R00 S00 Serial Number Ship Date Ship Method Tracking Number Warehouse Description Test 0000000000 0000000000-00 No Value No Value No Value 35:48.0 FEDEX FREIGHT 0000000000 WAREHOUSE After: Part_Description,Waybill,Part_Number,R00,S00,Serial_Number,Ship_Date,Ship_Method,Tracking_Number,Warehouse Description Test,0000000000,0000000000-00,No Value,No Value,No Value,35:48.0,FEDEX FREIGHT,0000000000,WAREHOUSE When I import the file I get this weird error. An error occurred in script 'C:\wamp\www\csvtophp\classes\dn_inventory_csv.php' on line 46: Query: LOAD DATA INFILE 'c:\\wamp\\www\\csvtophp\\csv_storage\\output.csv' INTO TABLE `dn_inventory_temp` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\' IGNORE 1 LINES (`ÿþP Remember, I am currently using this (almost) same exact code to do importing from CSV to MySQL on a 70 column file. I have checked and triple checked the database fields, they are completely correct. Link to comment https://forums.phpfreaks.com/topic/54714-load-data-infile-error/ Share on other sites More sharing options...
NerdConcepts Posted June 9, 2007 Author Share Posted June 9, 2007 I've played with this for quite sometime more, yet, I still cannot find any problems with the code and cannot understand why it worked with another table/file combination but doesn't now. Even though there are no errors in the .csv file. Link to comment https://forums.phpfreaks.com/topic/54714-load-data-infile-error/#findComment-271204 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.