Syntax Highlighter JS

Sunday, March 18, 2012

The case of the missing zero's - Part 2

EDIT: I now recommend the Jitterbit Data Loader which can be found here, over the salesforce data loader. At the time of this edit, Jitterbit had a free version.

In part 1, we looked at where and why the zero's are missing in salesforce's database.

Now, we will turn our attention towards how to put them back by creating a modified version of the data loader.

Please note that this will use regular expressions to change any field ending in .0 to .00 (.00, .000, etc will be left alone).  This is not the idea method but it works.

In a perfect world, we would call the DescribeSObject method in the metadata API and look at the DescribeSObjectResult's fieldtype and scale fields to only fix this problem on given field types.  I have been hoping that salesforce would update the dataloader to do so but as of version 23 - no such luck.  Maybe I will get bored one day and do it myself.

In the mean time, notice how are modifying an existing function.   Sales force is doing a similar thing (i.e. not using the meta data API but reformatting a field) in this same method for dates.

Since this is a rather technical post lets go over some prerequisite things you will need:
  1. A working Eclipse Java environment.  If you have the IDE installed, then you should be good to go.
  2. Install ANT to your machine (this requires the JDK not to be confused with the JRE).
  3. Install Perl to your machine.
  4. Download the latest copy of the ApexDataLoader source code and extract it to C:\apexdataloader\
Once you have these installed, you are now ready to use ANT to generate lib\partnerwsdl.jar

You can generate this file by issuing running ant.bat in C:\apexdataloader\build\

Once completed, it should create C:\apexdataloader\lib\partnerwsdl.jar.

Now you can create a new java project in eclipse, import the filesystem into the project, fix your JDK and partnerwsdl.jar references in the java build window by locating these on the file system..

Once you have the project loaded:
  1. Open the PartnerQueryVisitor.Java file
  2. Change import java.util.regex.pattern; to read import java.util.regex.*;
  3.  Locate the convertFieldValuefunction and append this code to the bottom (before the return line)
            if (fieldVal != null) {
                // create a pattern to use regular expressions
                Pattern ptrnEndsInDotZero = Pattern.compile("\\.[0-9]");
                Pattern ptrnEndsInDotTwoNums = Pattern.compile("\\.[0-9][0-9]");
                // cast our number to a string
                String strDecimalAmount = fieldVal.toString();
                // create a matcher from our pattern and string
                Matcher mtchEndsInDotZero = ptrnEndsInDotZero.matcher(strDecimalAmount);
                Matcher mtchEndsInDotTwoNums = ptrnEndsInDotTwoNums.matcher(strDecimalAmount);
                boolean blnDotZeroSearchResult = mtchEndsInDotZero.find(); 
                boolean blnDotTwoNumsSearchResult = mtchEndsInDotTwoNums.find();
                if (blnDotZeroSearchResult == true && blnDotTwoNumsSearchResult == false) {
                    // Has tenths but not hundredths
                    // found an entry with missing Decimal
                    // add the zero back.
                    fieldVal = fieldVal + "0";
            }   // check for null field 
  4. Use C:\apexdataloader\build\ant.bat to build your jar file in C:\apexdataloader\JAR\
  5.  If your version of java wasn't compatible with the salesforce exe, you can now call the jar file from the command line (or a bat file) like this:
    java.exe -cp ..\MyDataLoader.jar -Dsalesforce.config.dir=..\ConfigDIRGoesHERE com.salesforce.dataloader.process.ProcessRunner
Or you can avoid the hard work and us this jar file which has been modified in the above fashion.

No comments:

Post a Comment