Hi, I make this perl code (convert excel files in one xml file), it works well but is too much slow, 4 minute to create the xml file (12mb). Can you help me to modify this perl code to speed up the conversion?
thanks :)
Andrea
#!/usr/bin/perl -w
#########################
my $version="0.0.4";
# 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.07.30 v0.0.3 DM: Added support for comma ',' or point '.' mu
+ltiple separated propriety - (only one multiple propriety cell for li
+ne);
# Optimize and relived the perl code removing unused
+perl module: (File::Find - Spreadsheet::ParseExcel - Switch);
# 2012.08.28 v0.0.4 DM: Added support for ONE, TWO or THREE comm
+a (or point) separated propriety for excel 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' (one two or three pr colum for excel line), e
+very value 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 "Material mapping generator - Version 0.0.4 \nmaterialmapping_fi
+le:$materialmapping_file\ndata_folder:$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
#---------------------------------------------------------------------
+-------------------
unless (defined $materialmapping_file and defined $data_folder)
{
print "Usage: $0 <materialmapping_file> <data_folder>\n";
exit;
}
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 "loading...\n";
while( ($filename = readdir(DIR))){
next unless $filename =~ /\.xls$/i;
my $parser = Spreadsheet::ParseExcel->new();
my $oBook = $parser->parse($data_folder.$filename);
next unless 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");
+
+ #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}));
}
+ #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 defined $pr_cell_name2 and
+ length(decode('cp1252',$pr_cell2->{Val})) gt 0 and length(decode('cp
+1252',$pr_cell_name2->{Val})) gt 0)
{
my @pr2 = split(/[,.]+/, d
+ecode('cp1252',$pr_cell2->{Val}));
+ foreach $pr2(@pr2){
+
+ if(@pr2 gt 1)
+ {
+
+ my $materialmapping_item2 = $materialmapp
+ing_table_xml->createElement("item");
+ #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}));
}
+
+ 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(defined $pr_cell3 and
+defined $pr_cell_name3 and length(decode('cp1252',$pr_cell3->{Val}))
+gt 0 and length(decode('cp1252',$pr_cell_name3->{Val})) gt 0)
+ {
+ 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 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);
+ }
}
+ if (($prC2 = $pr_cell2)){
{
+
+ $materialmapping_item = $materialmapping_item2;
+ $materialmapping_table_xml_root->addChild($materialmapping_item2);
+
+ $materialmapping_item2->setAttribute(decode('cp1252',$pr_cell_name2
+->{Val}),$pr2);
+
+ }
+}
+
}
}
}
}
+
+
+ #for every duble 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(defined $pr_cell3 and d
+efined $pr_cell_name3 and length(decode('cp1252',$pr_cell3->{Val})) g
+t 0 and length(decode('cp1252',$pr_cell_name3->{Val})) gt 0)
{
+ 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("item");
+
+
+ #se
+t 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}));
}
+
+
+
+
+
+
+ $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(deco
+de('cp1252',$pr_cell_name4->{Val}),$pr4);
+
+ }
+ }}
+ $materialmapping_item3->setAttribute(decode('cp1252',$pr_ce
+ll_name2->{Val}),$pr2);
+
+ #set internal
$materialmapping_item3->setAttribu
+te("internal",decode('cp1252',$internal_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);
+}
}
}
}
}
}
}
}
}
+
+
+ #for every triple 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(defined $pr_cell3 and d
+efined $pr_cell_name3 and length(decode('cp1252',$pr_cell3->{Val})) g
+t 0 and length(decode('cp1252',$pr_cell_name3->{Val})) gt 0)
{
+ if($prC3 gt $prC2)
+ {
+ my @pr3 = spl
+it(/[,.]+/, decode('cp1252',$pr_cell3->{Val}));
+ foreach $pr3
+(@pr3)
+ {
+
+
+ if(@p
+r3 gt 1)
+ {
+
+
+
+ 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 gt $prC3)&&($prC
+4 gt $prC2))
+ {
+ my @pr4 = spl
+it(/[,.]+/, decode('cp1252',$pr_cell4->{Val}));
+ foreach $pr4
+(@pr4)
+ {
+
+ if(@pr4 gt 1){
+ my $ma
+terialmapping_item4 = $materialmapping_table_xml->createElement("item
+");
+
+ $mate
+rialmapping_item4->setAttribute("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_item4->setAtt
+ribute("item_number",decode('cp1252',$item_number_cell->{Val}));
}
+
+
+
+
+ $materi
+almapping_item4->setAttribute(decode('cp1252',$pr_cell_name4->{Val}),
+$pr4);
+
+ $materialmapping_item4->setAttribute(decode('cp1252',$pr_ce
+ll_name3->{Val}),$pr3);
+ $materialmapping_item4->setAttribute(dec
+ode('cp1252',$pr_cell_name2->{Val}),$pr2);
+
+ for(my $prC5 = $map_Cmin+2; $prC5 <= $map_Cmax ; $prC5++)
+ {
+ $pr_cell_name5 = $oWkS->{C
+ells}[$title_row][$prC5];
+ $pr_cell5 = $oWkS->{Cells}
+[$iR][$prC5];
+ if(($prC5 ne $prC3)&&($prC
+5 ne $prC2)&&($prC5 ne $prC4))
+ {
+ my @pr5 = spl
+it(/[,.]+/, decode('cp1252',$pr_cell5->{Val}));
+ foreach $pr5
+(@pr5)
+ {
+
+
+
+ $materi
+almapping_item4->setAttribute(decode('cp1252',$pr_cell_name5->{Val}),
+$pr5);
+
+ }}}
+
+ #set internal
$materialmapping_item4->setAttribu
+te("internal",decode('cp1252',$internal_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_item4->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_item4->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_item4->se
+tAttribute('brand',$brand_string);
}
if(length($ignored_brand_strin
+g) gt 0){
$materialmapping_item4->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_item4->se
+tAttribute('customer',$customer_string);
}
if(length($ignored_customer_st
+ring) gt 0){
$materialmapping_item4->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_item4->se
+tAttribute('market',$market_string);
}
if(length($ignored_market_stri
+ng) gt 0){
$materialmapping_item4->se
+tAttribute('ignore_market',$ignored_market_string);
+ }
}
+
+ $materialmapping_item = $materialmapping_item4;
+ $materialmapping_table_xml_root->addChild($materialmapping_
+item4);
+}
}
}
}
}
}
}
}
}
}
}
}
}
+
+ #set internal
$materialmapping_item->setAttribut
+e("internal",decode('cp1252',$internal_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 "Imported: $filename \n";
}
$materialmapping_table_xml->toFile($materialmapping_file,2);
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.
