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
BenjobobbyBenjobobby 

DataLoader: error reading from MySQL when inserting into Account object

Hi Folks. I'm trying to use the DataLoader CLI to read data out of my external Salesforce data warehouse (MySQL) and populate our sandbox instance with a few records. For unknown reasons, it seems DataLoader is reading in a row of data from my query, but is unable to parse the fields out, so I get an error saying '..."<really long concatenation of all fields>' can not be represented as java.sql.Date."   Here's the actual error:

2011-09-27 14:48:22,370 ERROR [account_insert] database.DatabaseReader readRow (DatabaseReader.java:195) - Error encounted trying to get value for column: Purchase_Date_TK1__c for row #1 (database execute query). Database configuration: queryAccountFull.  Error: Value 'Test Juror AccountPartner
123-456-78900015000000KWSyJAAX
Healthcare000Account for testing partner portal developments.true0
0000-00-00
0000-00-00' can not be represented as java.sql.Date.

Here's my database-conf.xml file:

<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<!-- Local salesforce warehouse database -->
<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:3306/salesforce"/>
    <property name="username" value="<a good account>"/>
    <property name="password" value="<valid password"/>
</bean>

<!-- Query all accounts for sandbox load -->
<bean id="queryAccountFull"
      class="com.salesforce.dataloader.dao.database.DatabaseConfig"
      singleton="true">
    <property name="sqlConfig" ref="queryAccountFullSql"/>
    <property name="dataSource" ref="dbDataSource"/>
</bean>

<bean id="queryAccountFullSql"
      class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true">
    <property name="sqlString">
        <value>
            SELECT
                    MasterRecordId,
                   `Name`,
                    `Type`,
                    ParentId,
                    BillingStreet,
                    BillingCity,
                    BillingState,
                    BillingPostalCode,
                    BillingCountry,
                    ShippingStreet,
                    ShippingCity,
                    ShippingState,
                    ShippingPostalCode,
                    ShippingCountry,
                    Phone,
                    Fax,
                    Website,
                    Id AS Sic,
                    Industry,
                    AnnualRevenue,
                    NumberOfEmployees,
                    Ownership,
                    Description,
                    IsPartner,
                    Vendor_ID__c,
                    liferay_organizationId__c,
                    Certified_By__c,
                    Toolkit_Purchased__c,
                    Purchase_Date_TK1__c,
                    Ship_Date_TK1__c,
                    Status__c
                FROM salesforce.account;
        </value>
    </property>
    <property name="columnNames">
        <list>
            <value>MasterRecordId</value>
            <value>Name</value>
            <value>Type</value>
            <value>ParentId</value>
            <value>BillingStreet</value>
            <value>BillingCity</value>
            <value>BillingState</value>
            <value>BillingPostalCode</value>
            <value>BillingCountry</value>
            <value>ShippingStreet</value>
            <value>ShippingCity</value>
            <value>ShippingState</value>
            <value>ShippingPostalCode</value>
            <value>ShippingCountry</value>
            <value>Phone</value>
            <value>Fax</value>
            <value>Website</value>
            <value>Sic</value>
            <value>Industry</value>
            <value>AnnualRevenue</value>
            <value>NumberOfEmployees</value>
            <value>Ownership</value>
            <value>Description</value>
            <value>IsPartner</value>
            <value>Vendor_ID__c</value>
            <value>liferay_organizationId__c</value>
            <value>Certified_By__c</value>
            <value>Toolkit_Purchased__c</value>
            <value>Purchase_Date_TK1__c</value>
            <value>Ship_Date_TK1__c</value>
            <value>Status__c</value>
        </list>
    </property>
</bean>

And here's my process-conf.xml file:

<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<!-- This is the "integrate" cs2.salesforce.com sandbox environment process-conf.xml file -->
<beans>
<!-- Insert all accounts from the local warehouse MySQL salesforce database into the sandbox instance. -->
    <bean id="account_insert"
          class="com.salesforce.dataloader.process.ProcessRunner"
          singleton="false">
        <description>Insert all accounts from the local warehouse MySQL salesforce database into the sandbox instance.</description>
        <property name="name" value="account_insert"/>
        <property name="configOverrideMap">
            <map>
                <entry key="sfdc.debugMessages" value="false"/>
                <entry key="sfdc.debugMessagesFile" value="./account_insert-debug-msgs.log"/>
                <entry key="process.statusOutputDirectory" value="./log/"/>
                <entry key="sfdc.endpoint" value="https://cs2.salesforce.com"/>
                <entry key="sfdc.username" value="<valid sandbox user account name/email>"/>
                <entry key="sfdc.password" value="<valid encrypted password>"/>
                <entry key="sfdc.timeoutSecs" value="600"/>
                <entry key="sfdc.loadBatchSize" value="50"/>
                <entry key="dataAccess.readBatchSize" value="1"/>
                <entry key="sfdc.entity" value="Account"/>
                <entry key="process.operation" value="insert"/>
                <entry key="process.mappingFile" value="./conf-test/account_insert.sdl"/>
                <entry key="dataAccess.name" value="queryAccountFull"/>
                <entry key="dataAccess.type" value="databaseRead"/>
                <entry key="process.initialLastRunDate" value="2011-09-27T00:00:00.000-0800"/>
            </map>
        </property>
    </bean>
</beans>

And finally, here's my mapping file:

#Mapping values
#Tue Sept 27 11:13:00 PDT 2011
MasterRecordId=MasterRecordId
Name=Name
Type=Type
ParentId=ParentId
BillingStreet=BillingStreet
BillingCity=BillingCity
BillingState=BillingState
BillingPostalCode=BillingPostalCode
BillingCountry=BillingCountry
ShippingStreet=ShippingStreet
ShippingCity=ShippingCity
ShippingState=ShippingState
ShippingPostalCode=ShippingPostalCode
ShippingCountry=ShippingCountry
Phone=Phone
Fax=Fax
Website=Website
Sic=Sic
Industry=Industry
AnnualRevenue=AnnualRevenue
NumberOfEmployees=NumberOfEmployees
Ownership=Ownership
Description=Description
IsPartner=IsPartner
Vendor_ID__c=Vendor_ID__c
liferay_organizationId__c=liferay_organizationId__c
Certified_By__c=Certified_By__c
Toolkit_Purchased__c=Toolkit_Purchased__c
Purchase_Date_TK1__c=Purchase_Date_TK1__c
Ship_Date_TK1__c=Ship_Date_TK1__c
Status__c=Status__c

If you're still reading - hey, thanks! Any help would be greatly appreciated. Suggestions to use some spiffy data manipulation tool (Apatar, Talend, etc) are not really what I'm looking for, but if you've got some freeware solution that doesn't involve the Windows platform, I'd be interested.

Thanks in advance! -Ben