Jump to content

LOAD DATA INFILE error?


NerdConcepts

Recommended Posts

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.