To export data from Salesforce, run the bulk tool that comes with the Connect for ODBC Driver in the samples\bulk folder.
Run this tool : bulk "DSN=XXX;UID=YYY;PWD=ZZZ"
Select 'Export' and provide requested information.
To load the data in the database, make sure the table does exist in the destination database.
Then, run the bulk tool : bulk "DSN=XXX;UID=YYY;PWD=ZZZ"
Select "Load" and provide requested information.
When loading salesforce data back in salesforce, the below actions must be taken into account :
1. Salesforce does not allow bulk loading of data into the auto-generated ROWID column. As a result, this data needs to be removed from the CSV file that is being used as input since the data in it was created by exporting all the rows from a Salesforce table.
The driver’s bulk load table argument has a limitation in that you can only pass just the table name if the input file contains data for ALL the columns in the table. As Salesforce does not allow bulk load into the ROWID field, modify the table name argument so that it contains a column list with ROWID left out. For example :
account(SYS_NAME, TYPE, PARENTID, BILLINGSTREET, BILLINGCITY, BILLINGSTATE, BILLINGPOSTALCODE, BILLINGCOUNTRY, BILLINGLATITUDE, BILLINGLONGITUDE, SHIPPINGSTREET, SHIPPINGCITY, SHIPPINGSTATE, SHIPPINGPOSTALCODE, SHIPPINGCOUNTRY, SHIPPINGLATITUDE, SHIPPINGLONGITUDE, PHONE, FAX, ACCOUNTNUMBER, WEBSITE, PHOTOURL, SIC, INDUSTRY, ANNUALREVENUE, NUMBEROFEMPLOYEES, OWNERSHIP, TICKERSYMBOL, DESCRIPTION, RATING, SITE, SYS_OWNERID, LASTVIEWEDDATE, LASTREFERENCEDDATE, JIGSAW, JIGSAWCOMPANYID, ACCOUNTSOURCE, SICDESC, CUSTOMERPRIORITY, SLA, ACTIVE, NUMBEROFLOCATIONS, UPSELLOPPORTUNITY, SLASERIALNUMBER, SLAEXPIRATIONDATE)
2. The tableName variable in the bulk.c code is too small to hold this value so redefine it and recompile the app:
char tableName[1024];
3. At this point the load will still fail an error indicating that there were invalid column values. Check the .csv file to see if there are no additional and unexpected line endings in it.
4. Edit the file so that all the fields for a single row are on a single line and remove the values for ROWID.
5. Edit the xml for the table definition and remove the ROWID field.
6. If the bulk load still fails, look in the Salesforce UI at the MonitoràJobsàBulk Data Load Jobs screen, if an error is captured, like :
"Id","Success","Created","Error"
"","false","false","INVALID_FIELD_FOR_INSERT_UPDATE:Unable to create/update fields: JigsawCompanyId, LastReferencedDate, PhotoUrl, LastViewedDate. Please check the security settings of this field and verify that it is read/write for your profile or permission set.:JigsawCompanyId LastReferencedDate PhotoUrl LastViewedDate --"
So there are now some fields in the data that are not allowed to be in the bulk insert. Modify the input CSV and XML files and tableName(columnList) value used in the bulk load sample to exclude these fields.
