如何在csv导入时在php中将少数列转换为JSON数组 [英] How to convert few columns as JSON array in php while csv import
问题描述
我有一个csv文件，请查看下面的图像CSV
< img src =https://i.stack.imgur.com/zH898.pngalt =在这里输入图片描述>
这是我的php
函数转换（$字符串）
{
返回htmlspecialchars（$字符串，ENT_QUOTES）;
}
$ columnArray = array（）;
$ dataArray = array（）;
$ firstRule = true;
$ b $（$ data = fgetcsv（$ source，1000，，））
{
if（$ firstRule）
{
foreach $ data as $ columnName）
{
$ columnArray [] = $ columnName;
}
$ firstRule = false;
}
else
{
$ rule = array（）; （$ i = 0; $ i< count（$ data）; $ i ++）
{
$ rule [$ columnArray [$ i]] = $ data [$ i] ;
}
$ dataArray [] = $ rule;
foreach（$ dataArray as $ data）
{
//在这里我卡住了，我想要json_encode几列并插入到表中
code $
$ p $ echo $ product_id; var_dump（$ bound_values）;
mlarray（2）{[product_id] =>字符串（2）ml[product_attributes] => （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， 引入： 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， 品牌： weight_6_kg， 组名： label_7， 部门： 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：标签_8\" ， category_5： length_8_cm， copy_writing_agency： width_8_cm， PHOTOGRAPHER_NAME： height_8_cm， photography_date： diameter_8_cm， 事件： size_8_ml， 状态： weight_8_kg ， 风格： 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， 性别： height_10_cm， care_instructions： diameter_10_cm， 脆弱 ：size_10_ml，assembly_required：weight_10_kg，unit_components：segment}} 2536789array（2）{[product_id] =>字符串（7）2536789[product_attributes] =>字符串（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：其他，class_name ： ， 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， feature_1： ， primary_material： 玻璃， secondary_material： 玻璃， TYPE_NAME： 东西，material_ finish_1 ： 东西， material_finish_2： ， for_name： ， 性别： ， care_instructions： ， 脆弱： 是的， 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： ， 季节： JJ17， 打： JJ17， 梯队： 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：品牌a，group_name：group b，department：其他，class_name： 东西， sub_class： ， category_1： ， category_2： ， category_3： ， category_4： ， category_5： ， copy_writing_agency： ， PHOTOGRAPHER_NAME： ， photography_date： ， 事件： 独家， 状态： 活动， 风格： 基本， p_family： 东西， material_finish_1：something，material_finish_2，material_finish_1， ： ， for_name： ， 性别： ， care_instructions： ， 脆弱： 是的， 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 _：，：}]'）
这是我的csv内容
的product_id，cv_id，sv_id，collection_id，赛季命中，层，style_no，base_product_id，PRODUCT_TITLE，引进，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，品牌，组名，部门，CLASS_NAME，sub_class，category_1，category_2，category_3，category_4，category_5，copy_writing_agency，PHOTOGRAPHER_NAME，photography_date，事件，状态，风格，p_family，pf_name，feature_1，primary_material ，secondary_material，TYPE_NAME，material_finish_1，material_finish_2，for_name，性别，care_instructions，脆弱，assembly_r equired，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 ,,东西,,蓝色，169 ,,, 189，，1.5 ,,,,,,,,,,,,牌子一个，B组，别的东西,,,,,,, ,,,独家，主动，基本，东西，别的东西，玻璃，玻璃，东西，东西,,,,,是,,, 24,16,32 ,,,,,, 250 ,,,,,,, ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,
提前致谢。
解决方案有很多方法可以解决这个问题，但这是我解决这个问题的方法。
请注意，这是未经测试的代码，因此请排除故障或在注释中询问是否存在问题/错误。
注意： fgetcsv 中的大小限制是可选的，而不是必需的，在这种情况下，会导致问题 - 所以删除下面代码的限制。
我已经评论了下面的代码，告诉你在每一步中发生了什么：
//声明一个空数组来包含列键
$ keys = [];
//将键加载到$ keys数组
//加载第一行
$ data = fgetcsv（$ source）;
//循环它，将数组添加到数组
foreach（$ data AS $ value）{
if（$ value）{
$ keys [] = $ value ;
$ b $ **
* $ key现在应该是一个如下所示的数组：
*
* $ key =数组（
* 0 =>'product_id'，
* 1 =>'color'，
* 2 =>'style'，
* 3 => ;'type'，
* .. etcc
*）;
* /
//现在处理其余的csv文件
while（$ data = fgetcsv（$ source））{
// reset the attributes array ，我们将在那里存储（将来的）JSON属性
$ attributes = [];
//重置product_id以确保选取新的/正确的值
$ product_id = NULL;
//遍历行中的列
foreach（$ data as $ index => $ value）{
//设置键值（基于上面建立的键值数组）
$ key = $ keys [$ index];
//将值加载到属性数组中（除非产品ID，我们把它放在$ product_id变量中）
if（'product_id'！= $ key）{
$ attributes [$ key] = $ value;
} else {
$ product_id = $ value;
if（empty（$ attributes）||！$ product_id）{
continue;
}
//准备一个PDO数据库的值插入
$ bound_values = [
'product_id'=> $ product_id，
'product_attributes'=> json_encode（$ attributes）
];
//插入数据库
$ stmt = $ db-> prepare（INSERT INTO product_table SET product_id =：product_id，product_attributes =：product_attributes）;
$ stmt-> execute（$ bound_values）;
}
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.
解决方案 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 );
}
这篇关于如何在csv导入时在php中将少数列转换为JSON数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
![< img src =https://i.stack.imgur.com/zH898.pngalt =在这里输入图片描述>](\"https://i.stack.imgur.com/zH898.png\"){kind=link}
![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}]'; 注意：我的csv头在CSV文件的第一行是wil l完全匹配Mysql表头](\"https://i.stack.imgur.com/91Lzb.png\"){kind=link}