用csv文件创建表到数据库mysql

Question

I'm trying to create a table using my csv file with fields and I also have column headers inside my csv file. However, when I try to run it.. it just shows and gives me a query... I'm trying to find out what seems to be the problem and I'm stuck with it... can you help me on this? Thank

Here's my code

<?php

 $server = "localhost";
      $username = "root";
      $pass = "";
      $dbname = "test";


      $conn = new PDO("mysql:host=$server;dbname=$dbname", $username, 
$pass);
      $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


// Parameters: filename.csv table_name

$file = 'C:\Users\HP\Desktop\ACC.DBF.csv';
$table = 'acc';

// get structure from csv and insert db
ini_set('auto_detect_line_endings',TRUE);
$handle = fopen($file,'r');
// first row, structure
 if ( ($data = fgetcsv($handle) ) === FALSE ) {
echo "Cannot read from csv $file";die();
}
$fields = array();
$field_count = 0;
for($i=0;$i<count($data); $i++) {
$f = strtolower(trim($data[$i]));
if ($f) {
    // normalize the field name, strip to 20 chars if too long
    $f = substr(preg_replace ('/[^0-9a-z]/', '_', $f), 0, 20);
    $field_count++;
    $fields[] = $f.' VARCHAR(255)';
}
}

$sqlcreate = $conn->prepare("CREATE TABLE $table (" . implode(', ', $fields) . ')');
$sqlcreate->execute();

echo "Create Table success" . "<br /><br />";
//$db->query($sql);
while ( ($data = fgetcsv($handle) ) !== FALSE ) {
$fields = array();
for($i=0;$i<$field_count; $i++) {
    $fields[] = '\''.addslashes($data[$i]).'\'';
}
 $sqlinsert = $conn->prepare("Insert into $table values(" . implode(', ', 
 $fields) . ')');
 $sqlinsert->execute();
echo "Insert Table success" ; 

}
fclose($handle);
ini_set('auto_detect_line_endings',FALSE);



?>

Answer 1

I have created a utility script which does the same thing that you are trying.. Please check if it helps you.

<?php

$fileName = './WP.csv';


function connectDB()
{
    $server = "mysql2345";
    $username = "root";
    $pass = "root";
    $dbname = "sc1";
    $conn = new PDO("mysql:host=$server;dbname=$dbname", $username, $pass);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $conn;
}

function createDb($csv_path, $db)
{

    if (($csv_handle = fopen($csv_path, "r")) === false) {
        throw new Exception('Cannot open CSV file');
    }

    if(!isset($delimiter)) {
        $delimiter = ',';
    }

    if (!isset($table)) {
        $table = preg_replace("/[^A-Z0-9]/i", '', basename($csv_path));
    }

    if (!isset($fields)) {
        $fields = array_map(function ($field){
            return $field;
        }, fgetcsv($csv_handle, 0, $delimiter));
    }

    $create_fields_str = join(', ', array_map(function ($field){
        return "$field VARCHAR(200) NULL";
    }, $fields));

    echo $create_table_sql = "CREATE TABLE IF NOT EXISTS $table ($create_fields_str)";


    $db->query($create_table_sql);

    return ['table'=>$table, 'fields'=>$fields];

}

function loadData($fileName, $tableName, $fields, $db)
{
    $fieldStr = implode(',', $fields);

    $query = <<<eof
    LOAD DATA LOCAL INFILE '$fileName'
     INTO TABLE $tableName
     FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
     LINES TERMINATED BY ''
    ($fieldStr)
eof;

    echo $query;

    $db->query($query);

}

$db = connectDB();

$tableInfo = createDb($fileName, $db);

loadData($fileName, $tableInfo['table'], $tableInfo['fields'], $db);