I had this article in my list for long time and today i got chance to share with you all. Data Loader is great native tool provide by the Salesforce to insert, upsert, update, export and delete data. Standard Data Loader wizard needs interaction however there are many scenarios where we need to perform these data loading tasks repeatedly like every night 1:00 AM (Nightly Services) or something. So, for these situations we can use standard data loader tool from Command line.
In this article, i will explain in detail on how to use Data Loader from command line.
The most important part of setting up Command line Data loader is to generate the encrypted password using utility provided by the Data Loader.
This directory contains two utility batch file : “encrypt.bat” and “process.bat”
Navigate to “Program Files | Data Loader | bin” in command prompt and run command.
encrypt.bat –g YOURSECRETKEY
The secrete key provided can be anything of your choice. Copy the generated Text highlighted in White color and paste in new file “Key.txt“.
Now run again above command to generated encrypted password with following command:
encrypt.bat -e password "C:Users\shiva\Desktop\Data Loader Documents\Try\Key.txt"
Now, note this password somewhere, as it is going to be used in remaining part of configuration.
Make sure that password generated is correct by checking below command, it will say password Matched or not :
encrypt.bat -v generatedPassword actualPassword secretKeyFilePath
If you are using proxy server and it uses the password, then repeat same step for generating encrypted password.
Additionaly, if we type only encrypt.bat without any parameter, it will show all the available options with help text.
Create file – “config.properties”:
This file contains all the common configurations settings like UserName, encrypted password. This file can also be kept as blank however it is good practice to write all common configurations in this file. Keys in this file will be same as of “Process-config.xml” as described at the end of this article.
#Loader Config #Wed Sep 19 2012 sfdc.debugMessages=true process.encryptionKeyFile=C:\Users\shiva\Desktop\Data Loader Documents\Try\Key.txt sfdc.debugMessagesFile = C:\Users\shiva\Desktop\Data Loader Documents\Try\AccountExport.log sfdc.endpoint=https://login.salesforce.com firstname.lastname@example.org sfdc.password=7b5693f0cad856dc778d4b8506f2c976 sfdc.proxyUsername=proxyUserName sfdc.proxyPassword=encrypytesPassword sfdc.proxyHost=proxyhostName sfdc.proxyPort=123 sfdc.loadBatchSize=200 sfdc.timeoutSecs=600
Create file – “process-conf.xml” :
This file contains all the information regarding import or export. In this article I am exporting the Account data, so sample file is
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd"> <beans> <bean id="Account" class="com.salesforce.dataloader.process.ProcessRunner" singleton="false"> <description>Export Account Data</description> <property name="name" value="Account"/> <property name="configOverrideMap"> <map> <entry key="sfdc.enableLastRunOutput" value="false"/> <entry key="sfdc.entity" value="Account"/> <entry key="process.operation" value="extract"/> <entry key="sfdc.extractionRequestSize" value="500"/> <entry key="sfdc.extractionSOQL" value="SELECT Id FROM Account"/> <entry key="dataAccess.name" value="D:\Data Load\Seed Data\Jit\Production - 16 Aug 12AccountExport.csv"/> <entry key="dataAccess.type" value="csvWrite"/> <entry key="dataAccess.writeUTF8" value="true" /> </map> </property> </bean> </beans>
At the end of this article, you can see the description of all parameters. Assuming that we are inside the bin folder of dataloader then run below Command:
Process.bat "directory path where we have file process-config.xml" process Name
In our case the process name is Account, process name is nothing but the property “name”.
After running the above command your data will be exported in file provided in configuration “process-config.xml” file.
While working with command line dataloader, there are chances that you get few error. So i am also inclusing few common error and there solutions:
Dataloader error: “javax.crypto.BadPaddingException:”Or error loading parameter of sfdc.password
Add “process.encryptionKeyFile=C:\Users\315447\Desktop\Data Loader\key.txt” Either in “config.properties” or “process-conf.xml”
<entry key="process.encryptionKeyFile" value="config/key.txt"/>
Error in query: query is empty
Occurs, If “sfdc.extractionSOQL” key in beans node does not have valid SOQL.
Parameters for Process-config.xml:
|<bean id=" PROCESSNAME " class="com.salesforce.lexiloader.process.ProcessRunner" singleton="false">||This first line uniquely defines the process in the XML file. This has to be unique and will be used to call the process later on. Only change the PROCESSNAME and leave all other parts the same.|
|<description> Your Description here </description>||A description of the process.|
|<property name="name" value="PROCESSNAME "/>||Copy the PROCESSNAME and place it in this parameter. This is used in the log files when debug messages are showing. This is the name that is shown on the debug logs.|
|<property name="configOverrideMap">||These two lines initiate the parameters for the process. They should not be changed and should always be the same.|
|<entry key="sfdc.debugMessages" value="false"/>||Set this to True if you want detailed debug messages to appear in the command line.|
|<entry key="sfdc.debugMessagesFile" value="c:\\dataloader\\samples\\status\\accountMasterSoapTrace.log"/>||Set the value to a directory and a file to keep the detailed log file in.
WARNING: This can create an extremely large file. Make sure that you have space on your hard drive before this is set.
|<entry key="sfdc.endpoint" value="https://www.salesforce.com"/>||Keep default, unless loading into Sandbox. for sandbox, it is test.salesforce.com.|
|<entry key="sfdc.username" value="jitendrazaa@winter13Sand.com" />||Set this as the username of the user you are importing /exporting as.|
|<entry key="sfdc.password" value="b6b3016135f717754590a3e35b1db32b" /><br/> <entry key="process.encryptonKeyFile" value="C:\\Program Files\\salesforce.com\\Apex Data Loader 9.0\\test\\Key.txt" />||See this article on how to create Encrypted password for Command line Dataloader in Salesforce|
|<entry key="sfdc.timeoutSecs" value="600"/>||System time out value.|
|<entry key="sfdc.loadBatchSize" value="200"/>||System batch load size.|
|<entry key="process.operation" value="extract" />||Signifies the process being run.
|<entry key="sfdc.entity" value="Account"/>||Signifies the object that you are running this process for. This should be the API name of the object when you are running processes for custom objects.|
|<entry key="sfdc.extractionRequestSize" value="500"/>||Batch size for extract processes only|
|This is the SOQL query run for extract processes. You can use the Data Loader GUI to generate the SOQL statement and conditions and simply cut and paste into this entry key.|
|<entry key="dataAccess.type" value="csvWrite" />||Signifies the action when doing the process for Extract, Insert, Delete, Upsert. This key should be used in conjunction with the "process.operation" key.
When using Extract processes, use csvWrite. When using Delete, Insert, and Upsert processes, use csvRead.
|<entry key="dataAccess.writeUTF8" value="true" />||When using a "csvWrite" dataccess type, set this to True when you want files to be extracted as UTF-8.|
|<entry key="dataAccess.name" value="<b>C:\\Program Files\\salesforce.com\\Apex Data Loader 9.0\\test\\extract.csv</b>" />||Signifies the location and file name of the dataaccess type process. If running a csvRead, the process will look for the file in this location. If running a csvWrite, the process will replace the file in this location.|
|<entry key="process.mappingFile" value="<b>C:\\Program Files\\salesforce.com\\Apex Data Loader 9.0\\test\\upsert_mapping.sdl</b>"/>||Signifies the location of a data loader mapping file. This is the same mapping file used when saving a mapping from the data loader GUI. This is required for insert, delete and upsert processes.|
|<entry key="sfdc.externalIdField" value="Customer_ID__c"/>||Signifies the External ID field used for the upsert process. This is required for upserts.|
|<entry key="process.statusOutputDirectory" value="C:\\Program Files\\salesforce.com\\Apex Data Loader 9.0\\test\\logs\\" />||Signifies the directory where the data loader success and error files will be created in.|
|<entry key="process.outputSuccess" value="C:\\Program Files\\salesforce.com\\Apex Data Loader 9.0\\test\\Logs\\csvUpsertProcess_success.csv" /><br/><br/> <entry key="process.outputError" value="C:\\Program Files\\salesforce.com\\Apex Data Loader 9.0\\test\\Logs\\csvUpsertProcess_error.csv" />||Signifies the directory and filename where the success and error files will be created. If this key is not specified, the "process.statusOutputDirectory" key will contain similar filenames as ones generated by the data loader GUI.|