Salesforce data loading when all else fails. - August 21, 2009 at 8:39 pm

Had an interesting challenge the other day: all the best last data migration plans were failing. Why? The tool of choice — which one is not important — failed. What to do?

I used Cloud Converter. Quelle surprise, right? I could have used something else, but it’s some decent code that I’m familiar with — so I used it. How?

I first did a test to be sure the API was working using a SalesforceSession object. Why test? Well, you never know. Why the Cloud Converter provided "SalesforceSession"? It’s easy. One line of code or so with a valid user and I established that the user was good, that the API was functional and that the user had rights to the API.

Sweet!

Then I moved to the source documents. I had two kinds: Simple CSV’s and Complex CSV’s.

Simple CSV’s were easy. I added a standard Java FileReader to the sample code, pointed it at the file, and split the data into component parts. Then I built a collection of "Sproxy" objects, added the SalesforceSession to a SalesforceDAO and executed an upsert. Start to finish, about 5 minutes for the first file.

Complex CSV’s were tougher. The difference between simple and complex was that the data contained commas. I know, this seems like it should be relatively easy, but at the time I was drawing a blank. So I imported those Complex CSV’s into Excel, saved them as Excel files and then used another Cloud Converter class, ExcelConverterService.

ExcelConverterService reads an XLS and breaks the data in the cells into a collection of type safe objects. Then I did the same thing I had done with Simple CSV’s: iterated through the data, added it to a Sproxy collection and threw it at the API.

Eh voila – instant data load.

Truth be told, I would rather not have had to do it this way. But when all else failed, it was nice to have an extra tool in my kit.

You can find Cloud Converter over on Google Code or on Developer.Force.com.