function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
ZweeZwee 

Failed to parse date using dataloader Cliq

Hi,

I'm using dataloadercliq in ubuntu 12.04 to transfer data from mysql database to salesforce. I create an Upsert operation for opprotunity with the database-conf.xml like this:

 

<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<bean id="dbDataSource"
      class="org.apache.commons.dbcp.BasicDataSource"
      destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://localhost/oscommerce"/>
    <property name="username" value="root"/>
    <property name="password" value=""/>
</bean>
<bean id="queryData"
      class="com.salesforce.dataloader.dao.database.DatabaseConfig"
      singleton="true">
    <property name="sqlConfig" ref="queryDataSql"/>
    <property name="dataSource" ref="dbDataSource"/>
</bean>
<bean id="queryDataSql"
      class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true">
    <property name="sqlString">
        <value>
SELECT 'account_id', 'contact_id', 'close_date', 'amount', 'number_of_YubiKeys', 'number_of_YubiHSMs', 'osC_Customer_ID', 'osC_Order_ID', 'Name', 'StageName' UNION SELECT c.salesforce_account_id,c.salesforce_contact_id, DATE_FORMAT(o.date_purchased,'%m/%d/%Y %T'), ROUND(GREATEST(((SELECT SUM(IF(ot.class='ot_total',1,-1)*ot.value) FROM orders_total ot WHERE ot.orders_id=o.orders_id AND ot.class IN ('ot_total','ot_tax','ot_yubicotax','ot_shipping'))),0),0) AS Amount, IFNULL((SELECT SUM(op.products_quantity*pq.jp_value) FROM orders_products op, johns_parameters pq WHERE op.orders_id=o.orders_id AND pq.jp_group IN ('RFIDKey','BlackKey','WhiteKey') AND pq.jp_item=op.products_id),0),IFNULL((SELECT SUM(op1.products_quantity*pq1.jp_value) FROM orders_products op1, johns_parameters pq1 WHERE op1.orders_id=o.orders_id AND pq1.jp_group IN ('HSM') AND pq1.jp_item=op1.products_id),0),c.customers_id, o.orders_id, CONCAT('Webstore Purchase',' - ID: ',o.orders_id), CONCAT('Closed Won') FROM customers c, orders o WHERE c.customers_id=o.customers_id AND o.customers_id != 0 AND c.salesforce_contact_id IS NOT NULL AND c.salesforce_account_id IS NOT NULL AND (o.salesforce_opportunity_id='' OR o.salesforce_opportunity_id IS NULL) AND (o.sales_person='Web' OR o.sales_person IS NULL) AND o.orders_status IN (3,102) AND o.orders_id=221170;
        </value>
    </property>
    <property name="columnNames">
        <list>
            <value>account_id</value>
            <value>contact_id</value>
            <value>close_date</value>
            <value>amount</value>
            <value>number_of_YubiKeys</value>
            <value>number_of_YubiHSMs</value>
            <value>osC_Customer_ID</value>
            <value>osC_Order_ID</value>
            <value>Name</value>
            <value>StageName</value>
        </list>
    </property>
</bean>
</beans>

 


However, when I try to run the Upsert operation, i got this error:

