Automating the APEX Date Loader

There is a lot of great documentation about the APEX Data Loader and how it can help with loading data into Salesforce.  Just like any other external tool to Salesforce it is using the Salesforce’s APIs for the connection and interaction with Salesforce. Understanding those APIs will help you understand what is fully possible with the APEX Data Loader, but it is not required to get started. Here is a base article to get started with trying to automate the APEX data loader from the command line.

http://wiki.developerforce.com/page/Using_Data_Loader_from_the_command_line

This article lays out nicely the files involved and some best practices.  There are some very helpful links at the bottom too.  Especially review the ‘paper’ link which explains clearly how to encrypt your password so that it is not stored in clear text.

Here is how my process-conf.xml file looked like as I wrote a command line option for the APEX Data Loader for inserting data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<beans>
    <bean id="insertTeams" class="com.salesforce.dataloader.process.ProcessRunner" singleton="false">
        <description>Insert Teams</description>
        <property name="name" value="insertTeams">
        <property name="configOverrideMap">
            <map>
                <entry key="sfdc.debugMessages" value="true"></entry>
                <entry key="sfdc.debugMessagesFile" value="C:\Program Files (x86)\salesforce.com\Apex Data Loader 23.0\Team\insertTeams.log"></entry>
                <entry key="sfdc.endpoint" value="https://login.salesforce.com"></entry>
                <entry key="sfdc.username" value="MyUserName@hotmail.com"></entry>               
                <entry key="sfdc.password" value="0d30d56ae54323622039a0d667a486efd98903b31e14da9f7000ca15a3d1390b410705ce0f238a5c"></entry>
                <entry key="process.encryptionKeyFile" value="C:\Program Files (x86)\salesforce.com\Apex Data Loader 23.0\Team\key.txt"></entry>
                <entry key="sfdc.timeoutSecs" value="600"></entry>
                <entry key="sfdc.loadBatchSize" value="200"></entry>
                <entry key="sfdc.externalIdField" value=""></entry>
                <entry key="sfdc.entity" value="Team__c"></entry>
                <entry key="process.operation" value="insert"></entry>
                <entry key="process.mappingFile" value="C:\Program Files (x86)\salesforce.com\Apex Data Loader 23.0\Team\TeamMapping.sdl"></entry>
                <entry key="dataAccess.name" value="C:\Program Files (x86)\salesforce.com\Apex Data Loader 23.0\Team\Teams2.csv"></entry>
                <entry key="dataAccess.type" value="csvRead"></entry>
                <entry key="process.initialLastRunDate" value="2005-12-01T00:00:00.000-0800"></entry>
                <entry key="process.statusOutputDirectory" value="C:\Program Files (x86)\salesforce.com\Apex Data Loader 23.0\Team"></entry>
            </map>
        </property>
<p>    </p>
</property></bean><p>    </p>
</beans>

The ‘paper’ link I mentioned about does a very nice job of explaining what each of the ‘entry key=’ lines should be set to.

The ‘paper’ link also lays out how to format a batch file so you do not have to type the command line.  A sample batch file in the bin directory (“C:\Program Files (x86)\salesforce.com\Apex Data Loader 23.0\bin”) could look like this…

1
2
3
4
echo "starting"
cls
CALL process "C:\Program Files (x86)\salesforce.com\Apex Data Loader 23.0\Team" "insertTeams"
pause

Where “C:\Program Files (x86)\salesforce.com\Apex Data Loader 23.0\Team” is the location of the “process-conf.xml’ file and “insertTeams’ is the bean process to run within the “process-conf.xml” file.

If you do not want to clutter up the bin directory you could add a cd call in the bat file so all of your files could stay in the same directory.  It would look like this…

1
2
3
4
5
echo "starting"
cls
cd "C:\Program Files (x86)\salesforce.com\Apex Data Loader 23.0\bin"
CALL process "C:\Program Files (x86)\salesforce.com\Apex Data Loader 23.0\Team" "insertTeams"
pause

When I ran this batch file I needed these files in that directory…
1) config.properties ( This file only included sfdc.endpoint=https\://login.salesforce.com )
2) key.txt ( This was holding data to help decrypt my password )
3) process-conf.xml ( This was holding my process bean xml )
4) RunMe.bat ( This is the batch file that calls the process batch file in the bin directory )
5) TeamMapping.sdl ( This is the mapping file that APEX Data Loader will use for the insert command )
6) Teams2.csv ( This is the actual data to be inserted into Salesforce )

Here is a web site that describes the various batch calls above including echo, cls, cd, CALL and pause:  http://www.computerhope.com/batch.htm.

What if you want to schedule this batch job to run every night?

If your batch job does everything you need it to do, you could create a Task to run on your Windows computer every so often. Click on Start, All Programs, Accessories, System Tools and Scheduled Tasks.  You can setup jobs to run once a day or month and at a specific time at night.  Make sure the computer the task is running on is available when the task needs to run.

Usually when loading data to Salesforce you need to pull data from another data source and then push it to Salesforce.  To tie those two actions together you could create a Visual Studio project that is either a ‘Windows Forms Application’ or a ‘Console Application’.  Both of them will produce an exe file that can then be scheduled using the Scheduled Task manager on a Windows machine.

Here is some very simple code for a Console Application that will run the batch job we have been describing.

1
2
3
4
5
6
7
8
9
Module Module1
    Sub Main()
        Console.WriteLine("Starting")
        'Kick off batch job
        Process.Start("C:\Program Files (x86)\salesforce.com\Apex Data Loader 23.0\Team\RunMe.bat")
    End Sub
End Module

I hope this post has given you some ideas on how to automate the APEX data loader for your integration requirements.

 

 

This entry was posted in Technology and tagged , , , , . Bookmark the permalink.

One Response to Automating the APEX Date Loader

  1. Werner says:

    Just wish to say your article is as surprising.
    The clarity in your post is simply nice and i can assume you are an expert on this
    subject. Well with your permission allow me to
    grab your feed to keep updated with forthcoming post.
    Thanks a million and please carry on the enjoyable work.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>