Remove HTML Tags Using Find and Replace

Here is a quick and easy way to remove HTML tags completely by using the Find and Replace function in Microsoft.

Put this in the find box: \<*\>
Leave the Replace box Empty
Check the 'Use Wildcard' box
Replace All!

Exporting Emails from Goldmine

Converting companies from Goldmine to Salesforce.com presents a number of difficulties, one of which was exporting emails.

Because files must be in CSV format in order to import to Salesforce.com we need to write a query that will take the data stored int he Mailbox.dfb table and export it to excel. Below are the steps I went through recently to accomplish this.

1. The query used to create a view that converts the RFC822 image column to a text field.

GO
CREATE VIEW [dbo].[vMailbox]
AS
SELECT     LINKRECID, FLAGS, USERID, FOLDER, FOLDER2, ACCOUNTNO, CREATEON, MAILSIZE, MAILDATE, MAILTIME, MAILREF, LOPRECID, MAILID, EXT,
                      CAST(CAST(RFC822 AS varbinary(MAX)) AS varchar(4000)) AS RFC822, recid
FROM         dbo.MAILBOX

NOTE: I had to insert 'varchar(4000)' instead of varchar(max) because when I didn't put a cap on the size of the email messages they would be too large and produce errors when exporting to excel. This limits the email message size to 4,000 characters, but that's better than nothing!

2. Export this new view to an excel document.

• Right click on the database > Tasks > Export Data...
• Choose a Data Source:
• Next
• Choose a Destination: 
• Destination: Microsoft Excel
• Browse: [Give your files a name and destination]
• Excel Version: Microsoft Excel 97-2003 (I have had problems trying this with 2007)
• Next
• Write a query to specify the data to transfer
• Copy and paste the query below into the text area (this pulls data from the view created)
• SELECT LINKRECID, FLAGS, USERID, FOLDER, FOLDER2, ACCOUNTNO, CREATEON, MAILSIZE, MAILDATE, MAILTIME, MAILREF, LOPRECID, MAILID, EXT, CAST(CAST(RFC822 AS varbinary(MAX)) AS varchar(4000)) AS RFC822, recid FROM dbo.vMAILBOX
• Next
• Select Source Tables and Views
• Next
• Review Data Type and Mapping
• Next
• Save and Run Package
• Check 'Run Immediately'
• Finish >>|
• Complete the Wizard
• Finish

If the number of Mailbox files is in excess of 65,000 you may need to pull the data out in batches of 50,000 or 60,000 because excel 97-2003 will allow a maximum of a little over 65,000 rows in a file.Refer to my previous post about limiting the size of an output for more details. http://cloudrevelations.blogspot.com/2012/04/limit-results-in-sql-server-using.html

SQL Query that Excludes Null's and Empty Values

select accountno, notes
from contact1
where notes is not null and datalength(notes) > 0