Copying data from Salesforce to SQL Server

It can be handy to copy data from Salesforce to SQL Server if one would like to analyse the data. After all, the query language in Salesforce is not easy to use, whereas SQL Server allows easy access to data, good analytical possibilities and rapid response time.

Currently, Python is my favorite tool. It allows to achieve great results with a minimum of effort.

The Python code to do so is:

from simple_salesforce import Salesforce
import pandas as pd
import pyodbc
from sqlalchemy import create_engine

sf = Salesforce(username='tom', password='wacht', security_token='abc')
stmt = "SELECT field1, field2 FROM Table"

queryOutput = sf.query_all(stmt)
ff = pd.DataFrame(queryOutput['records'])
if ff.empty==False:
  ff.drop(ff.columns[0], axis=1, inplace=True)
  nmbrRows = ff.shape[0]
  print("Number of rows in Salesforce "+ str(nmbrRows))
else:
  print('No records in Salesforce')

engine = create_engine("mssql+pyodbc://user:psw@dsn")
ff.to_sql('Table', schema='dbo', con = engine, chunksize=100, method='multi', index=False, if_exists='replace')