Back to Blog

Using CLI Data Loader For Data Exports

A few months back, we had an opportunity to build a functionality for hourly incremental/delta export of data from Salesforce. The restrictions enforced by the customer and the limits imposed by Salesforce made it a really fun problem to solve!

We decided to use the Command Line Data Loader, instead of buying yet another licensed ETL tool. The command line data loader can be used to schedule exports as well.

The Command Line Data Loader requires SOQL to export data. We needed to schedule the process using windows scheduler, on an hourly basis. However, the main challenge here was to write a dynamic SOQL, that would apply a filter to extract the records for those created date is greater than last 1 hour. Salesforce does not provide any 'out of the box' datetime literal like LAST_1_Hour or LAST_N_HOUR.

There are 2 methods to accomplish this requirement. First is using Powershell script and the second is using SOQL itself.

    Method 1 - PowerShell Script

  1. In case of command line data loader, process-conf.xml contains the SOQL. In case of an hourly incremental data extract, SOQL structure will be

    SELECT Id, Name FROM Contact WHERE CreatedDate > 2018-06-28T08:00:00.000Z AND CreatedDate <= 2018-06-28T09:00:00.000Z

    But the above SOQL is only for an extract that will run at 09:00. We needed to write a SOQL that was dynamic and insert the correct datetime in where clause for every run.

    Neither command line data loader nor SOQL provides any feature to achieve the stated requirement. We followed the steps below to achieve our goal:

    1. Step 1 - Create a process-conf-template.xml file with same content as process-conf.xml but instead of putting any hard coded date or literal in the SOQL, put placeholders like below.

    2. SELECT Id, Name FROM Contact WHERE CreatedDate > #LAST_EXTRACT_RUN_DATE_TIME# AND CreatedDate <= #CURRENT_DATE_TIME#

    3. Step 2 - Now, create a .bat file (create-process-config.bat) with below powershell script that will replace the "#LAST_EXTRACT_RUN_DATE_TIME#" and "#CURRENT_DATE_TIME#" placeholder with actual date/time and will put in the process-conf.xml that will be used in the actual data loader process.

    4. powershell.exe -Command "Set-Content -Path "E:\CommandLine\Script\currentdatetime.txt" -Value $(Get-Date).ToString('yyyy-MM-ddTHH:mm:ss.000Z')"

      powershell.exe -Command "Get-Content E:\CommandLine\Script\process-conf-template.xml | Foreach-Object{$_ -replace '#LAST_EXTRACT_RUN_DATE_TIME#',(Get-Content E:\CommandLine\Script\lastrundatetime.txt)} | Foreach-Object{$_ -replace '#CURRENT_DATE_TIME#',(Get-Content E:\CommandLine\Script\currentdatetime.txt)} | set-content E:\CommandLine\Config\process-conf.xml"

      powershell.exe -Command "Set-Content -Path "E:\CommandLine\Script\lastrundatetime.txt" -Value ((Get-Content E:\CommandLine\Script\currentdatetime.txt))"

      In the above powershell script, one can replace the file path in place of "E:\CommandLine\Script\" and "E:\CommandLine\Config\".

    5. Step 3 - Now, create-process-config.bat must execute before the actual data loader process runs. One has to create a new bat file (mainProcess.bat) that will run the create-process-config.bat first and after that it will run the data extract. Put below code in the mainProcess.bat file:

    6. echo Creating process config file call E:\CommandLine\Script\create-process-config.bat

      echo Initiating data extract call E:\CommandLine\Script\DataExtract.bat

    7. Step 4 (Optional) - Additionally, if datetime stamp needs to be added in the file that is extracted, one can write below script in a bat file (renamefile.bat) and call that file in the end of mainProcess.bat:

    8. echo Initiating File Renaming set d=%date:~-4,4%%date:~-7,2%%date:~0,2% set d=%d: =_% set t=%time:~0,2%%time:~3,2%%time:~6,2% set t=%t: =0%

      RENAME "put file path here" DataExtract.csv" "DataExtract_%d%_EST_%t%.csv"

    9. Step 5 - Now, schedule the mainProcess.bat file using windows scheduler. This method is dynamic. It will run for any frequency, so it can be scheduled every 5 minutes, hourly or at any given frequency because it will put last data extract run time and current time in the SOQL.

  2. Method 2 - SOQL

    • In this case, following queries can be used for different scenarios:

    • Scenario 1 -If daily export is required to run at 18:00 Hours (6:00 PM) daily to export the data that is created after 6:00 PM on the previous day.

      To achieve this, the process schedule has to run daily at 18:00 Hours with following SOQL:

      SELECT Id, Name, OwnerId FROM sObjectName WHERE (CreatedDate = YESTERDAY AND HOUR_IN_DAY(CreatedDate) >= 18) OR (CreatedDate = TODAY AND HOUR_IN_DAY(CreatedDate) < 18)

    • Above scenario can be implemented to export the data daily at N:00 hour.

      Scenario 2 -If daily export is required for every hour.

      To achieve this, schedule the process for every hour with following SOQL:

      SELECT Id, Name, OwnerId FROM sObjectName WHERE CreatedDate = TODAY AND HOUR_IN_DAY(CreatedDate) >= N-1 AND HOUR_IN_DAY(CreatedDate) < N

      In the above query, N has to be replaced with the time when the process is being scheduled. Therefore, the process has to be scheduled 24 times on a daily basis. See below examples:

      Scheduler at 9:00 AM will export the data for last 1 hour using below SOQL:

      SELECT Id, Name, OwnerId FROM sObjectName WHERE CreatedDate = TODAY AND HOUR_IN_DAY(CreatedDate) >= 8 AND HOUR_IN_DAY(CreatedDate) < 9

      Scheduler at 10:00 PM (22:00) will export the data for last 1 hour using below SOQL:

      SELECT Id, Name, OwnerId FROM sObjectName WHERE CreatedDate = TODAY AND HOUR_IN_DAY(CreatedDate) >= 21 AND HOUR_IN_DAY(CreatedDate) < 22

      So, this may help to get out of the licensing cost. PowerShell script method is the better one because it requires less code and maintenance compared to the SOQL method.