将CSV文件直接导入MySQL

您可以创建一个脚本来解析csv文件并将数据放入db。

类似于:

    $path = "yourfile.csv";
    $row = 1;
    if (($handle = fopen($path, "r")) !== FALSE) {
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            $row++;
            $data_entries[] = $data ;

        }
        fclose($handle);
    }
    // this you'll have to expand
    foreach($data_entries as $line){
        $sql = "INSERT INTO ..."
        $db->execute($line);
    }

但是csv中的列名和数据库表中的列名不同,我应该怎么做?

LOAD DATA INFILE syntax

默认情况下,如果在LOAD DATA INFILE语句的末尾未提供列列表,则输入行应包含每个表列的字段。如果只想加载表的某些列,请指定列列表:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

当我发现INFILE语法太复杂时,我喜欢做的是使用类似于 HeidiSQL

如果只想将CSV文件最后一列的前7个字符加载到表的uniqComments列中,则可以执行以下操作。。。

load data local infile 'uniq.csv' into table tblUniq fields terminated by ',' 
enclosed by '"' lines terminated by '\n' (uniqName, uniqCity, @seven_chars) 
set uniqComments=left(@seven_chars,7)

试试这个,这个对我很有用。

ini_set('auto_detect_line_endings',TRUE);

$csv_data=array();

$file_handle = fopen($_FILES['file_name']['tmp_name'], 'r');

while(($data = fgetcsv($file_handle) ) !== FALSE){

 $update_data= array('first'=>$data['0'],
                      'second'=>$data['1'],
                      'third'=>$data['2'],
                       'fourth'=>$data['34']);

// save this array in your database
}

LOAD DATA INFILE 'c:/xampp/example.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Import CSV file into MySQL