CSV Data Placement and Updating issue

NerdConcepts · June 5, 2007

Well, I've got the following script, there are notes in there explaining what I am already going. What I want it to do it is to read a header called "WORK_ORDER_NUMBER" from that I want it to crab the value from the csv file from each line, and reference it to my database. If it already exists I want it to update. I have no problem having PHP upload the file, read the first line, modify the first line (changing spaces to underscores, so that the header is the database field header), then have it rewrite the file and import the new csv file into the database. Works perfect. But I realized a lot of my records that I am going to be importing will need updating. I'd rather not have multiple entries of the same "WORK_ORDER_NUMBER" in the database. But enough with explanation, here is the code and notes I have now.

<?PHP
require('includes/init.php');
include("classes/csv_upload.php");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="html/text; charset=iso-8859-1" />
<title><?php echo($title_main); ?></title>

</head>

<body>

<?PHP
include('includes/header.php');

$csv = new Quick_CSV_import();

if (isset($_POST['submitted'])) {
        // This is where it uploads the file.
$target = "csv_storage/";
$target = $target . basename($_FILES['file_source']['name']) ;
$ok=1;
if(move_uploaded_file($_FILES['file_source']['tmp_name'], $target)) {
	echo "The file ". basename($_FILES['file_source']['name']). " has been uploaded<br /><br />";
} else {
	echo "Sorry, there was a problem uploading your file.";
}

// 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]);
$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 = "c:\\wamp\\www\\csvtophp\\csv_storage\\" . $_FILES['file_source']['name'];
$csv->import();
}

?>
<form enctype="multipart/form-data" action="importer.php" method="post">
<fieldset>
	<legend>Test CSV File Upload</legend>
	Select CSV File to Upload
	<input type="file" name="file_source" />
	<br />
	<input type="hidden" name="submitted" value="TRUE" />
	<input type="submit" name="submit" value="Import" />
</fieldset>
</form>

</body>
</html>

here is the class file

<?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 = "csv_data";
    
    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;
  }
  

}
?>

NerdConcepts · June 5, 2007

Before I write the code to do this...I was thinking. Would a temporary database work? I mean put all the imported information into a temp database, then use more PHP code to reference the temp table with the permanent one then delete the temporary one? Before I write the code can I get some feedback on my idea...thanks a bunch.

Sign In

CSV Data Placement and Updating issue

Recommended Posts

NerdConcepts

Link to comment

Share on other sites

NerdConcepts

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information