将逗号分隔的数组项展开为单独的数组项

Question

I have a CSV file, which I'm parsing into an associative array, which I need to save in the DB. The issue is that some of my CSV columns contain other CSV values, which I will need to insert separately into the DB. Examples talk better, so here goes:

ID, Col1, Col2
1, "Example 1.1.1,Example 1.1.2", Example 1.2
2, Example 2.1, "Example 2.2.1, Example 2.2.2"

As you can see, for row 1, there are 2 values in the first column, and for row 2, there are 2 values in the 2nd column.

I somehow need to get these to expand to 4 separate array items, which look like:

array(
    [0] => array( "ID" => 1, "Col1" => "Example 1.1.1", "Col2" => "Example 1.2" ),
    [1] => array( "ID" => 1, "Col1" => "Example 1.1.2", "Col2" => "Example 1.2" ),
    [2] => array( "ID" => 2, "Col1" => "Example 2.1", "Col2" => "Example 2.2.1" )
    [3] => array( "ID" => 2, "Col1" => "Example 2.1", "Col2" => "Example 2.2.2" )
)

Is there a function in PHP that does that, or do I have to loop through the entire thing?

Answer 1

The approach:

• read every line of the CSV file on its own
• save the headers array from the first line of the CSV file
• for the other lines, check if there's a text within double quotes.
• save this text in an array and remove this text from the original line string.
• extract the data from the original line and fill an array and append the array to the "master array" if there's no duplicates.
• HERE'S YOUR PROBLEM: in case there's duplicates, handle them by looping over the array of the results found in the double quotes and adding each result on its own.

This code should work well with the data you presented, but it might not work with the following case:

ID, Col1, Col2
1, "Example 1.1.1,Example 1.1.2", "Example 1.2"
2, Example 2.1, "Example 2.2.1, Example 2.2.2"

Having more than on CSV string in a line might break it. I didn't have much time to test all the possibilities.

Here's the code:

$file = @fopen("file.csv", "r");
$major_arr = array();
$headers_array = array();
$iterator = 0;
$total_cols = 0;
if(!empty($file)){
    while(! feof($file)){
        $major_line_arr = array();
        $line_str = fgetcsv($file);
        if($iterator == 0){
            // First Line
            // Read headers
            $headers_array = explode(",", $line_str);
            // Trim the whitespace in the array
            $headers_array = array_map('trim', $headers_array);
            $total_cols = sizeof($headers_array);
        } else {
            // Search for text within double quotes
            $text_btw_double_quotes_arr = array();
            if(preg_match('/"([^"]+)"/', $line_str, $m)){
                array_push($text_btw_double_quotes_arr, $m[1]);   
            }
            // Remove this text from the original line
            $new_line_str = $line_str;
            if(!empty($text_btw_double_quotes_arr)){
                for($i=0; $i<sizeof($text_btw_double_quotes_arr); $i++){
                    $new_line_str = str_replace($text_btw_double_quotes_arr[$i], "", $new_line_str);
                    $major_line_arr = array();
                    $double_quoted_term = $text_btw_double_quotes_arr[$i];
                    $major_line_arr_tmp = array();
                    for($j=0; $j<$total_cols; $j++){
                        array_push($major_line_arr_tmp, $csv_columns_arr[$j]);
                    }
                    // Build the Major Line array
                    if(!empty($headers_array)){
                        for($j=0; $j<sizeof($headers_array); $j++){
                            $header_title = $headers_array[$j];
                            $value = $major_line_arr_tmp[$j];
                            if($value == ''){
                                $value = $double_quoted_term;
                            }
                            $new_arr = array($header_title => $value);
                            array_push($major_line_arr, $new_arr);
                        }
                    }
                    // Add the Major Line to the Major array
                    array_push($major_arr, $major_line_arr);
                }
            } else {
                $major_line_arr_tmp = array();
                for($i=0; $i<$total_cols; $i++){
                    array_push($major_line_arr_tmp, $csv_columns_arr[$i]);
                }
                // Build the Major Line array
                if(!empty($headers_array)){
                    for($i=0; $i<sizeof($headers_array); $i++){
                        $header_title = $headers_array[$i];
                        $value = $major_line_arr_tmp[$i];
                        $new_arr = array($header_title => $value);
                        array_push($major_line_arr, $new_arr);
                    }
                }
                // Add the Major Line to the Major array
                array_push($major_arr, $major_line_arr);
            }
        }
        $iterator++;
    }
    fclose($file);
}