0

My CSV file has got some additional commas within a particular column which is causing the problem. When I import it into MySQL, the data in that particular column is split into 2 columns. Could you please help me to fix this? Thanks.

NOTE 1: I'm not allowed to use Load Data Local Infile on my server so this is not an option to be considered.

NOTE 2: I'm not well versed in PHP so I would appreciate it if you would show the the correct code.

NOTE 3. The additional commas are in the $Club_Name column. However there is a possibility that additional commas could also appear in other columns in the future so I'm looking for a complete solution to clean up all irrelevant commas in all columns.

My code (after days of struggle):

<?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);
}
?>
webportal
  • 53
  • 5
  • you've tagged fgetcsv but you aren't actually using that function at all...using it would probably help. You're naively splitting each line with by commas without considering if they're actually delimiters of fields, or part of the data. You haven't shown us any of the problematic source data, but in a valid CSV file, I'd expect that that if a field contains any special characters like that, that the field would be enclosed in double-quotes. Is that the case for you? Anyway using a specialised function like fgetcsv should help take care of those details, you can find tutorials about it online – ADyson Oct 30 '18 at 12:23
  • Not a very helpful answer. – webportal Oct 31 '18 at 09:46
  • It's not an answer, its just a comment - and intentionally so :-). You haven't shown us your problematic data, so the best I can do is make some assumptions and suggestions. I've explained what the problem is likely to be, but I can't be 100% certain what the solution is, because I can't reproduce your issue without data. But I can be pretty sure that using the actual CSV library you've (erroneously) tagged your question with is likely to make your life easier. You can already find tutorials and documentation related to it online, I'm not sure you really need anyone to write another one here. – ADyson Oct 31 '18 at 09:53
  • Anyway, realistically this question is probably a duplicate of [How to extract data from csv file in PHP](https://stackoverflow.com/questions/2805427/how-to-extract-data-from-csv-file-in-php) – ADyson Oct 31 '18 at 09:57
  • This link you provided was useful. Thanks. I appreciate it. https://stackoverflow.com/questions/2805427/how-to-extract-data-from-csv-file-in-php My solution will come shortly for the benefit of others with the same dilemma. – webportal Oct 31 '18 at 12:52

1 Answers1

0

Basically these are the lines that I edited. The rest are the same. Hope it helps someone. 

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;
webportal
  • 53
  • 5