MySQL - 將CSV匯入資料庫

要透過 PHP 程式將 CSV 檔案匯入資料庫，我們需要使用 mysqli 函式 query() 執行“LOAD DATA INFILE”語句，如下所示 -

$sql = "LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv' INTO TABLE CUSTOMERS FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n'";
$mysqli->query($sql);

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.
');
$sql = "CREATE TABLE EMPLOYEES( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID) )";
if($mysqli->query($sql)){
    printf("Employees table created successfully...!\n");
}
//now lets insert some data into it..
$sql = "INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 
32, 'Ahmedabad', 2000.00 )";
if($mysqli->query($sql)){
    printf("First record inserted successfully...!\n");
}
$sql = "INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 
25, 'Delhi', 1500.00 )";
 if($mysqli->query($sql)){
    printf("Second record inserted successfully...!\n");
}
$sql = "INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 
23, 'Kota', 2000.00 )";
if($mysqli->query($sql)){
    printf("Third record inserted successfully...!\n");
}
//display table record
$sql = "SELECT * FROM EMPLOYEES";
if($result = $mysqli->query($sql)){
    printf("Table records: \n");
    while($row = mysqli_fetch_array($result)){
        printf("ID: %d, NAME: %s, AGE: %d, ADDRESS: %s, SALARY: %f", 
            $row['ID'],
            $row['NAME'],
            $row['AGE'],
            $row['ADDRESS'],
            $row['SALARY']);
            printf("\n");
    }
}
//now let's export the table data into csv file
$sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM EMPLOYEES INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv' FIELDS ENCLOSED BY '' TERMINATED BY ';' ESCAPED BY '' LINES TERMINATED BY '\r\n'";
if($result = $mysqli->query($sql)){
   printf("Table data exported successfully....!\n");
   print_r($result , "\n");
}
//now let's create a customers table
$sql = "CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID) )";
if($mysqli->query($sql)){
    printf("Customers table created successfully...!\n");
}
//now lets import employees table data csv file into customers table
$sql = "LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv' INTO TABLE CUSTOMERS FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n'";
if($mysqli->query($sql)){
    printf("CSV file data imported successfully into Customers table....!\n");
}
//print customers table records
$sql = "SELECT * FROM CUSTOMERS";
if($result = $mysqli->query($sql)){
    printf("Customers table records after importing csv file data into it: \n");
    while($row = mysqli_fetch_array($result)){
        printf("ID: %d, NAME: %s, AGE: %d, ADDRESS: %s, SALARY: %f", 
            $row['ID'],
            $row['NAME'],
            $row['AGE'],
            $row['ADDRESS'],
            $row['SALARY']);
            printf("\n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

輸出

獲得的輸出如下所示 -

Employees table created successfully...!
First record inserted successfully...!
Second record inserted successfully...!
Third record inserted successfully...!
Table records:
ID: 1, NAME: Ramesh, AGE: 32, ADDRESS: Ahmedabad, SALARY: 2000.000000
ID: 2, NAME: Khilan, AGE: 25, ADDRESS: Delhi, SALARY: 1500.000000
ID: 3, NAME: kaushik, AGE: 23, ADDRESS: Kota, SALARY: 2000.000000
Table data exported successfully....!
Customers table created successfully...!
CSV file data imported successfully into Customers table....!
Customers table records after importing csv file data into it:
ID: 1, NAME: Ramesh, AGE: 32, ADDRESS: Ahmedabad, SALARY: 2000.000000
ID: 2, NAME: Khilan, AGE: 25, ADDRESS: Delhi, SALARY: 1500.000000
ID: 3, NAME: kaushik, AGE: 23, ADDRESS: Kota, SALARY: 2000.000000