Official BV Software Blog

Ecommerce New and Updates - BV Commerce

Generate a Customer List for BV Commerce 5

by mmcconnell1618 26. March 2008 15:50

If you've ever needed to get a quick export of everyone who has purchased from your store in the last year this SQL script can be used to generate a comma separated text file. Make sure you change the "> date" part to be the correct starting point for your export. You'll get customers' names, address, phone and email as long as they placed an order on your store.

 

SELECT 'LastName, FirstName, Line1, Line2, City,

        RegionName, PostalCode, CountryName,

        Phone, Email' AS Expr1, 0 AS [Order]

UNION

SELECT   CAST(CAST(AddressBook AS xml ).query(N'/AddressBook/Address[last()]/LastName/text()') AS nvarchar(1000))

+ ', ' + CAST(CAST(AddressBook AS xml ).query(N'/AddressBook/Address[last()]/FirstName/text()') AS nvarchar(1000))

+ ', ' + CAST(CAST(AddressBook AS xml ).query(N'/AddressBook/Address[last()]/Line1/text()') AS nvarchar(1000))

+ ', ' + CAST(CAST(AddressBook AS xml ).query(N'/AddressBook/Address[last()]/tLine2/text()') AS nvarchar(1000))

+ ', ' + CAST(CAST(AddressBook AS xml ).query(N'/AddressBook/Address[last()]/City/text()') AS nvarchar(1000))

+ ', ' + CAST(CAST(AddressBook AS xml ).query(N'/AddressBook/Address[last()]/RegionName/text()') AS nvarchar(1000))

+ ', ' + CAST(CAST(AddressBook AS xml ).query(N'/AddressBook/Address[last()]/PostalCode/text()') AS nvarchar(1000))

+ ', ' + CAST(CAST(AddressBook AS xml ).query(N'/AddressBook/Address[last()]/CountryName/text()') AS nvarchar(1000))

+ ', ' + CAST(CAST(AddressBook AS xml ).query(N'/AddressBook/Address[last()]/Phone/text()') AS nvarchar(1000))

+ ', ' + Email AS Expr1, 1 AS [Order]

FROM   bvc_User

WHERE (bvin IN

          (SELECT DISTINCT UserId

           FROM         bvc_Order

           WHERE (OrderNumber <> '') AND (TimeOfOrder > '01/01/2007')))

ORDER BY [Order]

 Run this in SQL Management Studio and then copy and paste the results to a text file. You can open the text file in Excel as "comma separated" (CSV) to get sort the data or mail merge.

 

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Tags: , , , ,

bv commerce | Code

Comments are closed
Add to Technorati Favorites