CAE5942
asked on
PHP import script ignoring xml tags containing html
Hi everyone,
In order to import products into our website that was built using Magento, we export an xml file from our point of sale program and this is uploaded to our web server. A php script is then run and a csv file is created. We then use the Magmi php plugin to import the products. The problem I'm having is that if any html is included in the xml file then that xml tag is not imported into Magento. The following is an example of the code in the xml file containing some html:
<LongDescription><![CDATA[
When the import script is run, then the above tag gets ignored. You'll notice in the above snippet that the following tags have been inserted into the xml tag:
<![CDATA[
]]>
These tags were automatically inserted into the xml file when it was exported from our Point of Sale program (they weren't intentionally added) so I'm wondering if anyone could tell me whether it is in fact those tags that are preventing the php import script from accepting the xml tag and importing the information into Magento?
I'd really appreciate any advice.
In order to import products into our website that was built using Magento, we export an xml file from our point of sale program and this is uploaded to our web server. A php script is then run and a csv file is created. We then use the Magmi php plugin to import the products. The problem I'm having is that if any html is included in the xml file then that xml tag is not imported into Magento. The following is an example of the code in the xml file containing some html:
<LongDescription><![CDATA[
When the import script is run, then the above tag gets ignored. You'll notice in the above snippet that the following tags have been inserted into the xml tag:
<![CDATA[
]]>
These tags were automatically inserted into the xml file when it was exported from our Point of Sale program (they weren't intentionally added) so I'm wondering if anyone could tell me whether it is in fact those tags that are preventing the php import script from accepting the xml tag and importing the information into Magento?
I'd really appreciate any advice.
Although... this page says what you have should be working: http://www.w3schools.com/xml/xml_cdata.asp
Looking at the http://wiki.magmi.org/index.php?title=Import_new_products page, it shows standard CSV format... but your text above has double quotes in it and that will break normal CSV parsing.
ASKER
Thanks for the reply,
So from what you're saying it seems that the only thing breaking the code is the double quotes in the inline css - is that right? If yes, if I escaped the doubled quotes as follows would that work?
<LongDescription><![CDATA[
So from what you're saying it seems that the only thing breaking the code is the double quotes in the inline css - is that right? If yes, if I escaped the doubled quotes as follows would that work?
<LongDescription><![CDATA[
Probably not. CSV parsers are usually pretty simple minded. But you can change them to single quotes instead of double quotes. HTML doesn't care. Then the question is whether the Magento database import is written properly. It should be doing a 'mysql_real_escape' of some version which would take care of the quotes.
ASKER
The Magento database import script probably needs tweaking to take care of the quotes but I need a workaround in the meantime so I'll just try the single quotes and will come back to accept the solution. It may not be until tomorrow morning though as the import script runs overnight.
Thanks so much for your advice. I really appreciate it.
Thanks so much for your advice. I really appreciate it.
ASKER
Hi, I tested this out by only including the single quotes in the inline css however it's still not being processed. Do you think that it could be the import script that needs tweaking to allow html in the .csv file?
Can you post a copy of the CSV file that is supposed to be imported that has the problem? Just a small sample.
ASKER
Hi, I have the php script that contains the code to create the csv file but I can't find the actual csv file. I think that maybe after it's been created it gets deleted until the next time the import script is run. I can run the script if you need the csv file but would it help if I posted the php script itself?
No, I think we need to see some data that doesn't work. The script may work fine with correct data.
ASKER
Hi, I've found the csv file and I've included a small portion of it in an excel file. Could you take a look to see what might be causing the issue?
Thanks for your support.
Thanks for your support.
ASKER
Sorry I've now uploaded the file …
products.xlsx
products.xlsx
What you uploaded appears to be ok... but Excel will filter the import. CSV is a plain text format and that's what I need to see. The plain text that is generated and fed to the database. And I specifically need to see lines that are Not currently importing properly.
ASKER
Ok I've uploaded a csv file. It contains 1 product in 3 colours. The first 2 colours are outputting to the csv file because there is no html but the third colour, ie. khaki didn't output the description as I added html.
Thanks again for your help.
Thanks again for your help.
ASKER
See attached for the csv file.
products2.csv
products2.csv
Ok, you've identified the stage at which it fails, the CSV export. Now is the time to post the PHP script that does that to see what's going on that causes it to fail. It would be helpful if you could also post the XML that created the CSV above.
ASKER
Ok great. Below is the code from the php script. Note that I've removed the values from inside the quote marks in the first 4 lines of code for privacy.
Thanks again
Thanks again
<?php
$con = mysql_connect("123.0.0.0”, "", "");
mysql_select_db("");
$project_location = "/home/site.com/html";
$csv_file = $project_location."/var/import/products.csv";
$fp = fopen($csv_file,"w");
/******************************************************************************
* Generate CSV from products table to magento import format
******************************************************************************/
if(!$fp){
echo "Can not create file ";
exit;
} else {
echo "File created";
}
$head = array("store","websites","attribute_set","type","categories","sku","name","supplier_name","style_code","spec_chart","alt_code","status","visibility","enable_googlecheckout","tax_class_id","colour","size","style","gender","price","special_price","description","composition","special_from_date","qty","manage_stock","product_name","store_id","colour_groups","configurable_attributes","simples_skus","swatch_path","is_in_stock","image","small_image","thumbnail","re_skus","media_gallery","us_skus","colour_clone");
fputcsv($fp, $head);
//simple products
$sql = "SELECT * FROM products p left join products_sizes ps on ps.Sku = p.Sku WHERE p.status = 0";
$result = mysql_query($sql);
// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $sql;
die($message);
}
while($rs = mysql_fetch_assoc($result) ){
$price = $rs['Price'];
$specialprice = $rs['SpecialPrice'];
if( intval($rs['Price']) <= 0 ){
$price = $rs['SpecialPrice'];
$specialprice = '';
$rs['SpecialPriceStart'] = '';
$rs['SpecialPriceEnd'] = '';
}
if( $specialprice <= 0 ){
$specialprice = '';
$rs['SpecialPriceStart'] = '';
$rs['SpecialPriceEnd'] = '';
}
$ColourGroup = ucwords(strtolower(str_replace("/","/ ",$rs['ColourGroup'])));
$rs['ColourGroup'] = str_replace('/ ','/',$ColourGroup);
$swatch_image = " ";
if($rs['SizeName']!='N/A')
$SizeName="-".$rs['SizeName'];
elseif($rs['SizeName']=='N/A')
$rs['SizeName']='';
$name = $rs['ProductTitle'].'-'.ucwords(strtolower($rs['ColourName'])).$SizeName;
$store = 0;
$rs['ColourName'] = ucwords(strtolower($rs['ColourName']));
if($rs['WebDepartment']=='Bow Ties' || $rs['WebDepartment']=='Formal Ties' || $rs['WebDepartment']=='Vests' || $rs['WebDepartment']=='Belts' || $rs['WebDepartment']=='Handkerchiefs' || $rs['WebDepartment']=="Big Men's Vests"){
$rs['QtyOnHand']=10000;
}
$swatch_image = " ";
if( $rs['SwatchImage'] != '' ){
$swatch_image = $project_location. "/new_media/".$rs['SupplierName'].'/swatches/'.$rs['SwatchImage'];
}
$product_image = " ";
if( $rs['ProductImage'] != '' ){
$product_image = $project_location."/new_media".$rs['ProductImage'];
}
$alternate_image = " ";
if( $rs['AlternativeImage'] != "" ){
$al_prs = explode(";", $rs['AlternativeImage']);
$alternate_image = $product_image.";";
for($al_pr=0; $al_pr < count($al_prs); $al_pr++){
if (strpos(trim($al_prs[$al_pr]),'.jpg') !== false) {
$alternate_image .= $project_location."/new_media/".$rs['SupplierName'].'/'.str_replace(" ", "-", trim($al_prs[$al_pr])).";";
}
else
{
$alternate_image .= $project_location."/new_media/".$rs['SupplierName'].'/'.str_replace(" ", "-", trim($al_prs[$al_pr])).".jpg;";
}
}
$alternate_image = substr($alternate_image, 0, -1);
}
if( $rs['AlternativeImage2'] != "" ){
$al_prs2 = explode(";", $rs['AlternativeImage2']);
$alternate_image = $alternate_image.";";
for($al_pr2=0; $al_pr2 < count($al_prs2); $al_pr2++){
if (strpos(trim($al_prs2[$al_pr2]),'.jpg') !== false) {
$alternate_image .= $project_location."/new_media/".$rs['SupplierName'].'/'.str_replace(" ", "-", trim($al_prs2[$al_pr2])).";";
}
else
{
$alternate_image .= $project_location."/new_media/".$rs['SupplierName'].'/'.str_replace(" ", "-", trim($al_prs2[$al_pr2])).".jpg;";
}
}
$alternate_image = substr($alternate_image, 0, -1);
}
$instock = ($rs['QtyOnHand'] < 3)?0:1;
if($rs['Code']=='')
$rs['Code']=$rs['Sku'];
$store = 0;
$ar = array('admin','base','Default','simple', '',$rs['Code'],$name,$rs['SupplierName'],'',$rs['SpecChart'],
'','1','1','1','New Tax Rule',$rs['ColourName'],$rs['SizeName'],
'', '',$price,$specialprice,$rs['LongDescription'],$rs['Composition'],$rs['SpecialPriceStart'],
$rs['QtyOnHand'],0,$name,$store, $rs['ColourGroup'],'','',$swatch_image,$instock,$product_image,$product_image,$product_image,'',$alternate_image,'','');
fputcsv($fp, $ar);
}
//Configurable products
$sql = "SELECT p.* FROM products p group by p.Style having p.status = 0";
$results = mysql_query($sql) or die(mysql_error());
$rs = array();
$main_sku = '';
while($rs = mysql_fetch_assoc($results) ){
$sql_simple = "SELECT group_concat(BarCode) as simpleskus, group_concat(SizeName) as colorsizes FROM `products_sizes`where Style=".$rs['Style'];
$res_simple = mysql_query($sql_simple);
$simple_sku='';
if(mysql_num_rows($res_simple)>0){
$res_arr = mysql_fetch_array($res_simple);
$simple_sku = $res_arr['simpleskus'];
if(str_replace('N/A,','',$res_arr['colorsizes'])!='N/A')
$config_attr="colour,size";
else
$config_attr="colour";
}
$sql_colors = "SELECT group_concat(ColourGroup) as colornames FROM `products`where Style=".$rs['Style'];
$res_colors = mysql_query($sql_colors);
$color_names='';
if(mysql_num_rows($res_colors)>0){
$res_arr = mysql_fetch_array($res_colors);
$color_names = ucwords(strtolower(str_replace(",",", ",$res_arr['colornames'])));
$color_names = ucwords(strtolower(str_replace("/","/ ",$color_names)));
$color_names = str_replace('/ ','/',$color_names);
$color_names = str_replace(', ',',',$color_names);
//$color_names = $res_arr['colornames'];
}
$price = $rs['Price'];
$specialprice = $rs['SpecialPrice'];
if( intval($rs['Price']) <= 0 ){
$price = $rs['SpecialPrice'];
$specialprice = '';
$rs['SpecialPriceStart'] = '';
$rs['SpecialPriceEnd'] = '';
}
$ColourGroup = ucwords(strtolower(str_replace("/","/ ",$rs['ColourGroup'])));
$rs['ColourGroup'] = str_replace('/ ','/',$ColourGroup);
if( $specialprice <= 0 ){
$specialprice = '';
$rs['SpecialPriceStart'] = '';
$rs['SpecialPriceEnd'] = '';
}
$swatch_image = " ";
if( $rs['SwatchImage'] != '' ){
$swatch_image = $project_location. "/new_media/".$rs['SupplierName'].'/swatches/'.$rs['SwatchImage'];
}
$product_image = " ";
if( $rs['ProductImage'] != '' ){
$product_image = $project_location."/new_media".$rs['ProductImage'];
}
$rel_items = " ";
if( trim($rs['SuggestedItems']) != ''){
$rel_items = str_replace(';',',',substr($rs['SuggestedItems'],0,-1));
}
$alternate_image = " ";
if( trim($rs['AlternativeImage']) != "" ){
$al_prs = explode(";", $rs['AlternativeImage']);
$alternate_image = "";
for($al_pr=0; $al_pr < count($al_prs); $al_pr++){
if (strpos(trim($al_prs[$al_pr]),'.jpg') !== false) {
$alternate_image .= $project_location."/new_media/".$rs['SupplierName'].'/'.str_replace(" ", "-", trim($al_prs[$al_pr])).";";
}
else
{
$alternate_image .= $project_location."/new_media/".$rs['SupplierName'].'/'.str_replace(" ", "-", trim($al_prs[$al_pr])).".jpg;";
}
}
$alternate_image = substr($alternate_image, 0, -1);
}
if( trim($rs['AlternativeImage2']) != "" ){
$al_prs2 = explode(";", $rs['AlternativeImage2']);
$alternate_image = $alternate_image.";";
for($al_pr2=0; $al_pr2 < count($al_prs2); $al_pr2++){
if (strpos(trim($al_prs2[$al_pr2]),'.jpg') !== false) {
$alternate_image .= $project_location."/new_media/".$rs['SupplierName'].'/'.str_replace(" ", "-", trim($al_prs2[$al_pr2])).";";
}
else
{
$alternate_image .= $project_location."/new_media/".$rs['SupplierName'].'/'.str_replace(" ", "-", trim($al_prs2[$al_pr2])).".jpg;";
}
}
$alternate_image = substr($alternate_image, 0, -1);
}
$category_id = '';
$name = $rs['ProductTitle'];
$store = 0;
$gender="";
if($rs['Ranges'] == "corp" || $rs['Ranges'] == 'Healthcare' || $rs['Ranges'] == 'Beauty' || $rs['Ranges'] == 'Hospitality' || $rs['Ranges'] == 'Workwear'){
$category_id = 'corp Brochure#'.ucwords(strtolower($rs['Category'])).'#'.ucwords(strtolower($rs['WebDepartment']));
$gender = str_replace('corp ','',$rs['GroupName']);
}elseif($rs['Ranges'] == "Online"){
$category_id = 'Online Store#'.ucwords(strtolower($rs['Category'])).'#'.ucwords(strtolower($rs['WebDepartment']));
$gender = '';
}else{
$ranges = explode('; ',$rs['Ranges']);
foreach($ranges as $range){
if( $range == "Online" ){
$category_id .= 'Online Store#'.ucwords(strtolower($rs['Category'])).'#'.ucwords(strtolower($rs['WebDepartment'])).';;';
}else if( $range == "corp" ){
$category_id .= 'corp Brochure#corp#'.ucwords(strtolower($rs['WebDepartment'])).';;';
}else if( $range == “Range1” || $range == "Range2” || $range == "Range3” || $range == "Range4”){
$category_id .= 'corp Brochure#'.ucwords(strtolower($range)).'#'.ucwords(strtolower($rs['WebDepartment'])).';;';
}
}
$category_id = substr($category_id, 0, -2);
#$category_id = 'Online Store#'.ucwords(strtolower($rs['Category'])).'#'.ucwords(strtolower($rs['WebDepartment'])).';;corp Brochure#corp#'.ucwords(strtolower($rs['WebDepartment']));
$gender = str_replace('corp ','',$rs['MajorName']);
}
$upsell_rs = "-re::1";
if( trim($rs['upsells']) != "" ){
$upsell_rs = "-re::1,".$rs['upsells'];
}
$ar = array('admin','base','Default','configurable',$category_id,$rs['Style'],$name,$rs['SupplierName'],$rs['Style'],
$rs['SpecChart'],
$rs['AltCode'],'1','4','1','New Tax Rule','','',
'', $gender,$price,$specialprice,$rs['LongDescription'],$rs['Composition'],$rs['SpecialPriceStart'],
0,1,$name,0, $rs['ColourGroup'],$config_attr,$simple_sku,$swatch_image,1,$product_image,$product_image,
$product_image,$rel_items,$alternate_image,$upsell_rs,$color_names);
fputcsv($fp, $ar);
}
fclose($fp);
?>ASKER
The xml tag that holds the description is called 'LongDescription' and it can be found in the following lines of code:
and
'', '',$price,$specialprice,$rs['LongDescription'],$rs['Composition'],$rs['SpecialPriceStart'],and
'', $gender,$price,$specialprice,$rs['LongDescription'],$rs['Composition'],$rs['SpecialPriceStart'],
I don't see an XML file or a PHP function to convert the XML in any of that. ??
ASKER
The code below is a sample of the xml file relating to the 3 products that were in the csv file. Is that ok?
<?xml version="1.0" standalone="yes"?>
<ItemsDataSet>
<TemplateItem>
<Style>5721</Style>
<Sku>5721002</Sku>
<AltCode />
<Name>MENS COTTON CHINOS BLACK</Name>
<LongDescription>A flat front trouser with two slant pockets and a fob pocket at the front and two button-through hip pockets at the rear. </LongDescription>
<SupplierCode>10</SupplierCode>
<SupplierName>NEW SUPPLIER</SupplierName>
<Major>1</Major>
<MajorName>MENS</MajorName>
<GroupCode>5</GroupCode>
<GroupName>TROUSERS</GroupName>
<ClassCode>502</ClassCode>
<ClassName>PURE COTTON TROUSERS</ClassName>
<WebDepartment>Trousers</WebDepartment>
<BrandCode>999</BrandCode>
<BrandName>OPEN BRAND</BrandName>
<SeasonCode>999</SeasonCode>
<SeasonName>OPEN SEASON</SeasonName>
<FirstSaleDate>27/06/2012</FirstSaleDate>
<LastChangeDate>02/12/2014</LastChangeDate>
<Catalog>1</Catalog>
<HotList />
<ColourCode>002</ColourCode>
<ColourName>BLACK</ColourName>
<ColourGroup>BLACK/CHARCOAL</ColourGroup>
<Price> 89.00</Price>
<SpecialPrice> 69.00</SpecialPrice>
<SpecialPriceStart>26/11/2014</SpecialPriceStart>
<SpecialPriceEnd>23/11/2024</SpecialPriceEnd>
<MultiSellPrice />
<OtherSellPrice />
<SuggestedItems>1078;9795;1126;1164;1158;</SuggestedItems>
<AlternativeItems>1126;</AlternativeItems>
<AddOnItems>1126;1078;9795;2237;1720;2282;1171;1164;5116;4744;</AddOnItems>
<RecommendedItems/>
<ProductTitle> Men's Cotton Chinos</ProductTitle>
<ProductImage>5721002_Black.jpg</ProductImage>
<Category>Men</Category>
<AlternativeImage2 />
<Composition>100% Cotton</Composition>
<Ranges>Online</Ranges>
<CorporateRelated />
<AlternativeImage>5721002_Black_Side;5721002_Black_Back</AlternativeImage>
<SwatchImage />
<SpecChart>31</SpecChart>
<Item>
<Code>572100202</Code>
<BarCode>572100202</BarCode>
<SizeCode>2</SizeCode>
<SizeName>30</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100204</Code>
<BarCode>572100204</BarCode>
<SizeCode>4</SizeCode>
<SizeName>32</SizeName>
<QtyOnHand>3</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100206</Code>
<BarCode>572100206</BarCode>
<SizeCode>6</SizeCode>
<SizeName>34</SizeName>
<QtyOnHand>3</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100207</Code>
<BarCode>572100207</BarCode>
<SizeCode>7</SizeCode>
<SizeName>36</SizeName>
<QtyOnHand>3</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100208</Code>
<BarCode>572100208</BarCode>
<SizeCode>8</SizeCode>
<SizeName>38</SizeName>
<QtyOnHand>2</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100209</Code>
<BarCode>572100209</BarCode>
<SizeCode>9</SizeCode>
<SizeName>40</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100210</Code>
<BarCode>572100210</BarCode>
<SizeCode>10</SizeCode>
<SizeName>42</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100211</Code>
<BarCode>572100211</BarCode>
<SizeCode>11</SizeCode>
<SizeName>44</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100212</Code>
<BarCode>572100212</BarCode>
<SizeCode>12</SizeCode>
<SizeName>46</SizeName>
<QtyOnHand>2</QtyOnHand>
<QtyOnOrder/>
</Item>
</TemplateItem>
<TemplateItem>
<Style>5721</Style>
<Sku>5721003</Sku>
<AltCode />
<Name>MENS COTTON CHINOS NAVY</Name>
<LongDescription>A flat front trouser with two slant pockets and a fob pocket at the front and two button-through hip pockets at the rear. </LongDescription>
<SupplierCode>10</SupplierCode>
<SupplierName>NEW SUPPLIER</SupplierName>
<Major>1</Major>
<MajorName>MENS</MajorName>
<GroupCode>5</GroupCode>
<GroupName>TROUSERS</GroupName>
<ClassCode>502</ClassCode>
<ClassName>PURE COTTON TROUSERS</ClassName>
<WebDepartment>Trousers</WebDepartment>
<BrandCode>999</BrandCode>
<BrandName>OPEN BRAND</BrandName>
<SeasonCode>999</SeasonCode>
<SeasonName>OPEN SEASON</SeasonName>
<FirstSaleDate>07/05/2012</FirstSaleDate>
<LastChangeDate>02/12/2014</LastChangeDate>
<Catalog>1</Catalog>
<HotList />
<ColourCode>003</ColourCode>
<ColourName>NAVY</ColourName>
<ColourGroup>BLUE</ColourGroup>
<Price> 89.00</Price>
<SpecialPrice> 69.00</SpecialPrice>
<SpecialPriceStart>26/11/2014</SpecialPriceStart>
<SpecialPriceEnd>23/11/2024</SpecialPriceEnd>
<MultiSellPrice />
<OtherSellPrice />
<SuggestedItems>1078;9795;1126;1164;1158;</SuggestedItems>
<AlternativeItems>1126;</AlternativeItems>
<AddOnItems>1126;1078;9795;2237;1720;2282;1171;1164;5116;4744;</AddOnItems>
<RecommendedItems/>
<ProductTitle> Men's Cotton Chinos</ProductTitle>
<ProductImage>5721003_Navy.jpg</ProductImage>
<Category>Men</Category>
<AlternativeImage2 />
<Composition>100% Cotton</Composition>
<Ranges>Online</Ranges>
<CorporateRelated />
<AlternativeImage>5721003_Navy_Side;5721003_Navy_Back;5721003_Navy_GM</AlternativeImage>
<SwatchImage />
<SpecChart>31</SpecChart>
<Item>
<Code>572100302</Code>
<BarCode>572100302</BarCode>
<SizeCode>2</SizeCode>
<SizeName>30</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100304</Code>
<BarCode>572100304</BarCode>
<SizeCode>4</SizeCode>
<SizeName>32</SizeName>
<QtyOnHand/>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100306</Code>
<BarCode>572100306</BarCode>
<SizeCode>6</SizeCode>
<SizeName>34</SizeName>
<QtyOnHand>2</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100307</Code>
<BarCode>572100307</BarCode>
<SizeCode>7</SizeCode>
<SizeName>36</SizeName>
<QtyOnHand/>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100309</Code>
<BarCode>572100309</BarCode>
<SizeCode>9</SizeCode>
<SizeName>40</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100312</Code>
<BarCode>572100312</BarCode>
<SizeCode>12</SizeCode>
<SizeName>46</SizeName>
<QtyOnHand>2</QtyOnHand>
<QtyOnOrder/>
</Item>
</TemplateItem>
<TemplateItem>
<Style>5721</Style>
<Sku>5721064</Sku>
<AltCode />
<Name>MENS COTTON CHINOS KHAKI</Name>
<LongDescription><![CDATA[<p>A flat front trouser with two slant pockets and a fob pocket at the front and two button-through hip pockets at the rear.</p> <p><strong>SPECIAL PROMOTION:</strong> Enter Promo Code 12345 at checkout.</p> ]]></LongDescription>
<SupplierCode>10</SupplierCode>
<SupplierName>NEW SUPPLIER</SupplierName>
<Major>1</Major>
<MajorName>MENS</MajorName>
<GroupCode>5</GroupCode>
<GroupName>TROUSERS</GroupName>
<ClassCode>502</ClassCode>
<ClassName>PURE COTTON TROUSERS</ClassName>
<WebDepartment>Trousers</WebDepartment>
<BrandCode>999</BrandCode>
<BrandName>OPEN BRAND</BrandName>
<SeasonCode>999</SeasonCode>
<SeasonName>OPEN SEASON</SeasonName>
<FirstSaleDate>07/05/2012</FirstSaleDate>
<LastChangeDate>02/12/2014</LastChangeDate>
<Catalog>1</Catalog>
<HotList />
<ColourCode>064</ColourCode>
<ColourName>KHAKI</ColourName>
<ColourGroup>GREEN</ColourGroup>
<Price> 89.00</Price>
<SpecialPrice> 69.00</SpecialPrice>
<SpecialPriceStart>26/11/2014</SpecialPriceStart>
<SpecialPriceEnd>23/11/2024</SpecialPriceEnd>
<MultiSellPrice />
<OtherSellPrice />
<SuggestedItems>1078;9795;1126;1164;1158;</SuggestedItems>
<AlternativeItems>1126;</AlternativeItems>
<AddOnItems>1126;1078;9795;2237;1720;2282;1171;1164;5116;4744;</AddOnItems>
<RecommendedItems/>
<ProductTitle> Men's Cotton Chinos</ProductTitle>
<ProductImage>5721064_Khaki.jpg</ProductImage>
<Category>Men</Category>
<AlternativeImage2 />
<Composition>100% Cotton</Composition>
<Ranges>Online</Ranges>
<CorporateRelated />
<AlternativeImage>5721064_Khaki_Side;5721064_Khaki_Back;5721064_Khaki_GM</AlternativeImage>
<SwatchImage />
<SpecChart>31</SpecChart>
<Item>
<Code>572106404</Code>
<BarCode>572106404</BarCode>
<SizeCode>4</SizeCode>
<SizeName>32</SizeName>
<QtyOnHand>3</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572106406</Code>
<BarCode>572106406</BarCode>
<SizeCode>6</SizeCode>
<SizeName>34</SizeName>
<QtyOnHand>3</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572106407</Code>
<BarCode>572106407</BarCode>
<SizeCode>7</SizeCode>
<SizeName>36</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572106408</Code>
<BarCode>572106408</BarCode>
<SizeCode>8</SizeCode>
<SizeName>38</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572106409</Code>
<BarCode>572106409</BarCode>
<SizeCode>9</SizeCode>
<SizeName>40</SizeName>
<QtyOnHand>6</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572106410</Code>
<BarCode>572106410</BarCode>
<SizeCode>10</SizeCode>
<SizeName>42</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572106411</Code>
<BarCode>572106411</BarCode>
<SizeCode>11</SizeCode>
<SizeName>44</SizeName>
<QtyOnHand>2</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572106412</Code>
<BarCode>572106412</BarCode>
<SizeCode>12</SizeCode>
<SizeName>46</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
</TemplateItem>
</ItemsDataSet>
That's what I was looking for. I opened it in Microsoft's XML Notepad and it was fine. No errors. When I saved it, it had the same exact content except that it was indented by sections.
I'm going to have get some sleep but the next I want to know is which program converts this into CSV. I'll check back tomorrow but I'm done for tonight.
I'm going to have get some sleep but the next I want to know is which program converts this into CSV. I'll check back tomorrow but I'm done for tonight.
ASKER
Hi, I think the following script imports the products from the xml:
<?php
ini_set("display_errors", 1);
$con = mysql_connect("", "", "");
mysql_select_db("");
$location = "/home/site.com/html";
$project_location = $location."/media";
function XML2Array($XmlFilePath) {
if (!file_exists($XmlFilePath))
return FALSE;
$Class = Array ();
$Class['UsersFileXml'] = file_get_contents($XmlFilePath);
$Class['FileXml'] = simplexml_load_string($Class['UsersFileXml']);
$Class['FileJson'] = json_encode($Class['FileXml']);
$Array = json_decode($Class['FileJson'], TRUE);
unset ($Class);
return $Array;
}
function dateformat($dateval) {
list ($day, $month, $year) = explode("/", $dateval);
return $year . '-' . $month . '-' . $day;
}
/***********************************************************
* Move data into backup tables
* *********************************************************/
mysql_query("truncate table `products_old`");
mysql_query("truncate table `products_sizes_old`");
mysql_query("insert into products_old SELECT * FROM `products`");
mysql_query("insert into products_sizes_old SELECT * FROM `products_sizes`");
mysql_query("truncate table `products`");
mysql_query("truncate table `products_sizes`");
/************** Move data into backup tables *********************/
$array = XML2Array($location.'/folder/products.xml');
foreach ($array['TemplateItem'] as $product) {
$index_key = '';
$val_str = '';
$sku = $style = $ProductImage = $SupplierName = '';
foreach ($product as $key => $value) {
if (!is_array($value)) {
if ($key == 'Sku') {
$sku = $value;
}
if ($key == 'Style') {
$style = $value;
}
if($key == 'ProductImage')
$ProductImage = $value;
if($key == 'SupplierName')
$SupplierName = $value;
switch ($key) {
case 'FirstSaleDate' :
case 'LastChangeDate' :
case 'SpecialPriceEnd' :
case 'SpecialPriceEnd' :
case 'SpecialPriceStart' :
$value = dateformat($value);
break;
case 'AltCode':
$v = $value[0];
if( strtolower($v) == 's' ){
$value = substr($value, 1);
}
break;
}
$index_key = $index_key . $key . ', ';
if(trim($value)!='')
$val_str = $val_str . "'" . mysql_real_escape_string(trim($value)) . "', ";
else
$val_str = $val_str . "'', ";
} else {
if ($key == 'Item') {
if($value['Code']!=''){
if(is_array($value['QtyOnHand'])){
$value['QtyOnHand']=0;
}
if(is_array($value['SizeName']))
$value['SizeName']="N/A";
$item_index_key = 'Style, Sku, Code, BarCode, SizeCode, SizeName, QtyOnHand';
$item_val_str = $style. "," . $sku . ",".$value['Code'].",".$value['BarCode'].",".$value['SizeCode'].",'".$value['SizeName']."',".$value['QtyOnHand'];
//echo"<br />"."INSERT INTO products_sizes (" . $item_index_key . ") VALUES (" . $item_val_str . ")";
mysql_query("INSERT INTO products_sizes (" . $item_index_key . ") VALUES (" . $item_val_str . ")");
}else{
foreach ($value as $itemkey => $items) {
if(is_array($items['QtyOnHand'])){
$items['QtyOnHand']=0;
}
$item_index_key = 'Style, Sku, Code, BarCode, SizeCode, SizeName, QtyOnHand';
$item_val_str = $style. "," . $sku . ",".$items['Code'].",".$items['BarCode'].",".$items['SizeCode'].",'".$items['SizeName']."',".$items['QtyOnHand'];
//echo"<br />"."INSERT INTO products_sizes (" . $item_index_key . ") VALUES (" . $item_val_str . ")";
mysql_query("INSERT INTO products_sizes (" . $item_index_key . ") VALUES (" . $item_val_str . ")");
}
}
}
}
//}
}
if($ProductImage!='')
$SupplierProductImage = '/'.$SupplierName.'/'.$ProductImage;
else
$SupplierProductImage='';
$find_array = array('_','-',' ');
$replace_array = array('','','');
$SupplierProductImage = strtolower(str_replace($find_array,$replace_array,$SupplierProductImage));
//$index_key = substr(trim($index_key), 0, -1);
//$val_str = substr(trim($val_str), 0, -1);
$index_key = $index_key .'SupplierProductImage';
$val_str = $val_str ."'".$SupplierProductImage."'";
mysql_query("INSERT INTO products (" . $index_key . ") VALUES (" . $val_str . ")");
}
/**************************************************************************************
* Import Image Names from folders to database
**************************************************************************************/
mysql_query("truncate table products_images");
function listFolderFiles($dir,$project_location) {
$ffs = scandir($dir);
foreach ($ffs as $ff) {
if ($ff != '.' && $ff != '..' && $ff!='.DS_Store') {
if (is_dir($dir . '/' . $ff)){
listFolderFiles($dir . '/' . $ff,$project_location);
}else{
$convertimage = strtolower(str_replace($project_location,'',$dir.'/'.$ff));
$find_array = array('_','-',' ');
$search_array = array('','','');
$convertimage = str_replace($find_array,$search_array,$convertimage);
$sql="insert into products_images(origimage,convertimage) values('".$dir."/".$ff."','".$convertimage."')";
mysql_query($sql);
}
}
}
}
listFolderFiles($project_location,$project_location);
mysql_query("UPDATE products_images SET origimage = REPLACE (origimage,'".$project_location."','')");
mysql_query("update products_images pi, products p set p.ProductImage=pi.origimage WHERE pi.convertimage=p.SupplierProductImage");
/*********************************************************************
* Up Cell products
*********************************************************************/
$sql_upcell = "select * from products where CorpRelated!=''";
$res_upcell = mysql_query($sql_upcell);
while($row_upcell = mysql_fetch_assoc($res_upcell)){
$sql_altcode="SELECT group_concat(Style) as upcell_products FROM `products` WHERE `AltCode` in ('".str_replace("; ","','",$row_upcell['CorpRelated'])."')";
$res_altcode = mysql_query($sql_altcode);
$rows_altcode = mysql_num_rows($res_altcode);
if($rows_altcode){
$row_altcode = mysql_fetch_assoc($res_altcode);
mysql_query("update products set upsells='".$row_altcode['upcell_products']."' where Style=".$row_upcell['Style']);
}
}
/**********************************************************************
* Update category IDs into import data
* ********************************************************************/
mysql_query("update `products` set Category=Ranges WHERE Ranges='Range1'");
mysql_query("update `products` set Category=Ranges WHERE Ranges='Range2'");
mysql_query("update `products` set Category=Ranges WHERE Ranges='Range3'");
mysql_query("update `products` set Category=Ranges WHERE Ranges='Range4'");
mysql_query("update `products` set Category='Corp' WHERE Ranges='Corp'");
mysql_query("update `catalog_category_entity_varchar` cpev, `catalog_category_entity` cpe, products p set p.category_id=cpe.entity_id WHERE cpe.entity_id=cpev.entity_id and cpev.attribute_id=41 and cpev.`value`=p.Category and cpe.level=3");
mysql_query("update `catalog_category_entity_varchar` cpev, `catalog_category_entity` cpe, products p set p.sub_category_id=cpe.entity_id WHERE cpe.entity_id=cpev.entity_id and cpev.attribute_id=41 and cpev.`value`=p.WebDepartment and cpe.level=4");
mysql_query("update products set status=0, active='0' where 1");
mysql_query("update products_old set status=0, active='0' where 1");
/******************************************************************
* ********Disabled the not found products in xml file ************
******************************************************************/
mysql_query("update products p, products_old po set po.active='1' where p.Sku=po.Sku");
mysql_query("update products_sizes ps, products_sizes_old pso set pso.active='1',ps.active='1' where ps.BarCode=pso.BarCode");
mysql_query("update products_old po, catalog_product_entity cpe, catalog_product_entity_int cpei set cpei.value=2 where po.Sku=cpe.sku and cpe.entity_id=cpei.entity_id and cpei.attribute_id =96 and po.active='0'");
mysql_query("update products_sizes_old pso, catalog_product_entity cpe, catalog_product_entity_int cpei set cpei.value=2 where pso.BarCode=cpe.sku and cpe.entity_id=cpei.entity_id and cpei.attribute_id =96 and pso.active='0'");
//Update the Modified products in products table
//mysql_query("update products p, products_old po set p.status=1, po.status=1 where p.LastChangeDate=po.LastChangeDate and p.Sku=po.Sku");
mysql_query("update products p, products_old po set p.status=0 where p.Style=po.Style and po.status=0");
mysql_query("update products p, products_sizes ps set p.status=0 where p.Style=ps.Style and ps.active='0'");
/**************** Update category IDs into import data **************************/
?>ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This is my test program without any of the MySQL code.
<?php
ini_set("display_errors", 1);
// XML2Array
function XML2Array($XmlFilePath) {
global $jsonarray;
global $zimplexml;
if (!file_exists($XmlFilePath))
return FALSE;
$Class = Array ();
$Class['UsersFileXml'] = file_get_contents($XmlFilePath);
$Class['FileXml'] = simplexml_load_string($Class['UsersFileXml']);
$zimplexml = $Class['FileXml'];
$Class['FileJson'] = json_encode($Class['FileXml']);
$jsonarray = $Class['FileJson'];
$Array = json_decode($Class['FileJson'], TRUE);
unset ($Class);
return $Array;
}
function dateformat($dateval) {
list ($day, $month, $year) = explode("/", $dateval);
return $year . '-' . $month . '-' . $day;
}
// get XML into an array
$array = XML2Array('products2.xml');
echo "<pre>";
//var_dump($array);
//var_dump($jsonarray);
var_dump($zimplexml);
echo "</pre>";
?>ASKER
Ok I may need to have a php developer look into this as my knowledge is very limited. Thanks for your help.
You're welcome, glad to help.
This page shows how to 'escape' them: http://support.esri.com/cn/knowledgebase/techarticles/detail/23973
In PHP, htmlentities() can do the conversion for you and can be limited to XML1 requirements.
http://php.net/manual/en/function.htmlentities.php The decoding function and related functions are listed on that page.