Sunday, October 3, 2010

Create Google Data Feed from Yahoo Store XML File using only Excel 2007

Create Google Data Feed from Yahoo Store XML File

Using Microsoft Excel 2007
 -- Using formulas,  VLOOKUP and CONCATENATE

1. Save the Catalog XML file from the Yahoo Store Manager -> Search Engines Section

2. Open the file in Excel 2007

3.  Save as an Excel Spreadsheet.

4.  In Excel:
  • PART 1 - Clean up the file
  •   You can turn off the data filter
  •   You will get a HEADER like this:
    StoreID StoreName PublishTimestamp ID ID2 Type ID3 Type4 Value ID5 TableID TableFieldID Value6 TableFieldID7 Key Value8
  • Delete the first 9 columns. - Up to ID5.   ID5 contains the unique product IDs that Google feed can use as the id.
  • PART 2 -- CLEAN the image link and the description fileds.
  • Sort by "Value 6" (the data column)
  •  -- Look for all the " image tags  <  i  m  g ....  
  • cut and paste them into the next sheet -- temporarily.
  •   -  use text to column to cut out the link only and remove the HTML image tag.  delimit by equals (=) sign.
  •      then search and replace with blank the last > at the end of the tag.
  •     being careful not loose your place, paste back the correct and cleaned image urls.

  to be continued ...  

 Contact me if you need more information on how the get this Google feed converted.

Google Data Feed Specifications:

Tried some XML editors and converters but they were too complicated.  This XML editor has a free 30 day trial if you want to try it:   XML Editor

No comments: