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

Excel Formula to Remove Last Word from Cell

I have found both of these formula to work well to removing the last word from a string in a cell in excel.


=LEFT(A1,LOOKUP(2^15,FIND(" "," "&A1,ROW(INDIRECT("1:"&LEN(A1)))))-1)

=LEFT($A2,SEARCH("#",SUBSTITUTE($A2," ","#",LEN($C2)-LEN(SUBSTITUTE($A2," ","")))))

SQL Conversion Query's

Below are some querys that will help in converting data in SQL to a format more friendly with excel.




This can be used to convert a column to varchar(max) which works well with Excel:

Select
convert(varchar(max),convert(ntext,TaskToDo)) as TaskToDo
from t_Task



This is used to replace carriage returns with spaces:

Select
REPLACE(taskitem, Char(13) + Char(10), ' ') as TaskItem
from t_Task



Replace and convert at the same time:

Select 
REPLACE(convert(varchar(max),convert(ntext,TaskToDo)), Char(13) + Char(10), ' ')
from t_Task

Conga Mail Merge - Display All Merge Fields // Insert Merge Field Shortcut

There are quite a number of little quirks that are nice to know when using the Salesforce.com AppExchange app Conga Composer to create merge documents. While calling their support line is very helpful, sometime I feel like I need to give them a break.

Here is a function I use all the time..

Display all Merge Fields in Word:
ALT + F9

Insert Merge Field
ATL + I + F + M + M

Migrate Reports from Salesforce Production to Production Using Ecplise

When moving from Sandbox to production you have the option of using change sets to quickly move reports, objects, triggers, classes ect. to a production enviornment. I was posed the problem of moving a number of reports from one Production Org to another Production Org that would have the same fields and same configuration.

With production org's not able to connect to other production org's via change sets I had to get creative, here is what I did.


CAUTION: When handling metadata components from a production enviornment in eclipse you must be VERY careful as you can screw things up in a hurry! I suggest playing around with this in a developer org before doing anything in production.

1. Connect Eclipse to the production environment of both org's.
2. When downloading a project in Eclipse, be sure to select all metadata components.
3. One of your folders will be Reports.
4. Open up the report you want to recreate. It will open a window, at the bottom of the page change your option from 'Design' to 'Source'
5. In the instance you are going to migrating the report right click on the report folder where you want the report to appear, hover over 'New' and click the 'Other..' option.
6. Under the 'XML' folder click 'XML' then 'Next'
7. You can give the report the same name as the one being copied, so 'SampleReport.report' would work.
8. Next you need to copy the XML code from step 4 and paste it into this new XML file you have created. 
9. NOTE: if the fields that are in the report are not consistent between the org's you will get errors
10. Finally click save, then right click on the report folder and select 'Force.com' > 'Save to Server' and that's it!