+ Start a Discussion

SOQL error with Left function using apex data loader command line

I'm using "Apex Data Loader 14" in batch process mode to export data.  I found that IDs are 18 characters long in Data Loader exports and 15 characters long in Salesforce.com report exports.  Apparently, the Data Loader adds on a 3-digit "External ID".


I'm trying to strip off these extra 3 characters using the SOQL Left function.  But I get this error message:


6500 [csvContactExtract] ERROR com.salesforce.lexiloader.action.progress.NihilistProgressAdapter  -
Select Left(Id,15),FirstName,LastName,Title
ERROR at Row:1:Column:12
expecting "from", found ''


I am attaching the code for the bean from the process-conf.xml.

It worked fine until I added the Left function. 

Can anyone tell me how I can get the Left function to work?



<bean id="csvAccountExtractProcess"
<description>csvAccountExtract job gets account info from salesforce and saves info into a CSV file."</description>
<property name="name" value="csvAccountExtract"/>
<property name="configOverrideMap">
<entry key="sfdc.debugMessages" value="false"/>
<entry key="sfdc.debugMessagesFile" value="c:\dataloader\samples\status\sfdcSoapTrace.log"/>
<entry key="sfdc.endpoint" value="https://www.salesforce.com"/>
<entry key="sfdc.username" value="me@you.com"/>
<!-- password specified below is invalid, please generate one using the encrypt.bat utility -->
<entry key="sfdc.password" value="9999999999999"/>
<entry key="sfdc.timeoutSecs" value="600"/>
<entry key="sfdc.loadBatchSize" value="200"/>
<entry key="sfdc.entity" value="Account"/>
<entry key="sfdc.extractionRequestSize" value="500"/>
<entry key="sfdc.extractionSOQL" value="Select Left(Id,15),Account__c,Type,Name,Acc__c,Acc_On_Hold__c,Status_Date_Change__c FROM Account"/>
<entry key="process.operation" value="extract"/>
<entry key="process.mappingFile" value="R:\SF_data_exchange\conf\accountExtractMap.sdl"/>
<entry key="dataAccess.type" value="csvWrite"/>
<entry key="dataAccess.name" value="R:\SF_data_exchange\accounts.csv"/>


There is no left function in SOQL.

Oops! My bad.


So, how can I remove the 3 trailing characters in that context?


It sounds like you are looking for the 15-character version of the record Id. You could either apply that logic after you extract the data from salesforce.com. Or you could create a formula field which supports the LEFT function to store the 15-character version of the record Id.



There should be a left, right, and mid/substring  function in SOQL.