[SOLVED] CSV Importing to MySQL

[NerdConcepts]

Well I had the last script working perfectly. Except sometimes we rearrange the CSV "headers" so a field may be in one place on one file and another place on another file, yet they are the same thing. I've tired about 30 different scripts and none of them work at all. For example:

 

Csv file looks like:

work order,client name,start date,end date

7283728,"name,client",02/07/07,04/02/07

 

that is just the test file I've made up. I found out, the hard way, you cannot have spaces in your database fields. So what I am trying to do is take that first line, replace all spaces with an _. That away database fields can be like work_order and in the CSV file also. I tried using

 

	$fcontents = fopen($_FILES['csvUpload']['tmp_name'], "r");

for($i=0; $i<sizeof($fcontents); $i++) { 
	$line = trim($fcontents[$i], ',');
	$arr = explode(",", $line); 
     	
	$sql = "insert into test values ('". implode("','", $arr) ."')"; 
	mysql_query($sql);
	echo $sql ."<br \>\n";
	if(mysql_error()) {
		echo mysql_error() ."<br \>\n";
	} 
}

 

But that doesn't seem to read at all. I created a "test" table that the fields were c1,c2,c3 and the csv file was:

 

c1,c2,c3

test c1,test c2,test c3

 

Just to see if the script would atleast read the first line and put the values in the right table fields, yeah right. Says row counts don't match. I looked for a book that did anything close to this, wouldn't mind spending 30 dollars on book if it would help, but nothing. Also checked the forum, that is where I got the script above; then there was nothing else. I've tired using piece of other scripts but nothing.

 

If someone could point me to a direction or maybe someone has the code out there to actually grab the first line, change it and then process all the lines after it, no idea. I am sure it's no on the forums here or anywhere else neither. Thanks for any help.

[Barand]

Have you looked at mysql LOAD DATA INFILE ?

[NerdConcepts]

I've looked at it is, not sure how to read and change that first line of ever CSV file that is read. I do have a feeling that I am going to have to have the file uploaded, changed and then deleted using PHP. I was trying to do it with just reading the file, but I don't think I can change it w/o uploading it. And then I still don't know how to change that first line.

[NerdConcepts]

Ok, well I've tried uploading the file and then using that to do the LOAD INFILE stuff. Here is my code.

 

<?PHP
require('includes/init.php');

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

$fcontents = $_FILES['uploaded']['name'];

$query = "LOAD DATA LOCAL INFILE '". $fcontents . "' INTO TABLE test FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'";
$result = mysql_query($query);
}

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

 

It uploads the file perfectly, but the LOAD INFILE query does nothing at all. I've done some reading on how to us it and what not and cannot figure out how to get it to read the first line, which determines which row (in the CSV file) goes to which field (in MySQL). Then again, I still would have to figure out how to change the first line after uploading.

[NerdConcepts]

Ok, got the basics working. Another programmer I know found a tutorial on how-to build a class that does CSV importing to MySQL. I've stripped it down to exactly what I need it to do. Only 1 problem. Yes, just one . I need it to grab the first line, change spaces to _. That away if the header in the CSV file is "WORK ORDER" it will change it to "WORK_ORDER" that away it'll be pointing to the right MySQL field. To bad MySQL doesn't support spaces Here is the code for everything I am doing. Also, if I need to upload the file before modifying it, that is fine, just need to know how to modify that first line using PHP.

 

csv_upload.php (class file, just in case I need to call to it again)

<?PHP

date_default_timezone_set('America/Chicago');

class Quick_CSV_import
{
  var $table_name; //where to import to
  var $file_name;  //where to import from
  var $use_csv_header; //use first line of file OR generated columns names
  var $field_separate_char; //character to separate fields
  var $field_enclose_char; //character to enclose fields, which contain separator char into content
  var $field_escape_char;  //char to escape special symbols
  var $error; //error message
  var $arr_csv_columns; //array of columns
  var $table_exists; //flag: does table for import exist
  var $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 = "test";
    
    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);
      $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;
  }
  

}
?>

 

Here is the importer.php file

<!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
require('includes/init.php');
include("classes/csv_upload.php");

$csv = new Quick_CSV_import();

if (isset($_POST['submitted'])) {
  $csv->file_name = $HTTP_POST_FILES['file_source']['tmp_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>

 

This code works perfectly...test CSV looks like this

c1,c3,c2

c1text,"first,last",c2text

 

while the fields are listed in c1,c2,c3; it goes ahead and puts it in the right order, which is what I want it to do. Problem is listed at the very top. I've tried a few things but cannot seem to get it to work.