2012-11-06 13:26:44,487 INFO  [main] controller.Controller initLog (Controller.java:404) - The log has been initialized
2012-11-06 13:26:44,497 INFO  [main] process.ProcessConfig getBeanFactory (ProcessConfig.java:103) - Loading process configuration from config file: /home/zwee/dataLoader/cliq_process/opportunityUpsert/config/process-conf.xml
2012-11-06 13:26:44,722 INFO  [main] xml.XmlBeanDefinitionReader loadBeanDefinitions (XmlBeanDefinitionReader.java:315) - Loading XML bean definitions from file [/home/zwee/dataLoader/cliq_process/opportunityUpsert/config/process-conf.xml]
2012-11-06 13:26:44,917 INFO  [opportunityUpsert] controller.Controller initConfig (Controller.java:365) - The controller config has been initialized
2012-11-06 13:26:44,921 INFO  [opportunityUpsert] process.ProcessRunner run (ProcessRunner.java:116) - Initializing process engine
2012-11-06 13:26:44,925 INFO  [opportunityUpsert] process.ProcessRunner run (ProcessRunner.java:119) - Loading parameters
2012-11-06 13:26:47,373 INFO  [opportunityUpsert] config.LastRun load (LastRun.java:96) - Last run info will be saved in file: /home/zwee/dataLoader/cliq_process/opportunityUpsert/log/opportunityUpsert_lastRun.properties
2012-11-06 13:26:47,431 INFO  [opportunityUpsert] process.ProcessRunner run (ProcessRunner.java:127) - Logging in to: https://login.salesforce.com/services/Soap/u/24.0
2012-11-06 13:26:47,463 INFO  [opportunityUpsert] client.PartnerClient login (PartnerClient.java:478) - Beginning Partner Salesforce login ....
2012-11-06 13:26:47,543 INFO  [opportunityUpsert] client.PartnerClient loginInternal (PartnerClient.java:519) - Salesforce login to https://login.salesforce.com/services/Soap/u/24.0/services/Soap/u/24.0 as user zwee@developer2.com
2012-11-06 13:26:49,764 INFO  [opportunityUpsert] dao.DataAccessObjectFactory getDaoInstance (DataAccessObjectFactory.java:51) - Instantiating data access object: queryData of type: databaseRead
2012-11-06 13:26:49,769 INFO  [opportunityUpsert] xml.XmlBeanDefinitionReader loadBeanDefinitions (XmlBeanDefinitionReader.java:315) - Loading XML bean definitions from file [/home/zwee/dataLoader/cliq_process/opportunityUpsert/config/database-conf.xml]
2012-11-06 13:26:49,878 INFO  [opportunityUpsert] process.ProcessRunner run (ProcessRunner.java:132) - Checking the data access object connection
2012-11-06 13:26:50,313 INFO  [opportunityUpsert] process.ProcessRunner run (ProcessRunner.java:137) - Setting field types
2012-11-06 13:26:51,904 INFO  [opportunityUpsert] process.ProcessRunner run (ProcessRunner.java:141) - Setting object reference types
2012-11-06 13:26:54,322 INFO  [opportunityUpsert] process.ProcessRunner run (ProcessRunner.java:145) - Creating Map
2012-11-06 13:26:54,327 INFO  [opportunityUpsert] action.OperationInfo instantiateAction (OperationInfo.java:95) - Instantiating action for operation: upsert
2012-11-06 13:26:54,442 INFO  [opportunityUpsert] controller.Controller executeAction (Controller.java:123) - executing operation: upsert
2012-11-06 13:26:54,444 INFO  [opportunityUpsert] action.AbstractAction execute (AbstractAction.java:115) - Loading: upsert
2012-11-06 13:26:54,613 ERROR [opportunityUpsert] visitor.DAOLoadVisitor visit (DAOLoadVisitor.java:92) - Error converting value to correct data type: Failed to parse date: close_date
org.apache.commons.beanutils.ConversionException: Failed to parse date: close_date
at com.salesforce.dataloader.dyna.DateConverter.convert(DateConverter.java:186)
at org.apache.commons.beanutils.BeanUtilsBean.convert(BeanUtilsBean.java:1078)
at org.apache.commons.beanutils.BeanUtilsBean.copyProperty(BeanUtilsBean.java:437)
at org.apache.commons.beanutils.BeanUtilsBean.copyProperties(BeanUtilsBean.java:270)
at org.apache.commons.beanutils.BeanUtils.copyProperties(BeanUtils.java:137)
at com.salesforce.dataloader.dyna.SforceDynaBean.convertToDynaBean(SforceDynaBean.java:210)
at com.salesforce.dataloader.action.visitor.DAOLoadVisitor.visit(DAOLoadVisitor.java:89)
at com.salesforce.dataloader.action.AbstractLoadAction.visit(AbstractLoadAction.java:79)
at com.salesforce.dataloader.action.AbstractAction.execute(AbstractAction.java:125)
at com.salesforce.dataloader.controller.Controller.executeAction(Controller.java:124)
at com.salesforce.dataloader.process.ProcessRunner.run(ProcessRunner.java:149)
at com.salesforce.dataloader.process.ProcessRunner.run(ProcessRunner.java:100)
at com.salesforce.dataloader.process.ProcessRunner.main(ProcessRunner.java:253)
2012-11-06 13:26:54,645 INFO  [opportunityUpsert] progress.NihilistProgressAdapter doneSuccess (NihilistProgressAdapter.java:63) - The operation has fully completed.  There were 0 successful upserts and 1 errors.

 

 
I have searched so many sources and try different methods but still cannot find an answer. I would extremely appreciate if you could help me with this pain. Thank you very much!
Best Answer chosen by Admin (Salesforce Developers) 
ZweeZwee

Thanks, I tried that as well alr but it didn't work.

 

However I somehow manage to get it to work today by uninstalling all java, including OpenSDK, and dataloader+cliq, then reinstall everything. Then this time I link the opportunityExport.sh file created by Cliq to the latest mySQL-connector-java-5.1.22-bin.jar. And somehow it stops complaining.

 

Tottaly don't know why it works, but just post here so other people can see.

All Answers

XackXack

not sure if im stating the obvious or not, however the close date that you getting from your SQL server appears to be of the wrong data type.

 

Salesforce Date type (Closed date) needs to be: YYYY/MM/DD is your SQL extract pulling it out as a different format?
ZweeZwee

Thanks, I tried that as well alr but it didn't work.

 

However I somehow manage to get it to work today by uninstalling all java, including OpenSDK, and dataloader+cliq, then reinstall everything. Then this time I link the opportunityExport.sh file created by Cliq to the latest mySQL-connector-java-5.1.22-bin.jar. And somehow it stops complaining.

 

Tottaly don't know why it works, but just post here so other people can see.

This was selected as the best answer