Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

Re: delete duplicated xml lines

by hbm (Hermit)
on Aug 08, 2012 at 18:49 UTC ( #986339=note: print w/ replies, xml ) Need Help??


in reply to delete duplicated xml lines

I imagine you don't want to delete duplicate lines, you want to avoid creating them. As you walk through your spreadsheet, create an XML line only if the current line hasn't already been seen; and then hash the current line so it won't be duplicated. Something like:

my %seen; for my $thing ($worksheet->...) { next if exists $seen{$thing}; # create XML element $seen{$thing}++; }


Comment on Re: delete duplicated xml lines
Download Code
Re^2: delete duplicated xml lines
by cibiena (Initiate) on Aug 09, 2012 at 17:43 UTC
    Thankyou very much for your answer, but sorry I'am novice in perl and is very difficult for me. this is a little part of my code:
    my $materialmapping_table_xml = XML::LibXML->createDocument( "1.0", "U +TF-8"); my $materialmapping_table_xml_root = $materialmapping_table_xml->creat +eElement("masterdata"); $materialmapping_table_xml->setDocumentElement($materialmapping_table_ +xml_root); my $materialmapping_item3 = $materialmapping_table_xml->createElement( +"item3"); $materialmapping_item3->setAttribute(decode('cp1252',$pr_cell_name3->{ +Val}),$pr3); $materialmapping_item3->setAttribute("pr_family",$family); $materialmapping_table_xml_root->addChild($materialmapping_item3);
    and the same for item2... for example, generate this output:
    <item2 duble="1" pr="c" pr_width="1250" pr_family="2" /> <item2 duble="2" pr="c" pr_width="1250" pr_family="2" /> <item2 pr="a" duble="2" pr_width="1250" pr_family="2" /> <item2 pr="b" duble="2" pr_width="1250" pr_family="2" /> <item2 pr="c" duble="2" pr_width="1250" pr_family="2" /> <item3 pr="a" duble="1" pr_width="1250" pr_family="2" /> <item3 pr="b" duble="1" pr_width="1250" pr_family="2" /> <item3 pr="c" duble="1" pr_width="1250" pr_family="2" /> <item3 pr_width="1250" duble="1" pr="c" pr_family="2" /> <item3 pr="a" duble="2" pr_width="1250" pr_family="2" /> <item3 pr="b" duble="2" pr_width="1250" pr_family="2" /> <item3 pr="c" duble="2" pr_width="1250" pr_family="2" /> <item3 pr_width="1250" duble="2" pr="c" pr_family="2" /> <item3 pr_width="1250" pr="c" duble="2" pr_family="2" /> <item3 pr_width="1250" pr="c" duble="2" pr_family="2" /> <item3 pr_width="1250" pr="c" duble="2" pr_family="2" /> <item2 pr="a" duble="3" pr_width="1250" pr_family="2" />
    but the OUTPUT must be:
    <item3 pr="a" duble="1" pr_width="1250" pr_family="2" /> <item3 pr="b" duble="1" pr_width="1250" pr_family="2" /> <item3 pr="c" duble="1" pr_width="1250" pr_family="2" /> <item3 pr="a" duble="2" pr_width="1250" pr_family="2" /> <item3 pr="b" duble="2" pr_width="1250" pr_family="2" /> <item3 pr="c" duble="2" pr_width="1250" pr_family="2" /> <item2 pr="a" duble="3" pr_width="1250" pr_family="2" />
    where I have to put the code? thankyou very much for your precious help

      Please provide all of your code.

        #!/usr/bin/perl -w ######################### # # Materialmapping generator # Updated 30.07.2012 # my $version="0.0.3"; # 2012.04.12 v0.0.1 DM: Creation # 2012.04.23 v0.0.2 DM: Added support for active column and comma s +epareted families # 2012.05.30 v0.0.3 DM: Added support for comma separated propriety + - only one for line #Excel format rules: #-a merged cell named "Configurator mapping" on the first line identif +ies the mapping data (internal+properties) #-a merged cell named "Brand" on the first line identifies the Brand d +ata (optional) #-a merged cell named "Market" on the first line identifies the Market + data (optional) #-a merged cell named "Customer" on the first line identifies the Cust +omer data (optional) #-the first column of "Configurator mapping" should have an "internal" + as cell(line number is not important), the "internal" define the tit +le bar line; the cell must be on the same line where all the properti +es/markets/brand/customer names are #-the "active" column must be exactly left of the "internal" column #-the commercial code column must be exacly left of the "active" colum +n #-the "pr_family" column should be exactly right of the "internal" col +umn #-the tecnical description must be exactly right of the last "Configur +ator mapping" column #-the colorzones must be exactly right of the last "tecnical descripti +on" column #Notes: #-in the field "pr_family" the user can concatenate the family value l +ike 'TL1_1,TL1_4' , every value generate a line in the xml file with +the right family #-in the colums "propriety" the user can concatenate the propriety val +ue like 'value1,value2' (but only for one pr colum for line), every v +alue add a line in the xml file with the right propriety #-if the "active" column is not empty, the line is not evaluated #-if "internal" or "pr_family" field is empty, the line is not evaluat +ed # ######################### use Encode; use utf8; use XML::LibXML; use Spreadsheet::ParseExcel; system("cls"); # Read command line arguments #--------------------------------------------------------------------- +------------------- my $materialmapping_file = shift; my $data_folder = shift; print "OPTIONS\nmaterialmapping_file:$materialmapping_file\ndata_folde +r:$data_folder\n"; # subroutine sub getSQLTimeStamp { my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localt +ime(time); return sprintf "%4d-%02d-%02d %02d:%02d:%02d",$year+1900,$mon+1 +,$mday,$hour,$min,$sec; } # Main #--------------------------------------------------------------------- +------------------- if(length($materialmapping_file) gt 0 and length($data_folder) gt 0) { opendir ( DIR, $data_folder ) || die "Error in opening dir $data_f +older\n"; my $materialmapping_table_xml = XML::LibXML->createDocument( "1.0" +, "UTF-8"); my $materialmapping_table_xml_root = $materialmapping_table_xml->c +reateElement("masterdata"); $materialmapping_table_xml_root->setAttribute('version',getSQLTime +Stamp()); $materialmapping_table_xml->setDocumentElement($materialmapping_ta +ble_xml_root); print "Material mapping generator v.0.0.3 - Parsing files...\n"; while( ($filename = readdir(DIR))){ if($filename =~ /\.xls$/i) { my $parser = Spreadsheet::ParseExcel->new(); my $oBook = $parser->parse($data_folder.$filename); if (defined $oBook ) { for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iS +heet++) { $oWkS = $oBook->{Worksheet}[$iSheet]; # find the needed columns my $map_Cmin = -1; # the first is internal my $map_Cmax = -1; my $artno_col = -1; my $active_col = -1; my $colorzones_col = -1; my $family_col = -1; my $tec_desc_col = -1; my $brand_Cmin = -1; my $brand_Cmax = -1; my $customer_Cmin = -1; my $customer_Cmax = -1; my $market_Cmin = -1; my $market_Cmax = -1; my $title_row = -1; for (my $iC = $oWkS->{MinCol}; defined $oWkS->{Max +Col} && $iC <= $oWkS->{MaxCol} ; $iC++) { $oWkC = $oWkS->{Cells}[$oWkS->{MinRow}][$iC]; if(defined $oWkC) { if(decode('cp1252',$oWkC->{Val}) eq "Confi +gurator mapping") { for (my $iR = $oWkS->{MinRow} +1; defi +ned $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++) { $oWkC_int = $oWkS->{Cells}[$iR][$i +C]; if(defined $oWkC_int and decode('c +p1252',$oWkC_int->{Val}) eq "internal") { $title_row = $iR; } } if($title_row > 0) { foreach my $area ( @{ $oWkS->{Merg +edArea} } ) { if($area->[1] eq $iC and $area +->[0] eq $oWkS->{MinRow}){ $map_Cmax = $area->[3]; } } $map_Cmin = $iC; $artno_col = $map_Cmin -2; $active_col = $map_Cmin -1; $family_col = $map_Cmin +1; $colorzones_col = $map_Cmax +2; $tec_desc_col = $map_Cmax +1; } } elsif(decode('cp1252',$oWkC->{Val}) eq " +Market"){ foreach my $area ( @{ $oWkS->{MergedAr +ea} } ) { if($area->[1] eq $iC and $area->[0 +] eq $oWkS->{MinRow}){ $market_Cmax = $area->[3]; } } $market_Cmin = $iC; } elsif(decode('cp1252',$oWkC->{Val}) eq " +Customer"){ foreach my $area ( @{ $oWkS->{MergedAr +ea} } ) { if($area->[1] eq $iC and $area->[0 +] eq $oWkS->{MinRow}){ $customer_Cmax = $area->[3]; } } $customer_Cmin = $iC; } elsif(decode('cp1252',$oWkC->{Val}) eq " +Brand"){ foreach my $area ( @{ $oWkS->{MergedAr +ea} } ) { if($area->[1] eq $iC and $area->[0 +] eq $oWkS->{MinRow}){ $brand_Cmax = $area->[3]; } } $brand_Cmin = $iC; } } } if($map_Cmin >= 0 and $map_Cmin >= 0 and $title_ro +w >= 0){ for(my $iR = $title_row +1; defined $oWkS->{Ma +xRow} && $iR <= $oWkS->{MaxRow} ; $iR++){ $internal_cell = $oWkS->{Cells}[$iR][$map_ +Cmin]; $active_cell = $oWkS->{Cells}[$iR][$active +_col]; $family_cell = $oWkS->{Cells}[$iR][$family +_col]; if(defined $internal_cell and defined $act +ive_cell and defined $family_cell and length(decode('cp1252',$interna +l_cell->{Val})) gt 0 and !(length(decode('cp1252',$active_cell->{Val} +)) gt 0) and length(decode('cp1252',$family_cell->{Val})) gt 0) { #fo every family print a line my @families = split(/[,.]+/, decode(' +cp1252',$family_cell->{Val})); foreach $family(@families){ + my $materialmapping_item = $materialmapping_table_xml->createElemen +t("item"); + + #add all propriety for(my $prC = $map_Cmin+2; $prC <= + $map_Cmax ; $prC++) { $pr_cell_name = $oWkS->{Ce +lls}[$title_row][$prC]; $pr_cell = $oWkS->{Cells}[ +$iR][$prC]; if(defined $pr_cell and de +fined $pr_cell_name and length(decode('cp1252',$pr_cell->{Val})) gt 0 + and length(decode('cp1252',$pr_cell_name->{Val})) gt 0) { + my @pr = split(/[,.]+/, de +code('cp1252',$pr_cell->{Val})); + foreach $pr(@pr){ + + $materialmapping_item->setAttribute(decode( +'cp1252',$pr_cell_name->{Val}),$pr); + } + } + } + + + #for every multiple less 2 propriety print a line + for(my $prC2 = $map_Cmin+2; $prC2 <= $map_Cmax ; $prC2++) { $pr_cell_name2 = $oWkS->{C +ells}[$title_row][$prC2]; $pr_cell2 = $oWkS->{Cells} +[$iR][$prC2]; if(defined $pr_cell2 and d +efined $pr_cell_name2 and length(decode('cp1252',$pr_cell2->{Val})) g +t 1 and length(decode('cp1252',$pr_cell_name2->{Val})) gt 1) { my @pr2 = split(/[,.]+/, d +ecode('cp1252',$pr_cell2->{Val})); + foreach $pr2(@pr2){ + if(@pr2 gt 1) + { + my $materialmapping_item2 = $materialmapp +ing_table_xml->createElement("item3"); + $materialmapping_item2->setAttribute(deco +de('cp1252',$pr_cell_name2->{Val}),$pr2); + + for(my $prC3 = $map_Cmin+2; $prC3 <= $map +_Cmax ; $prC3++) + { + $pr_cell_name3 = $oWkS->{C +ells}[$title_row][$prC3]; + $pr_cell3 = $oWkS->{Cells} +[$iR][$prC3]; + if($prC3 ne $prC2) + { + my @pr3 = spl +it(/[,.]+/, decode('cp1252',$pr_cell3->{Val})); + foreach $pr3 +(@pr3) + { + + $materialmapping_item2->setAttribute(decode('cp1252',$pr_cell_name3 +->{Val}),$pr3); + } + } + } + + + #set internal $materialmapping_item2->setAttribu +te("internal",decode('cp1252',$internal_cell->{Val})); #set family $materialmapping_item2->setAttribu +te("pr_family",$family); #set item_number $item_number_cell = $oWkS->{Cells} +[$iR][$artno_col]; if(defined $item_number_cell and l +ength(decode('cp1252',$item_number_cell->{Val})) gt 0) { $materialmapping_item2->setAtt +ribute("item_number",decode('cp1252',$item_number_cell->{Val})); } #set colorzones $colorzones_cell = $oWkS->{Cells}[ +$iR][$colorzones_col]; if(defined $colorzones_cell and le +ngth(decode('cp1252',$colorzones_cell->{Val})) gt 0) { $materialmapping_item2->setAtt +ribute("colorzone",decode('cp1252',$colorzones_cell->{Val})); } #set description $description_cell = $oWkS->{Cells} +[$iR][$tec_desc_col]; if(defined $description_cell and l +ength(decode('cp1252',$description_cell->{Val})) gt 0) { $materialmapping_item2->setAtt +ribute("description",decode('cp1252',$description_cell->{Val})); } #set Brands if($brand_Cmin gt 0 and $brand_Cma +x gt 0) { my $brand_string = ""; my $ignored_brand_string = ""; for(my $brandC = $brand_Cmin; +$brandC <= $brand_Cmax ; $brandC++) { $brand_cell_name = $oWkS-> +{Cells}[$title_row][$brandC]; if(defined $brand_cell_nam +e and decode('cp1252',$brand_cell_name->{Val}) ne "Epta std") { $brand_cell = $oWkS->{ +Cells}[$iR][$brandC]; if(defined $brand_cell + and length(decode('cp1252',$brand_cell->{Val})) gt 0) { if(decode('cp1252' +,$brand_cell->{Val}) eq '0') { $ignored_brand +_string = $ignored_brand_string . decode('cp1252',$brand_cell_name->{ +Val}) . ","; } else { $brand_string += $brand_string . decode('cp1252',$brand_cell_name->{Val}) . ","; } } } } if(length($brand_string) gt 0) +{ $materialmapping_item2->se +tAttribute('brand',$brand_string); } if(length($ignored_brand_strin +g) gt 0){ $materialmapping_item2->se +tAttribute('ignore_brand',$ignored_brand_string); } } #set Customer if($customer_Cmin gt 0 and $custom +er_Cmax gt 0) { my $customer_string = ""; my $ignored_customer_string = +""; for(my $customerC = $customer_ +Cmin; $customerC <= $customer_Cmax ; $customerC++) { $customer_cell_name = $oWk +S->{Cells}[$title_row][$customerC]; $customer_cell = $oWkS->{C +ells}[$iR][$customerC]; if(defined $customer_c +ell_name and defined $customer_cell and length(decode('cp1252',$custo +mer_cell->{Val})) gt 0 and length(decode('cp1252',$customer_cell_name +->{Val})) gt 0) { if(decode('cp1252' +,$customer_cell->{Val}) eq '0') { $ignored_custo +mer_string = $ignored_customer_string . decode('cp1252',$customer_cel +l_name->{Val}) . ","; } else { $customer_stri +ng = $customer_string . decode('cp1252',$customer_cell_name->{Val}) . + ","; } } } if(length($customer_string) gt + 0){ $materialmapping_item2->se +tAttribute('customer',$customer_string); } if(length($ignored_customer_st +ring) gt 0){ $materialmapping_item2->se +tAttribute('ignore_customer',$ignored_customer_string); } } #set Market if($market_Cmin gt 0 and $market_C +max gt 0) { my $market_string = ""; my $ignored_market_string = "" +; for(my $marketC = $market_Cmin +; $marketC <= $market_Cmax ; $marketC++) { $market_cell_name = $oWkS- +>{Cells}[$title_row][$marketC]; $market_cell = $oWkS->{Cel +ls}[$iR][$marketC]; if(defined $market_cel +l_name and defined $market_cell and length(decode('cp1252',$market_ce +ll->{Val})) gt 0 and length(decode('cp1252',$market_cell_name->{Val}) +) gt 0) { if(decode('cp1252' +,$market_cell->{Val}) eq '0') { $ignored_marke +t_string = $ignored_market_string . decode('cp1252',$market_cell_name +->{Val}) . ","; } else { $market_string + = $market_string . decode('cp1252',$market_cell_name->{Val}) . ","; } } } if(length($market_string) gt 0 +){ $materialmapping_item2->se +tAttribute('market',$market_string); } if(length($ignored_market_stri +ng) gt 0){ $materialmapping_item2->se +tAttribute('ignore_market',$ignored_market_string); + } } + + + $materialmapping_item = $materialmapping_item2; + $materialmapping_table_xml_root->addChild($materialmapping_item2); + + +} + } } } + + + + + + + + + + + #for every multiple propriety print a line + for(my $prC2 = $map_Cmin+2; $prC2 <= $map_Cmax ; $prC2++) { $pr_cell_name2 = $oWkS->{C +ells}[$title_row][$prC2]; $pr_cell2 = $oWkS->{Cells} +[$iR][$prC2]; if(defined $pr_cell2 and d +efined $pr_cell_name2 and length(decode('cp1252',$pr_cell2->{Val})) g +t 0 and length(decode('cp1252',$pr_cell_name2->{Val})) gt 0) { my @pr2 = split(/[,.]+/, d +ecode('cp1252',$pr_cell2->{Val})); + foreach $pr2(@pr2){ + if(@pr2 gt 1) + { + + + for(my $prC3 = $map_Cmin+2; $prC3 <= $map +_Cmax ; $prC3++) + { + $pr_cell_name3 = $oWkS->{C +ells}[$title_row][$prC3]; + $pr_cell3 = $oWkS->{Cells} +[$iR][$prC3]; + if($prC3 gt $prC2) + { + my @pr3 = spl +it(/[,.]+/, decode('cp1252',$pr_cell3->{Val})); + foreach $pr3 +(@pr3) + { + + + if(@p +r3 gt 1) + { + + my $materialmapping_item3 = $materialmapping_table_xml->createEleme +nt("item2"); + + + $mater +ialmapping_item3->setAttribute(decode('cp1252',$pr_cell_name3->{Val}) +,$pr3); + + + for(my $prC +4 = $map_Cmin+2; $prC4 <= $map_Cmax ; $prC4++) + { + $pr_cell_name4 = $oWkS->{C +ells}[$title_row][$prC4]; + $pr_cell4 = $oWkS->{Cells} +[$iR][$prC4]; + if($prC4 ne $prC3) + { + my @pr4 = spl +it(/[,.]+/, decode('cp1252',$pr_cell4->{Val})); + foreach $pr4 +(@pr4) + { + + + $materialmapping_item3->setAttribute(dec +ode('cp1252',$pr_cell_name2->{Val}),$pr2); + $materialmapping_item3->setAttribute(deco +de('cp1252',$pr_cell_name4->{Val}),$pr4); + + } + }} + + #set internal $materialmapping_item3->setAttribu +te("internal",decode('cp1252',$internal_cell->{Val})); #set family $materialmapping_item3->setAttribu +te("pr_family",$family); #set item_number $item_number_cell = $oWkS->{Cells} +[$iR][$artno_col]; if(defined $item_number_cell and l +ength(decode('cp1252',$item_number_cell->{Val})) gt 0) { $materialmapping_item3->setAtt +ribute("item_number",decode('cp1252',$item_number_cell->{Val})); } #set colorzones $colorzones_cell = $oWkS->{Cells}[ +$iR][$colorzones_col]; if(defined $colorzones_cell and le +ngth(decode('cp1252',$colorzones_cell->{Val})) gt 0) { $materialmapping_item3->setAtt +ribute("colorzone",decode('cp1252',$colorzones_cell->{Val})); } #set description $description_cell = $oWkS->{Cells} +[$iR][$tec_desc_col]; if(defined $description_cell and l +ength(decode('cp1252',$description_cell->{Val})) gt 0) { $materialmapping_item3->setAtt +ribute("description",decode('cp1252',$description_cell->{Val})); } #set Brands if($brand_Cmin gt 0 and $brand_Cma +x gt 0) { my $brand_string = ""; my $ignored_brand_string = ""; for(my $brandC = $brand_Cmin; +$brandC <= $brand_Cmax ; $brandC++) { $brand_cell_name = $oWkS-> +{Cells}[$title_row][$brandC]; if(defined $brand_cell_nam +e and decode('cp1252',$brand_cell_name->{Val}) ne "Epta std") { $brand_cell = $oWkS->{ +Cells}[$iR][$brandC]; if(defined $brand_cell + and length(decode('cp1252',$brand_cell->{Val})) gt 0) { if(decode('cp1252' +,$brand_cell->{Val}) eq '0') { $ignored_brand +_string = $ignored_brand_string . decode('cp1252',$brand_cell_name->{ +Val}) . ","; } else { $brand_string += $brand_string . decode('cp1252',$brand_cell_name->{Val}) . ","; } } } } if(length($brand_string) gt 0) +{ $materialmapping_item3->se +tAttribute('brand',$brand_string); } if(length($ignored_brand_strin +g) gt 0){ $materialmapping_item3->se +tAttribute('ignore_brand',$ignored_brand_string); } } #set Customer if($customer_Cmin gt 0 and $custom +er_Cmax gt 0) { my $customer_string = ""; my $ignored_customer_string = +""; for(my $customerC = $customer_ +Cmin; $customerC <= $customer_Cmax ; $customerC++) { $customer_cell_name = $oWk +S->{Cells}[$title_row][$customerC]; $customer_cell = $oWkS->{C +ells}[$iR][$customerC]; if(defined $customer_c +ell_name and defined $customer_cell and length(decode('cp1252',$custo +mer_cell->{Val})) gt 0 and length(decode('cp1252',$customer_cell_name +->{Val})) gt 0) { if(decode('cp1252' +,$customer_cell->{Val}) eq '0') { $ignored_custo +mer_string = $ignored_customer_string . decode('cp1252',$customer_cel +l_name->{Val}) . ","; } else { $customer_stri +ng = $customer_string . decode('cp1252',$customer_cell_name->{Val}) . + ","; } } } if(length($customer_string) gt + 0){ $materialmapping_item3->se +tAttribute('customer',$customer_string); } if(length($ignored_customer_st +ring) gt 0){ $materialmapping_item3->se +tAttribute('ignore_customer',$ignored_customer_string); } } #set Market if($market_Cmin gt 0 and $market_C +max gt 0) { my $market_string = ""; my $ignored_market_string = "" +; for(my $marketC = $market_Cmin +; $marketC <= $market_Cmax ; $marketC++) { $market_cell_name = $oWkS- +>{Cells}[$title_row][$marketC]; $market_cell = $oWkS->{Cel +ls}[$iR][$marketC]; if(defined $market_cel +l_name and defined $market_cell and length(decode('cp1252',$market_ce +ll->{Val})) gt 0 and length(decode('cp1252',$market_cell_name->{Val}) +) gt 0) { if(decode('cp1252' +,$market_cell->{Val}) eq '0') { $ignored_marke +t_string = $ignored_market_string . decode('cp1252',$market_cell_name +->{Val}) . ","; } else { $market_string + = $market_string . decode('cp1252',$market_cell_name->{Val}) . ","; } } } if(length($market_string) gt 0 +){ $materialmapping_item3->se +tAttribute('market',$market_string); } if(length($ignored_market_stri +ng) gt 0){ $materialmapping_item3->se +tAttribute('ignore_market',$ignored_market_string); + } } + + + + + + + $materialmapping_item = $materialmapping_item3; + $materialmapping_table_xml_root->addChild($materialmapping_ +item3); + } + + }}} + + + + + }} } } + + #set internal $materialmapping_item->setAttribut +e("internal",decode('cp1252',$internal_cell->{Val})); #set family $materialmapping_item->setAttribut +e("pr_family",$family); #set item_number $item_number_cell = $oWkS->{Cells} +[$iR][$artno_col]; if(defined $item_number_cell and l +ength(decode('cp1252',$item_number_cell->{Val})) gt 0) { $materialmapping_item->setAttr +ibute("item_number",decode('cp1252',$item_number_cell->{Val})); } #set colorzones $colorzones_cell = $oWkS->{Cells}[ +$iR][$colorzones_col]; if(defined $colorzones_cell and le +ngth(decode('cp1252',$colorzones_cell->{Val})) gt 0) { $materialmapping_item->setAttr +ibute("colorzone",decode('cp1252',$colorzones_cell->{Val})); } #set description $description_cell = $oWkS->{Cells} +[$iR][$tec_desc_col]; if(defined $description_cell and l +ength(decode('cp1252',$description_cell->{Val})) gt 0) { $materialmapping_item->setAttr +ibute("description",decode('cp1252',$description_cell->{Val})); } #set Brands if($brand_Cmin gt 0 and $brand_Cma +x gt 0) { my $brand_string = ""; my $ignored_brand_string = ""; for(my $brandC = $brand_Cmin; +$brandC <= $brand_Cmax ; $brandC++) { $brand_cell_name = $oWkS-> +{Cells}[$title_row][$brandC]; if(defined $brand_cell_nam +e and decode('cp1252',$brand_cell_name->{Val}) ne "Epta std") { $brand_cell = $oWkS->{ +Cells}[$iR][$brandC]; if(defined $brand_cell + and length(decode('cp1252',$brand_cell->{Val})) gt 0) { if(decode('cp1252' +,$brand_cell->{Val}) eq '0') { $ignored_brand +_string = $ignored_brand_string . decode('cp1252',$brand_cell_name->{ +Val}) . ","; } else { $brand_string += $brand_string . decode('cp1252',$brand_cell_name->{Val}) . ","; } } } } if(length($brand_string) gt 0) +{ $materialmapping_item->set +Attribute('brand',$brand_string); } if(length($ignored_brand_strin +g) gt 0){ $materialmapping_item->set +Attribute('ignore_brand',$ignored_brand_string); } } #set Customer if($customer_Cmin gt 0 and $custom +er_Cmax gt 0) { my $customer_string = ""; my $ignored_customer_string = +""; for(my $customerC = $customer_ +Cmin; $customerC <= $customer_Cmax ; $customerC++) { $customer_cell_name = $oWk +S->{Cells}[$title_row][$customerC]; $customer_cell = $oWkS->{C +ells}[$iR][$customerC]; if(defined $customer_c +ell_name and defined $customer_cell and length(decode('cp1252',$custo +mer_cell->{Val})) gt 0 and length(decode('cp1252',$customer_cell_name +->{Val})) gt 0) { if(decode('cp1252' +,$customer_cell->{Val}) eq '0') { $ignored_custo +mer_string = $ignored_customer_string . decode('cp1252',$customer_cel +l_name->{Val}) . ","; } else { $customer_stri +ng = $customer_string . decode('cp1252',$customer_cell_name->{Val}) . + ","; } } } if(length($customer_string) gt + 0){ $materialmapping_item->set +Attribute('customer',$customer_string); } if(length($ignored_customer_st +ring) gt 0){ $materialmapping_item->set +Attribute('ignore_customer',$ignored_customer_string); } } #set Market if($market_Cmin gt 0 and $market_C +max gt 0) { my $market_string = ""; my $ignored_market_string = "" +; for(my $marketC = $market_Cmin +; $marketC <= $market_Cmax ; $marketC++) { $market_cell_name = $oWkS- +>{Cells}[$title_row][$marketC]; $market_cell = $oWkS->{Cel +ls}[$iR][$marketC]; if(defined $market_cel +l_name and defined $market_cell and length(decode('cp1252',$market_ce +ll->{Val})) gt 0 and length(decode('cp1252',$market_cell_name->{Val}) +) gt 0) { if(decode('cp1252' +,$market_cell->{Val}) eq '0') { $ignored_marke +t_string = $ignored_market_string . decode('cp1252',$market_cell_name +->{Val}) . ","; } else { $market_string + = $market_string . decode('cp1252',$market_cell_name->{Val}) . ","; } } } if(length($market_string) gt 0 +){ $materialmapping_item->set +Attribute('market',$market_string); } if(length($ignored_market_stri +ng) gt 0){ $materialmapping_item->set +Attribute('ignore_market',$ignored_market_string); + } } + + $materialmapping_table_xml_root->addChild($materialmapping_item); + }; } } } } } print "Parsed $filename \n"; } } $materialmapping_table_xml->toFile($materialmapping_file,2); }
        example: read this excel table:
        | A | B | C | D | E | ===+========+==========+===========+=========+=========+ 1 | | Configurator mapping | ---+--------+----------+-----------+---------+---------+ 2 | active | internal | pr_family | pr1 | pr2 | ---+--------+----------+-----------+---------+---------+ 3 | | item 1 | f1 | 1,2,3 | a,b | ---+--------+----------+-----------+---------+---------+ 4 | | item 2 | f1,f2 | 1 | a | ---+--------+----------+-----------+---------+---------+ 5 | | item 3 | f1 | 2 | b | ---+--------+----------+-----------+---------+---------+
        When this spreadsheet is processed by the script, the output must be:
        <?xml version="1.0" encoding="UTF-8"?> <masterdata version="2012-07-27 01:51:57"> <item internal="item 1 " pr_family="f1" pr1="1" pr2="a"/> <item internal="item 1 " pr_family="f1" pr1="1" pr2="b"/> <item internal="item 1 " pr_family="f1" pr1="2" pr2="a"/> <item internal="item 1 " pr_family="f1" pr1="2" pr2="b"/> <item internal="item 1 " pr_family="f1" pr1="3" pr2="a"/> <item internal="item 1 " pr_family="f1" pr1="3" pr2="b"/> <item internal="item 2 " pr_family="f1" pr1="1" pr2="a"/> <item internal="item 2 " pr_family="f2" pr1="1" pr2="a"/> <item internal="item 3 " pr_family="f1" pr1="2" pr2="b"/>
        The script do this but generate a lot of duplicate lines.. the solution is filter duplicate line or modify the logic.. thankyou very much for your answer

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://986339]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (9)
As of 2014-08-21 10:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (133 votes), past polls