Apex Code Development You need to sign in to do that
Don't have an account?
HaydenMuhl Need help using Data Loader to insert into dBase or MS Access
One of our old record keeping systems is a dBase V system. I'm trying to set up Data Loader so that it can update records in this old system.
I've been able to configure all the necessary files to extract data to CSV from the command line, but I don't know how to properly configure database-conf.xml to store this data in our dBase V system. I can easily link the dBase tables into an Access database, so if there is an easy way to insert this data into Access, that would also work.
Since there are no JDBC drivers for either dBase or Access, I'm trying to use the Sun JDBC-ODBC bridge to insert data into Access. Data Loader is balking at the "url" parameter needed to locate a database. Here is my database-conf.xml file.
<!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="sun.jdbc.odbc.JdbcOdbcDriver" />
<property name="url" value="file:///C:/PTIsynch/synch.mdb" />
</bean>
<bean id="PTI_InsertSFUser"
class="com.salesforce.lexiloader.dao.database.DatabaseConfig"
singleton="true">
<property name="sqlConfig" ref="PTI_InsertSFUserSql" />
<property name="dataSource" ref="dbDataSource" />
</bean>
<bean id="PTI_InsertSFUserSql"
class="com.salesforce.lexiloader.dao.database.SqlConfig"
singleton="true">
<property name="sqlString">
<value>
INSERT INTO SFUsers
(SFID, Fname, Lname)
VALUES (@sfdcID@, @sfdcFirstName@, @sfdcLastName@)
</value>
</property>
<property name="sqlParams">
<map>
<entry key="sfdcID" value="java.lang.String" />
<entry key="sfdcFirstName" value="java.lang.String" />
<entry key="sfdcLastName" value="java.lang.String" />
</map>
</property>
</bean>
</beans>
I solved the problem. To connect to an MS Access database you need the following values in the data source bean.
<property name="driverClassName" value="sun.jdbc.odbc.JdbcOdbcDriver" />
<property name="url" value="jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=yourDB.mdb" />
I believe you can link to a dBase system similarly using this property:
<property name="url" value="jdbc:odbc:DRIVER={Microsoft dBase Driver (*.dbf)};DBQ=yourfile.dbf" />
I haven't tested this yet, though.
All Answers
I solved the problem. To connect to an MS Access database you need the following values in the data source bean.
<property name="driverClassName" value="sun.jdbc.odbc.JdbcOdbcDriver" />
<property name="url" value="jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=yourDB.mdb" />
I believe you can link to a dBase system similarly using this property:
<property name="url" value="jdbc:odbc:DRIVER={Microsoft dBase Driver (*.dbf)};DBQ=yourfile.dbf" />
I haven't tested this yet, though.
I want to do something similar for MySQL, how did you find the specific information to connect to these databases? Thanks,
J
The Data Loader has an example connecting to an Oracle database, and uses a string that starts "jdbc:oracle". I was using the JDBC-ODBC bridge, and stumbled onto a forum post with the right url string, starting with "jdbc:odbc".
Here's what I found when I googled "jdbc:mysql".
http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html
It looks like the url string is used as a parameter in the Java functions DriverManager.getConnection() or Driver.connect().
Edit: Sorry about the smilies. It's taking a colon and a letter and turning them into a smily face. I'm too lazy to correct it on this post.
Thanks for the link. Ok using the examples and with the correct (hopefully, let´s see) Mysql information I have now set up a database-conf.xml file with 3 beans:
id="dbDataSource"
id="insertAccount"
id="insertAccountSql"
(In my example I am trying to insert data into MySQL). Do you know how to call these using process.bat?
Thanks again,
J
You have to supply process.bat with a conf directory and a process name.
> process.bat [yourConfDir] [yourProcessName]
yourConfDir refers to whatever directory you set up to contain config.properties, process-conf.xml and database-conf.xml.
yourProcessName corresponds to the name of a bean in process-conf.xml.
To get process.bat to use the settings in database-conf.xml, you will need to set the dataAccess.name parameter to the name of a databaseConfig bean in database-conf.xml.
If you don't have a copy of the Data Loader User Guide, here's a link. It's a little more comprehensive than the online documentation.
http://na1.salesforce.com/help/doc/en/salesforce_data_loader.pdf
Thanks again. For some reason I am getting a:
Sql error: Cannot load JDBC driver class 'com.mysql.jdbc.Driver'
Message even though I installed a Java MySQL connector and set the CLASSPATH variable correctly. Unsure how....
Thanks again,
J
I don't know how to fix that. We don't use MySQL here, so I can't do any testing.
FYI, to anyone else trying to use the Data Loader and dBase tables.
I was able to use Access as a front end to the dBase tables. I didn't have to change any settings to write to a table linked to an Access database, instead of a native Access table.
Hi dev_jh ,
I am also trying to connect to MySQL using Apex dataloader. Please post the solution if you have resoved the error message.
Thanks
Sue
Hi Sue,
I didn´t because I followed a different route. I installed Apatar, a great Open Source Free tool that allows you to connect Salesforce with MySQL (and many other DBs / Apps). You can find it in the Appexchange.
All the best,
J
I didn´t Sue, but I am using a free tool to connect SF and MySQL called Apatar. You can find it in the Appexchange. It is fairly easy to use and much friendlier than the obscure dataloader cmi.
Good luck,
J
I am trying to export account data to MS Access database. I am using Apex Data Loader 14. I have configured the files process-conf.xml and database-conf.xml. When I run the batch process, I can see on the board the follwing message:
585938 [proCrearClientesBaseDatos] INFO com.salesforce.lexiloader.controller.Controller - executing operation: extract
585953 [proCrearClientesBaseDatos] DEBUG com.salesforce.lexiloader.client.PartnerClient - Beginning web service operation: query
587375 [proCrearClientesBaseDatos] DEBUG com.salesforce.lexiloader.dao.database.DatabaseReader - Successfully updated 0 of 15 total updated rows in the database
587391 [proCrearClientesBaseDatos] INFO com.salesforce.lexiloader.action.progress.NihilistProgressAdapter - Processed 15 of 15 total records. Rate: 1
148000 records per hour. Estimated time to complete: 0 minutes and 0 seconds. There are 15 successes and 0 errors.
587391 [proCrearClientesBaseDatos] INFO com.salesforce.lexiloader.action.progress.NihilistProgressAdapter - The extract has fully completed. There were 15 successful extracts and 0 errors.
But when I open the MS Access file, the table is empty. Why does this happen?
Process-conf.xml
<bean id="proCrearClientesBaseDatos" class="com.salesforce.lexiloader.process.ProcessRunner" singleton="false">
<description>DatabaseAccountExtract job gets account info from salesforce and updates or inserts info into database."</description>
<property name="name" value="proCrearClientesBaseDatos"/>
<property name="configOverrideMap">
<map>
<entry key="process.enableLastRunOutput" value="false"/>
<entry key="sfdc.debugMessages" value="false"/>
<entry key="sfdc.debugMessagesFile" value="C:\Documents and Settings\Juan Pablo Garzon\My Documents\Temporal\Prueba Data Loader\Bitacora.log"/>
<entry key="sfdc.endpoint" value="https://www.salesforce.com"/>
<entry key="sfdc.username" value="jpgposso@hotmail.com"/>
<entry key="sfdc.password" value="f12954cf7d5688c433bbad5ef6fb9aed8ba05bc84603a313bed62f88f395f9af364ce991c4a075db"/>
<entry key="process.encryptionKeyFile" value="C:\Documents and Settings\Juan Pablo Garzon\My Documents\Temporal\Prueba Data Loader\Clave.key"/>
<entry key="sfdc.timeoutSecs" value="600"/>
<entry key="sfdc.loadBatchSize" value="200"/>
<entry key="sfdc.entity" value="Account"/>
<entry key="sfdc.extractionRequestSize" value="500"/>
<entry key="sfdc.extractionSOQL" value="SELECT Id, Name FROM Account"/>
<entry key="process.operation" value="extract"/>
<entry key="process.mappingFile" value="C:\Documents and Settings\Juan Pablo Garzon\My Documents\Temporal\Prueba Data Loader\mapCrearClientesBaseDatos.sdl"/>
<entry key="dataAccess.type" value="databaseWrite"/>
<entry key="dataAccess.name" value="datCrearClientesBaseDatos"/>
</map>
</property>
</bean>
Database-conf.xml
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<bean id="conexionBaseDatos" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="sun.jdbc.odbc.JdbcOdbcDriver"/>
<property name="url" value="jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\Documents and Settings\Juan Pablo Garzon\My Documents\Temporal\Prueba Data Loader\Prueba.mdb"/>
<property name="username" value=""/>
<property name="password" value=""/>
<property name="defaultAutoCommit" value="true"/>
</bean>
<bean id="datCrearClientesBaseDatos" class="com.salesforce.lexiloader.dao.database.DatabaseConfig" singleton="true">
<property name="sqlConfig" ref="sqlInsertarCliente"/>
<property name="dataSource" ref="conexionBaseDatos"/>
</bean>
<bean id="sqlInsertarCliente"
class="com.salesforce.lexiloader.dao.database.SqlConfig" singleton="true">
<property name="sqlString">
<value>
INSERT INTO Clientes
(Id, Name)
VALUES
(@Id@, @Name@)
</value>
</property>
<property name="sqlParams">
<map>
<entry key="Id" value="java.lang.String"/>
<entry key="Name" value="java.lang.String"/>
</map>
</property>
</bean>
</beans>
mapCrearClientesBaseDatos.sdl
# Account Insert Mapping values for query from Salesforce (left) and insert/update to Oracle (right)
# SalesforceFieldName=OracleFieldName
Id=Id
Name=Name
### These fields can be used for REP.INT_PARTIES table
##BillingStreet=billing_address
##Sic=sic_code