function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Amol AvhadAmol Avhad 

Create a SOQL query to get field names from command line

I am using data loader from command line to extract data in csv format. I have to hard code the SOQL query everytime in the sfdc.extractionSOQL property. Is there a way to get the field names dynamically and create a query from it? I need to select all fields dynamically and run the SOQL query. Below is the bean, I am using:
<bean id="csvAccountExtractProcess"
      class="com.salesforce.dataloader.process.ProcessRunner"
      singleton="false">
  <description>csvAccountExtract job gets account info from salesforce and saves info into a CSV file."</description>
    <property name="name" value="csvAccountExtract"/>
    <property name="configOverrideMap">
        <map>
            <entry key="sfdc.debugMessages" value="false"/>
            <entry key="sfdc.debugMessagesFile" value="c:\dataloader\samples\status\sfdcSoapTrace.log"/>
            <entry key="sfdc.endpoint" value="https://login.salesforce.com"/>
            <entry key="sfdc.username" value="user@mycompany.com"/>
            <!-- password specified below is invalid, please generate one using the encrypt.bat utility -->
            <entry key="sfdc.password" value="1111111111111111"/>
            <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 Id, Name, Type, ParentId, Phone, AccountNumber, Website, Sic, AnnualRevenue, NumberOfEmployees, TickerSymbol, Oracle_Id__c FROM Account"/>
            <entry key="process.operation" value="extract"/>
            <entry key="process.mappingFile" value="c:\dataloader\samples\conf\accountExtractMap.sdl"/>
            <entry key="dataAccess.type" value="csvWrite"/>
            <entry key="dataAccess.name" value="c:\dataloader\samples\data\extract.csv"/>
        </map>
    </property>
</bean>

 
NagendraNagendra (Salesforce Developers) 
Hi Amol,

To get all the fields for a sObject (Account sObject in your case) dynamically you can use:
SObjectType type = Schema.getGlobalDescribe().get('Account');
    Map<String,Schema.SObjectField> mfields = type.getDescribe().fields.getMap();
On the map mfields you have all the name of the fields for the specified sObject.

Hope this helps to your use case.

Kindly mark this as solved if it's resolved.

Thanks,
Nagendra

 
Stephen LiuStephen Liu
HI Amol,

I dont think you can generate dynamic SOQL from your process-conf file, reason being you cannot call Salesforce from within that file.
Nagendra provided Apex code, but I dont know how that would work in this scenario, please do let me know Nagendra.

I purpose you utilise Ant Migration tool's ability to query the object's metadata and make a ant target to parse the .object file and generate the process-conf from your ant build.xml
Below is an example on how would I approach this problem:
<?xml version="1.0" encoding="UTF-8"?>
<project name="Export" default="backup" xmlns:sf="antlib:com.salesforce">
	<taskdef resource="net/sf/antcontrib/antlib.xml"/>
    <property file="build.properties"/>
    <property environment="env"/>

    <macrodef name="export">
        <attribute name="file"/>
        <attribute name="object"/>
        <attribute name="soql"/>
        <attribute name="userName"/>
        <attribute name="password"/>
        <attribute name="serverURL"/>
        <attribute name="batchSize"/>
        <attribute name="limit"/> 

        <sequential>
            <echo message="Exporting @{object}"/>
            <mkdir dir="exports/DATE"/>
            <mkdir dir="logs/DATE"/>
            <copy file="config/template-process-conf.xml" tofile="config/process-conf.xml" overwrite="true" failonerror="true"/>
            <replace file="config/process-conf.xml">
                <replacefilter token="_object_" value="@{object}"/>
                <replacefilter token="_soql_" value="@{soql}"/>
                <replacefilter token="_file_" value="exports/${todayDate_only}/@{file}.csv"/>
                <replacefilter token="_serverURL_" value="@{serverURL}"/>
                <replacefilter token="_userName_" value="@{username}"/>
                <replacefilter token="_password_" value="@{password}"/>
                <replacefilter token="_batchSize_" value="@{batchSize}"/>
                <replacefilter token="_logFile_" value="logs/DATE/@{file}_log.csv"/>
            </replace>
            <java classname="com.salesforce.dataloader.process.ProcessRunner" classpath="C:\YOUR_PATH\dataloader-39.0.0-uber.jar" failonerror="true">
                <sysproperty key="salesforce.config.dir" value="config"/>
                <arg line="process.name=@{object}"/>
            </java>
        </sequential>
    </macrodef>
	
	<!--Step 1-->
	<target name="retrieveObjects">
		<mkdir dir="retrieveObjects"/>
		<sf:retrieve 
			userName="${sf.prod.profileName.username}"
			password="${sf.prod.profileName.passwordUnencrypted}"
			serverURL="${sfProd.serverurl}"
			retrieveTarget="retrieveObjects"
			unpackaged="retrieveSource/package.xml"/>
	</target>
	
	<!--Step 2-->
	<target name="foo">
		<foreach target="backup" param="file">	
			<path>
				<fileset dir="YOUR_PATH_objects" includes="**/*.object"/>
			</path>
		</foreach>
	</target>
	
	<target name="backup">
		<xmlproperty file="retrieveObjects/objects/Account.object" collapseAttributes="true"/> <!--this parses the xml file-->

		<export
            file="Account_backup-DATE"
            object="Account"
            soql="select ${CustomObject.fields.fullName} from Account"
            userName="${sf.prod.profileName.username}"
            password="${sf.prod.profileName.password}"
            serverURL="${sfProd.serverurl}"
            batchSize="200"
            limit = "1000" />
    </target>
</project>

for this to work you need:
  • ant migration tool properly configured​
  • download and install ant-contrib for the foreach loop to work
  • make a template-process-conf.xml and copy this code into it, and modify it as you need
This ant build.xml use a template file to generate the actual process-conf with replaced variables. Give this a try and let me know if you get any lucks or questions.
SFDave_126SFDave_126
Stephen,

Could you provide more information on how this template file would be constructed and whether there is any way around this? Wouldn't it be better to backup the process-conf.xml file to another file for safe-keeping before the script executes?

<copy file="config/template-process-conf.xml" tofile="config/process-conf.xml"overwrite="true" failonerror="true"/>
           <replace file="config/process-conf.xml">

Thanks,
Patrick Earl DCPatrick Earl DC
Has anyone able to make it work using the suggestion of stephen?