code or csv file not allowing upload

问题:

either a issue with the code or csv is causing trouble for the import. Anything that could be a problem with the code, or with the csv?

When i run the script i get a default messeage of : CSV File not readable

This works for 5 out of the 15 files and cannot see an obvious issue or solution.

http://www.dodgejeffgen.org/gs/issue1.csv

<?php  

ini_set('auto_detect_line_endings',TRUE);
ini_set('post_max_size', '128M');
ini_set('upload_max_filesize', '128M');

function clean($link, $str, $default ='') {
    if (!isset($str)) $str = $default;
    $str = @trim($str);
    if(get_magic_quotes_gpc()) {
        $str = stripslashes($str);
    }
    return mysqli_real_escape_string($link, $str);
}

$host_name  = "some host";
$database   = "some db";
$user_name  = "some user";
$password   = "some pass";

$connect = mysqli_connect($host_name, $user_name, $password, $database);
if (mysqli_connect_errno())
{ echo "Failed to connect to MySQL: " . mysqli_connect_error(); } else {}                       

$lines = $value1 = $value2 = $data = 0;

if ($_FILES["csv"]["size"] > 0) { 

    //get the csv file 
    $file = $_FILES["csv"]["tmp_name"]; 
    $handle = fopen($file,"r"); 

    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        //print_r($data).'<br />';
        if ($data[0]) {
            $sql = "INSERT INTO `articles` (`name`, `reference`) VALUES ('".clean($connect, $data[0])."','".clean($connect, $data[1])."')";
            //echo $sql.'<br />';
            if (mysqli_query($connect, $sql)) { } else { echo "Error: " . $sql . "<br>" . mysqli_error($connect); }
           $lines++;
       }
    }
    echo "<b>Your file has been imported.</b><br>";
    echo "Found a total of ".$lines." records in this csv file.<br />"; 
} else { echo 'CSV File not readable.<br />';}

?>
<form action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="post" enctype="multipart/form-data" name="form1" id="form1"> 
  Choose your file: <br /> 
  <input name="csv" type="file" id="csv" /> 
  <input type="submit" name="Submit" value="Submit" /> 
</form>

回答1:

I would recommend using LOAD DATA LOCAL INFILE. Just upload your csv file to some location on your server and run the following mysql query

LOAD DATA LOCAL INFILE '/path/to/csv_file.csv' INTO TABLE articles 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'  
(@col1,@col2) set `name`=TRIM(@col1),`reference`=TRIM(@col2)

回答2:

It might be better to use LOAD DATA LOCAL INFILE like Indra Kumar S reccomends. You may be able to load it into a temporary table (CREATE TEMPORARY TABLE ...), then use mysql queries to distribute that data to whatever permanent tables you store the data in.

Remember in addition to FIELDS TERMINATED BY and LINES TERMINATED BY there is also FIELDS ESCAPED BY, plus other options. See here: http://dev.mysql.com/doc/refman/5.7/en/load-data.html

The cool thing about loading into MySQL first is you that you can use MySQL, sometimes with PHP's help, to do a lot of data cleaning. MySQL should do a pretty good job of trimming edges, but you might prefer selecting out to PHP and then writing back into MySQL for more complex cleaning, like removing non-breaking spaces, extra spaces inside of strings, or non-ASCII characters.

When I did something like this, I think I did one run through the CSV to determine data types for each column (varchar, int, decimal, text, datetime), and then created the temporary table with matching datatypes, and the did LOAD DATA INFILE to that table.