php 读取 CSV 文件并存储到 MySQL 数据库中

Read CSV file and store into MySQL Database

提问by Lloydworth

I'm developing a website that allows user to browse and select a .csv file from their computer.
The website will then read the .csv file (items separated by commas) and store all items into maybe a list or an array? After that it will be store into my Database.

我正在开发一个网站，允许用户从他们的计算机浏览和选择 .csv 文件。
然后网站将读取 .csv 文件（项目以逗号分隔）并将所有项目存储到列表或数组中？之后它将存储到我的数据库中。

May i know how can this be done? Code snippets and references will be much appreciated.

我可以知道怎么做吗？代码片段和参考将不胜感激。

Thanks in advance!

提前致谢！

回答by Aravind

Hope you are using PHP to develop your website and want to import user CSV file into your MySQL Server database.

希望您正在使用 PHP 开发您的网站，并希望将用户 CSV 文件导入您的 MySQL 服务器数据库。

You can use the following PHP class to import your data: http://www.legend.ws/blog/tips-tricks/csv-php-mysql-import/

您可以使用以下 PHP 类来导入您的数据：http: //www.legend.ws/blog/tips-tricks/csv-php-mysql-import/

Assuming that you have already created the required database tables.

假设您已经创建了所需的数据库表。

Alternately, you can use the MySQLImport utility to import any CSV file into your MySQL database. Refer http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.htmlURL to get more details on mySQLImport command.

或者，您可以使用 MySQLImport 实用程序将任何 CSV 文件导入您的 MySQL 数据库。请参阅http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.htmlURL 以获取有关 mySQLImport 命令的更多详细信息。

回答by Geoffrey

see fgetcsv - http://php.net/manual/en/function.fgetcsv.php, has a good example there also, but I wouldn't store in an array first though, put them straight into the database so that you save ram and an extra loop.

请参阅 fgetcsv - http://php.net/manual/en/function.fgetcsv.php，那里也有一个很好的例子，但我不会先存储在数组中，将它们直接放入数据库中以便保存ram 和一个额外的循环。

回答by Geoffrey

This will upload a CSV file and import the data into the specified table.

这将上传一个 CSV 文件并将数据导入到指定的表中。

//Import the contents of a CSV file after uploading it
//http://www.bin-co.com/php/scripts/csv_import_export/
//Aruguments : $table - The name of the table the data must be imported to
//                $fields - An array of fields that will be used
//                $csv_fieldname - The name of the CSV file field
function CSVImport($table, $fields, $csv_fieldname='csv') {
    if(!$_FILES[$csv_fieldname]['name']) return;

    $handle = fopen($_FILES[$csv_fieldname]['tmp_name'],'r');
    if(!$handle) die('Cannot open uploaded file.');

    $row_count = 0;
    $sql_query = "INSERT INTO $table(". implode(',',$fields) .") VALUES(";

    $rows = array();

    //Read the file as csv
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $row_count++;
        foreach($data as $key=>$value) {
            $data[$key] = "'" . addslashes($value) . "'";
        }
        $rows[] = implode(",",$data);
    }
    $sql_query .= implode("),(", $rows);
    $sql_query .= ")";
    fclose($handle);

    if(count($rows)) { //If some recores  were found,
        //Replace these line with what is appropriate for your DB abstraction layer
        mysql_query("TRUNCATE TABLE $table") or die("MySQL Error: " . mysql_error()); //Delete the existing records
        mysql_query($sql_query) or die("MySQL Error: " . mysql_error()); // and insert the new ones.

        print 'Successfully imported '.$row_count.' record(s)';
    } else {
        print 'Cannot import data - no records found.';
    }
} 

for more details refer this link : http://www.bin-co.com/php/scripts/csv_import_export/

有关更多详细信息，请参阅此链接：http: //www.bin-co.com/php/scripts/csv_import_export/

回答by Olimjon Akhmedov

CSV. From MySQL to 'csv'. From 'csv' to MySQL This script converts table data to 'csv' file. You can recreate table from 'csv' file in another computer.

CSV。从 MySQL 到 'csv'。从 'csv' 到 MySQL 此脚本将表数据转换为 'csv' 文件。您可以在另一台计算机上从 'csv' 文件重新创建表。

<?php
// connect to database
require_once '../db_config.php';
$conn = new mysqli ( $dbhost, $dbuser, $dbpass, 'olimrefdb' );
if ($conn->connect_errno) die ( 'no db connection' );   

$tableToConvert = 'mdata';
$csvFileName = $tableToConvert . '_' . date ( "Ymd" );//will contain 'csv' data 

// -----------------------------MySQL table to CSV--------------------------
$tableSchema = $conn->query ( "SHOW CREATE TABLE $tableToConvert" )->fetch_assoc ();
$result = $conn->query ( "SELECT * FROM $tableToConvert" );

$handle = fopen ( $csvFileName.'.csv', 'w' );//to store 'csv' data file
$handle_schema = fopen ( $csvFileName.'_schema.csv', 'w' );//to store table schema 

fputcsv ( $handle_schema, $tableSchema );
while ( $row = $result->fetch_assoc () ) {
    fputcsv ( $handle, $row );
}
fclose ( $handle );
fclose ( $handle_schema );

// -----------------------------CSV to MySQL table--------------------------

$handle = fopen ( $csvFileName.'.csv', 'r' );//open 'csv' data file
$handle_schema = fopen ( $csvFileName.'_schema.csv', 'r' );//open table schema

$tableSchema = fgetcsv ( $handle_schema );
$newTableName = "$tableSchema[0]_" . date ( "Ymd" );

// change table name in schema
$tableSchema [1] = preg_replace ( "/$tableSchema[0]/", $newTableName, $tableSchema [1], 1 );

$conn->query ( "DROP TABLE IF EXISTS $newTableName" );
$conn->query ( $tableSchema [1] );//CREATE TABLE 
//  $conn->query ( "CREATE TABLE $newTableName LIKE $tableToConvert");//the same, but without using schema

while ( $row = fgetcsv ( $handle ) ) {
    $data = implode ( "','", $row ); //convert array to string

    if ($conn->query ( "INSERT INTO $newTableName values('$data')" )) {
    } else {
        echo '<span style="color:red">Problem:</span> ' . $data . '<br>';
        var_dump ( $conn->error_list );
        fclose ( $handle );
        exit ();
    }
}

fclose ( $handle ); 
echo 'Congratulation!!!' . '<br>';