将包含额外逗号的csv导入mysql

我的csv文件在一个特定的列中有一些额外的逗号导致了这个问题。当我将其导入mysql时，该特定列中的数据被拆分为两列。你能帮我修一下吗？谢谢。
注1：我不允许在我的服务器上使用加载数据本地填充，所以这不是一个可以考虑的选项。
注2：我并不精通php，因此如果您能展示正确的代码，我将不胜感激。
附注3。额外的逗号在$club\u name列中。但是，将来在其他列中也可能出现其他逗号，因此我正在寻找一个完整的解决方案来清除所有列中所有不相关的逗号。
我的密码（经过几天的挣扎）：

<?php 
include("/path/to/the/database/connection/db_connect.php");

$filename = fopen("/path/to/the/csv/file/clubperformance_District_102.csv", "r");
//open the csv file
if ($filename) {
$firstline = fgets($filename, 4096);
    while (($line = fgets($filename)) !== false) {
         $lineArr = explode(",", "$line");
         var_dump($lineArr); // to display the array
         $lineArr = preg_replace("/[^a-zA-Z0-9 ]/", "", $lineArr); //clean up everything except alpha numeric characters & spaces

// list all the variables representing the database columns            
list($District, $Division, $Area, $Club_Number, $Club_Name, $Club_Status, $Mem_Base, $Active_Members, $Goals_Met, $CCs, $Add_CCs, $ACs, $Add_ACs, $CL_AL_DTMs, $Add_CL_AL_DTMs, $Level_1, $Level_2, $Add_Level_2, $Level_3, $Level_4, $Level_5, $New_Members, $Add_New_Members, $Off_Trained_Round_1, $Off_Trained_Round_2, $Mem_dues_on_time_Oct, $Mem_dues_on_time_Apr, $Off_List_On_Time, $Club_Distinguished_Status, $Last_Updated) = $lineArr;

// and then insert data
mysqli_query($GLOBALS["___mysqli_ston"], "INSERT INTO `toastmaster_dcp_tracking_old`(`District`, `Division`, `Area`, `Club_Number`, `Club_Name`, `Club_Status`, `Mem_Base`, `Active_Members`, `Goals_Met`, `CCs`, `Add_CCs`, `ACs`, `Add_ACs`, `CL_AL_DTMs`, `Add_CL_AL_DTMs`, `Level_1`, `Level_2`, `Add_Level 2`, `Level_3`, `Level_4`, `Level_5`, `New_Members`, `Add_New_Members`, `Off_Trained_Round_1`, `Off_Trained_Round_2`, `Mem_dues_on_time_Oct`, `Mem_dues_on_time_Apr`, `Off_List_On_Time`, `Club_Distinguished_Status`, `Last_Updated`) VALUES ('$District', '$Division', '$Area', '$Club_Number', '$Club_Name', '$Club_Status', '$Mem_Base', '$Active_Members', '$Goals_Met', '$CCs', '$Add_CCs', '$ACs', '$Add_ACs', '$CL_AL_DTMs', '$Add_CL_AL_DTMs', '$Level_1', '$Level_2', '$Add_Level_2', '$Level_3', '$Level_4', '$Level_5', '$New_Members', '$Add_New_Members', '$Off_Trained_Round_1', '$Off_Trained_Round_2', '$Mem_dues_on_time_Oct', '$Mem_dues_on_time_Apr', '$Off_List_On_Time', '$Club_Distinguished_Status', '$Last_Updated')")or die(((is_object($GLOBALS["___mysqli_ston"])) ? mysqli_error($GLOBALS["___mysqli_ston"]) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false)));

    }

    fclose($filename);
}
?>

基本上这些是我编辑的台词。其余的都一样。希望它能帮助别人。

if ($filename) {
    $firstline = fgetcsv($filename, 1000);
        while (($line = fgetcsv($filename)) !== false) {
        var_dump($line); // to display the array
             $line = preg_replace("/[^a-zA-Z0-9 ]/", "", $line); //clean up the mess, if any

    // list all the variables representing the database columns          
    list($District, $Division, $Area, $Club_Number, $Club_Name, $Club_Status, $Mem_Base, $Active_Members, $Goals_Met, $CCs, $Add_CCs, $ACs, $Add_ACs, $CL_AL_DTMs, $Add_CL_AL_DTMs, $Level_1, $Level_2, $Add_Level_2, $Level_3, $Level_4, $Level_5, $New_Members, $Add_New_Members, $Off_Trained_Round_1, $Off_Trained_Round_2, $Mem_dues_on_time_Oct, $Mem_dues_on_time_Apr, $Off_List_On_Time, $Club_Distinguished_Status, $Last_Updated) = $line;

将包含额外逗号的csv导入mysql

1条答案

相关问题

热门标签

最新问答