Re: Text from Excel csv file loaded into MySQL table

Chris Boget wrote:

Can anyone point me to a really good end to end tutorial on extracting text from an Excel csv file and uploading it into MySQL via a PHP script?

Actually, depending on the integrity of the data and the consistency of the format in the CSV file, you can do this simply and easily using just MySQL. Take a look at the following pages: Thanks for that. As there will be a team of people creating the data and loading it I need to do some validation etc before it goes into the database.

Currently I have this and it looks like it might work:- # Open file $fptr = fopen($filename, "r"); # Check if file is open if($fptr) { $current_line = fgets($fptr,4096); $retval = TRUE;

       echo "open";
       while($current_line && $retval)

{ $mystring=csv_string_to_array($current_line); $query = "insert into invw2wfinal ( FIELD1, FIELD2, FIELD3, FIELD4

          )

values ( '". mysql_real_escape_string($mystring[0]) ."', '". mysql_real_escape_string($mystring[1]) ."', '". mysql_real_escape_string($mystring[2]) ."', '". mysql_real_escape_string($mystring[3]) ."' )";

     $result = mysql_query($query);
                                                                                                        if(!$result)
           {
                echo "<h1>Processing halted due to Error No:";
               echo mysql_errno().": ";
               echo mysql_error()."<BR>";
               echo "</h1>";
               $retval = FALSE;
               die;
           }
           elseif(mysql_affected_rows() == 0)
       {
         $retval = FALSE;
               die;
       }
     set_time_limit(0);
     $current_line = fgets($fptr,4096);

}

}     

fclose($fptr); function csv_string_to_array($str){ $expr="/,(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))/"; $results=preg_split($expr,trim($str)); return preg_replace("/^\"(.*)\"$/","$1",$results); } Alan