function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
JDP12345JDP12345 

Apex Data Loader

Hello>

 

Is there anyway of defining the data loader in code to convert my extracts fields to a number data type

 

right now when I run the data loader to extract a file that has decimals, it chops the trailing 0.

 

For example,

 

345343.5

 

Should be 345343.50

 

Below is an example of the code:

 

 

<bean id="csvInvestmentExtractProcess"
          class="com.salesforce.lexiloader.process.ProcessRunner"
          singleton="false">
  <description>csvAccountExtract job gets investment info from salesforce and saves info into a CSV file."</description>
        <property name="name" value="csvInvestmentExtract"/>
        <property name="configOverrideMap">
            <map>
                <entry key="sfdc.debugMessages" value="false"/>
                <entry key="sfdc.debugMessagesFile" value="S:\SFDC\Process1\status\sfdcSoapTrace.log"/>
                <entry key="sfdc.endpoint" value="https://www.salesforce.com"/>
                <entry key="sfdc.username" value="test@test.org"/>
                <entry key="sfdc.password" value="c22d48685a1a3f532658a86b909fc97f"/>
    <!-- <entry key="process.encryptionKeyFile" value="S:\SFDC\Process1\conf\key.txt"/> -->
                <entry key="sfdc.timeoutSecs" value="600"/>
                <entry key="sfdc.loadBatchSize" value="200"/>
                <entry key="sfdc.entity" value="Investment__c"/>
                <entry key="sfdc.extractionRequestSize" value="500"/>
                <entry key="sfdc.extractionSOQL" value="Select Id, Name, IsDeleted, LastModifiedById, LastModifiedDate, SystemModstamp, CreatedById, CreatedDate, Account_Name__c, Active__c, Balances_as_of__c, Capital_Gains_Distribution__c, Commitment__c, Fund_Type__c, Fund__c, ISN__c, Inception_Date__c, Income_Dividends__c, Initial_Investment__c, Liquidation_Date__c, Market_Value__c, MemberId__c, NAV__c, Notes__c, PoA_Instructions__c, Power_of_Attorney__c, RecordTypeId, SID__c, Share_Balance__c, Sub_Account__c, Transfer_to__c, Statement_Label__c, Month_end_Share_Balance__c, Month_end_Market_Value__c, Month_end_NAV__c, Month_end_Balances_as_of__c, StateStreetId__c from Investment__c"/>
                <entry key="process.operation" value="extract"/>
                <entry key="process.mappingFile" value="S:\SFDC\Process1\maps\investmentExtractMap.sdl"/>
                <entry key="dataAccess.type" value="csvWrite"/>
                <entry key="dataAccess.name" value="S:\SFDC\Process1\data\export\investment.csv"/>
            </map>
        </property>
    </bean>

 

 

sfdcfoxsfdcfox

The Apex Data Loader should return the same values as you would get by running a report. The field values should always use the scale and precision specified when using the API. I would invite you to try opening the file in something other than Microsoft Excel, which is notorious for it's "smart" CSV processing; this often causes trailing decimals to be lost, and is also responsible for mangling large numbers (4159017010, the phone number for Technical Support, would render as 4.159017e+09 or some such). Verify that the data is or is not being truncated in a program such as WordPad, Notepad, or even OpenOffice (which is not as "smart" as Excel, thankfully). To be clear, CSV files do not contain formatting information, and therefore Excel has to "guess" what is meant by the source data.

 

CSV:

First Name,Last Name,Company,Phone Number,Annual Revenue

John,Doe,Acme,2125554240,54000.00

 

Excel:

(A1:Text)First Name, (A2:Text)Last Name, (A3:Text)Company, (A4:Text)Phone Number,(A5:Text)Annual Revenue

(B1:Text)John, (B2:Text)Doe, (B3:Text)Acme, (B4:Text)2125554240,(B5:Currency:$#,##0.00)54000

 

In the first example, is what a CSV file looks like. Excel isn't smart enough to know that a phone number requires exact precision, and therefore "guesses" that it's a number, and converts it to 2.125554e+09 for you. The Annual Revenue field is likewise examined, and the trailing .00 is deemed superficial and will be discarded.

 

In the second example, data loaded natively into Excel as a .XLS or .XLSX file is explicitly flagged as a "text" field, and therefore Excel will preserve its contents. Note that the format above is illustrative and does not reflect the true nature of an Excel file, which also stores cell and text attributes. In the Annual Revenue field, Excel uses a Currency formula to derive that the field should be formatted as "$54,000.00". This happens because the user manually formatted the field as a currency formula, and Excel then uses the correct format.

 

To "fix" this problem, you need to actually tell Excel what format a cell is expected to be in. You can do this by selecting the entire column, right-clicking on the selected cells, and editing the cell properties (specify Currency or Number, and adjust the decimal places manually). Or, use a program that is smart enough to realize that the data is formatted a particular way for a particular reason, and not adjust the values it finds automatically.
JDP12345JDP12345

Thank you for your response.

 

I opened the file using notepad and I still see that the values are still being truncated.

 

Is there are way to define a field as a data type of double in the apex code I sent?
xunxun

I'm guessing you can use Trigger to do it.

 

a = a.setScale(2);
JDP12345JDP12345

Thanks.

 

From the code that I provide, can you let me know where to place the syntax?

 

Thanks again for your help!