Thread: Need to Get Export of Product URL & SKU

What is the easiest way for me to get an export of all my product skus and a URL for each? Is there a simple SQL query for this?

Thanx!

What kind of format do you need it in? You could use WSI to pull it out in XML, you could use SQL to retrieve in table format and could save to a csv file which you could open in notepad or excel, or you could run a pricing export which would give you the SKU (and a few other fields), or you could write an xmlpackage that you could load on a page in the admin site that would retrieve the information.

The only tricky part here (depending on how you want the data formated and how you choose to do this) may be with the URL. The URLs are built dynamically based off of the type (for product this is "p"), the product id, and the sename of the product. If you're using version 9 and custom routing rules, then you may also have custom rules set up which you'll need to honor...in which case the best option here will be WSI (using an xmlpackage to format the data and build the links) or an xmlpackage that you can load into a page and browse to from the admin site so that you can properly tie into the routing engine and get the correct URLs.

I was hoping to use a SQL query to pull this export to end up in a .CSV file. I can easily pull the product SKU from the products table, but am unclear on how to pull the corresponding URL. I need to matche to the SKU (productID) to it's URL.

You'll have to build the URL manually based off of the data in the product record. For a relative URL you would use something like

Code:

select SKU, 'p-' + cast(ProductID as nvarchar) + '-' + SEName + '.aspx' as URL from dbo.Product

which provides results like

Code:

SKU	URL
01-0001	p-1-playstation-3.aspx
01-0002	p-2-playsstation-portablepsp.aspx
01-0003	p-3-nintendo-ds-lite.aspx
01-0004	p-4-team-up-nascar-rc-cars.aspx
01-0005	p-5-megatech-megabotz-rc-artificial-intelligence-battle-vehicles.aspx
01-0006	p-6-megatech-micro-fly-rc-featherweight-helicopter.aspx
01-0007	p-7-megatech-airstrike-electric-powered-free-flight-airplane.aspx

If you want the absolute URL make sure to include that in the select statement

Code:

select SKU, 'http://localhost/Ashland/p-' + cast(ProductID as nvarchar) + '-' + SEName + '.aspx' as URL from dbo.Product

so that you get the full URL in the result set

Code:

SKU	URL
01-0001	http://localhost/Ashland/p-1-playstation-3.aspx
01-0002	http://localhost/Ashland/p-2-playsstation-portablepsp.aspx
01-0003	http://localhost/Ashland/p-3-nintendo-ds-lite.aspx
01-0004	http://localhost/Ashland/p-4-team-up-nascar-rc-cars.aspx
01-0005	http://localhost/Ashland/p-5-megatech-megabotz-rc-artificial-intelligence-battle-vehicles.aspx
01-0006	http://localhost/Ashland/p-6-megatech-micro-fly-rc-featherweight-helicopter.aspx
01-0007	http://localhost/Ashland/p-7-megatech-airstrike-electric-powered-free-flight-airplane.aspx

Thanks George! This worked perfectly!

George, will this be pulling all live and previously existing URLs whether or not our products are live and published in the SAP? For example - URLs for products that might have sold already but might come back again?

I'm trying to gather all of my URLs to do a 301 redirect to our new site URLs but I want to make sure this export includes not only URLs that are live on the site but also ones that are not live.

Thanks!

That will retrieve anything from your dbo.product table, yes. If you want to include only published product or the like, just add a whereclause in the query (e.g. where Published = 1)