How to convert few columns as JSON array in php while csv import

Question

i have a csv file, please see this below image of my CSV

and this is my table

i need a help to split few columns and convert as an array and insert in to table, lets say i want to exclude the first column which is 'product_id' from the array and remaining all columns as array and insert in to table, so the expected query will be like insert into process_detail (product_id,product_attributes) values ($product_id,'[{"color":"blue","style":"classic","material_type":"etc","length":"35 cm","price":"11.25","product_description":"etc"}]'; note: my csv headers which are in the first row in CSV file will match exactly with Mysql table headers

this is my php

 function convert($string)
{
    return htmlspecialchars($string,ENT_QUOTES);
}

$columnArray    = array();
$dataArray      = array();
$firstRule      = true;

while ($data = fgetcsv ($source, 1000, ","))
{
    if($firstRule)
    {
        foreach($data as $columnName)
        {
            $columnArray[] = $columnName;
        }

        $firstRule = false;
    }
    else
    {
        $rule = array();
        for($i = 0; $i < count($data) ; $i++)
        {
            $rule[$columnArray[$i]] = $data[$i];
        }
        $dataArray[] = $rule;
    }
}

     foreach($dataArray as $data)
        {
            // here i am stuck, i would want to json_encode of few columns and insert into table
        }

echo $product_id; var_dump($bound_values);

mlarray(2) { ["product_id"]=> string(2) "ml" ["product_attributes"]=> string(1588) "{"cv_id":"weight_2_kg","sv_id":"label_3","collection_id":"length_3_cm","season":"width_3_cm","hit":"height_3_cm","tier":"diameter_3_cm","style_no":"size_3_ml","base_product_id":"weight_3_kg","product_title":"label_4","introduction":"length_4_cm","color_name":"width_4_cm","price_a":"height_4_cm","color_price_a":"diameter_4_cm","sku_price_a":"size_4_ml","price_b":"weight_4_kg","color_price_b":"label_5","price_c":"length_5_cm","color_price_c":"width_5_cm","sku_price_c":"height_5_cm","product_availability_b":"diameter_5_cm","product_availability_c":"size_5_ml","product_availability_d":"weight_5_kg","returnable_a":"label_6","returnable_b":"length_6_cm","returnable_c":"width_6_cm","collect_a":"height_6_cm","collect_b":"diameter_6_cm","collect_c":"size_6_ml","brand":"weight_6_kg","group_name":"label_7","department":"length_7_cm","class_name":"width_7_cm","sub_class":"height_7_cm","category_1":"diameter_7_cm","category_2":"size_7_ml","category_3":"weight_7_kg","category_4":"label_8","category_5":"length_8_cm","copy_writing_agency":"width_8_cm","photographer_name":"height_8_cm","photography_date":"diameter_8_cm","event":"size_8_ml","status":"weight_8_kg","style":"label_9","p_family":"length_9_cm","pf_name":"width_9_cm","feature_1":"height_9_cm","primary_material":"diameter_9_cm","secondary_material":"size_9_ml","type_name":"weight_9_kg","material_finish_1":"label_10","material_finish_2":"length_10_cm","for_name":"width_10_cm","gender":"height_10_cm","care_instructions":"diameter_10_cm","fragile":"size_10_ml","assembly_required":"weight_10_kg","unit_components":"segment"}" } 2536789array(2) { ["product_id"]=> string(7) "2536789" ["product_attributes"]=> string(1570) "{"cv_id":"","sv_id":"","collection_id":"","season":"JJ17","hit":"JJ17","tier":"TIER1","style_no":"11DD DV","base_product_id":"","product_title":"something","introduction":"","color_name":"blue","price_a":"169","color_price_a":"","sku_price_a":"","price_b":"189","color_price_b":"","price_c":"1.5","color_price_c":"","sku_price_c":"","product_availability_b":"","product_availability_c":"","product_availability_d":"","returnable_a":"","returnable_b":"","returnable_c":"","collect_a":"","collect_b":"","collect_c":"","brand":"brand a","group_name":"group b","department":"something else","class_name":"something","sub_class":"","category_1":"","category_2":"","category_3":"","category_4":"","category_5":"","copy_writing_agency":"","photographer_name":"","photography_date":"","event":"exclusive","status":"Active","style":"Basic","p_family":"something","pf_name":"something else","feature_1":"","primary_material":"Glass","secondary_material":"Glass","type_name":"something","material_finish_1":"something","material_finish_2":"","for_name":"","gender":"","care_instructions":"","fragile":"Yes","assembly_required":"","unit_components":"","unit_pack_length_cm":"24","unit_pack_width_cm":"16","unit_pack_height_cm":"32","unit_pack_weight_g":"","length_cm":"","width_cm":"","height_cm":"","weight_kg":"","size_ml":"250","diameter_cm":"","set_includes":"","label_1":"","length_1_cm":"","width_1_cm":"","height_1_cm":"","diameter_1_cm":"","size_1_ml":"","weight_1_kg":"","label_2":"","length_2_cm":"","width_2_cm":"","height_2_cm":"","diameter_2_cm":"","size_2_":"","":""}" }INSERT INTO process_detail (process_id,product_id,product_attributes) values(1,'2536789','[{"cv_id":"","sv_id":"","collection_id":"","season":"JJ17","hit":"JJ17","tier":"TIER1","style_no":"11DD DV","base_product_id":"","product_title":"something","introduction":"","color_name":"blue","price_a":"169","color_price_a":"","sku_price_a":"","price_b":"189","color_price_b":"","price_c":"1.5","color_price_c":"","sku_price_c":"","product_availability_b":"","product_availability_c":"","product_availability_d":"","returnable_a":"","returnable_b":"","returnable_c":"","collect_a":"","collect_b":"","collect_c":"","brand":"brand a","group_name":"group b","department":"something else","class_name":"something","sub_class":"","category_1":"","category_2":"","category_3":"","category_4":"","category_5":"","copy_writing_agency":"","photographer_name":"","photography_date":"","event":"exclusive","status":"Active","style":"Basic","p_family":"something","pf_name":"something else","feature_1":"","primary_material":"Glass","secondary_material":"Glass","type_name":"something","material_finish_1":"something","material_finish_2":"","for_name":"","gender":"","care_instructions":"","fragile":"Yes","assembly_required":"","unit_components":"","unit_pack_length_cm":"24","unit_pack_width_cm":"16","unit_pack_height_cm":"32","unit_pack_weight_g":"","length_cm":"","width_cm":"","height_cm":"","weight_kg":"","size_ml":"250","diameter_cm":"","set_includes":"","label_1":"","length_1_cm":"","width_1_cm":"","height_1_cm":"","diameter_1_cm":"","size_1_ml":"","weight_1_kg":"","label_2":"","length_2_cm":"","width_2_cm":"","height_2_cm":"","diameter_2_cm":"","size_2_":"","":""}]')

this is my csv content product_id,cv_id,sv_id,collection_id,season,hit,tier,style_no,base_product_id,product_title,introduction,color_name,price_a,color_price_a,sku_price_a,price_b,color_price_b,price_c,color_price_c,sku_price_c,product_availability_b,product_availability_c,product_availability_d,returnable_a,returnable_b,returnable_c,collect_a,collect_b,collect_c,brand,group_name,department,class_name,sub_class,category_1,category_2,category_3,category_4,category_5,copy_writing_agency,photographer_name,photography_date,event,status,style,p_family,pf_name,feature_1,primary_material,secondary_material,type_name,material_finish_1,material_finish_2,for_name,gender,care_instructions,fragile,assembly_required,unit_components,unit_pack_length_cm,unit_pack_width_cm,unit_pack_height_cm,unit_pack_weight_g,length_cm,width_cm,height_cm,weight_kg,size_ml,diameter_cm,set_includes,label_1,length_1_cm,width_1_cm,height_1_cm,diameter_1_cm,size_1_ml,weight_1_kg,label_2,length_2_cm,width_2_cm,height_2_cm,diameter_2_cm,size_2_ml,weight_2_kg,label_3,length_3_cm,width_3_cm,height_3_cm,diameter_3_cm,size_3_ml,weight_3_kg,label_4,length_4_cm,width_4_cm,height_4_cm,diameter_4_cm,size_4_ml,weight_4_kg,label_5,length_5_cm,width_5_cm,height_5_cm,diameter_5_cm,size_5_ml,weight_5_kg,label_6,length_6_cm,width_6_cm,height_6_cm,diameter_6_cm,size_6_ml,weight_6_kg,label_7,length_7_cm,width_7_cm,height_7_cm,diameter_7_cm,size_7_ml,weight_7_kg,label_8,length_8_cm,width_8_cm,height_8_cm,diameter_8_cm,size_8_ml,weight_8_kg,label_9,length_9_cm,width_9_cm,height_9_cm,diameter_9_cm,size_9_ml,weight_9_kg,label_10,length_10_cm,width_10_cm,height_10_cm,diameter_10_cm,size_10_ml,weight_10_kg,segment 2536789,,,,JJ17,JJ17,TIER1,11DD DV,,something,,blue,169,,,189,,1.5,,,,,,,,,,,,brand a,group b,something else,something,,,,,,,,,,exclusive,Active,Basic,something,something else,,Glass,Glass,something,something,,,,,Yes,,,24,16,32,,,,,,250,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

thanks in advance.

What have you tried so far? Where did you get stuck? Any errors? — Kathara
– Kathara, Commented Nov 24, 2017 at 12:19
@Kathara, The challenge I have in inserting is, some CSV files will have 5 attributes columns and some will have 10 attributes and I can’t have a static attribute field names in the insert statement. — davidb
– davidb, Commented Nov 24, 2017 at 12:26
If you are "inserting into a table" then you should normalize your schema. — symcbean
– symcbean, Commented Nov 25, 2017 at 16:10
@symcbean- good point, assumes OP has control over db schema, though. — random_user_name
– random_user_name, Commented Nov 25, 2017 at 16:17

random_user_name · Accepted Answer · 2017-11-27 18:09:49Z

1

There are a handful of ways to approach this problem, however this is the way that I would solve it.

Note that this is untested code, so please either troubleshoot or else ask in comments if there's some issue / error.

NOTE: the size limit in the fgetcsv is optional, and not necessary, and in this case, causing problems - so remove the limit per the code below.

I've commented the code below to tell you what's going on at each step:

// declare an empty array to contain the column keys
$keys = [];

// load the keys into the $keys array
// load the first row
$data = fgetcsv( $source );
// loop over it, adding the keys into the array
foreach( $data AS $value ) {
    if ( $value ) {
        $keys[] = $value;
    }
}

/**
 * $keys should now be an array that looks like so:
 *
 * $keys = array(
 *             0 => 'product_id',
 *             1 => 'color',
 *             2 => 'style',
 *             3 => 'type',
 *             ..etc
 *         );
 */

// now process the rest of the csv file
while ($data = fgetcsv ( $source ) ) {
    // reset the attributes array, where we'll store the (future) JSON attributes
    $attributes = [];
    // reset product_id to ensure pick up the new / correct value
    $product_id = NULL;
    // loop over the columns in the row
    foreach( $data as $index => $value ) {
        // set the key (based on the keys array established above)
        $key = $keys[ $index ];

        // load the values into the attributes array (unless product id, where we put that in the $product_id variable)
        if ( 'product_id' != $key ) {
            $attributes[ $key ] = $value;
        } else {
            $product_id = $value;
        }
    }

    if ( empty( $attributes ) || ! $product_id ) {
        continue;
    }

    // prepare the values for a PDO database insert
    $bound_values = [
        'product_id'         => $product_id,
        'product_attributes' => json_encode( $attributes )
    ];

    // insert into the database
    $stmt = $db->prepare( "INSERT INTO product_table SET product_id = :product_id, product_attributes = :product_attributes" );
    $stmt->execute( $bound_values );
}

edited Nov 27, 2017 at 18:09

answered Nov 25, 2017 at 16:07

random_user_name

26.3k7 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

davidb Over a year ago

thanks you so much, im trying your solution. will update shortly

davidb Over a year ago

i have tried with 10 columns it worked fine, but when i tried with more than 30 columns it is inserting one more row. for example, i have two rows in a file with more than 30 columns, it is inserting 3 rows and two rows are properly inserted and one row inserted wrongly with different headers different values.

davidb Over a year ago

my files will have few empty cells with headers at times, just for your info.

davidb Over a year ago

empty cells let it insert with keys with empty values

davidb Over a year ago

thank you, i have updated with your updated edit. when i echo $product_id; var_dump( $bound_values); i get header name of a particular field as product_id, but i have a different table with individual attribute columns for testing, when i inserted in that table the same file worked.

|

Collectives™ on Stack Overflow

How to convert few columns as JSON array in php while csv import

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related