{"id":3402,"date":"2021-01-19T23:02:25","date_gmt":"2021-01-19T22:02:25","guid":{"rendered":"http:\/\/van-maanen.com\/?p=3402"},"modified":"2021-01-19T23:02:25","modified_gmt":"2021-01-19T22:02:25","slug":"copying-data-from-salesforce-to-sql-server","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=3402","title":{"rendered":"Copying data from Salesforce to SQL Server"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Currently, Python is my favorite tool. It allows to achieve great results with a minimum of effort.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The Python code to do so is:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">from simple_salesforce import Salesforce<br>import pandas as pd<br>import pyodbc<br>from sqlalchemy import create_engine<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">sf = Salesforce(username='tom', password='wacht', security_token='abc')\nstmt = \"SELECT field1, field2 FROM Table\"<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">queryOutput = sf.query_all(stmt)\nff = pd.DataFrame(queryOutput['records'])\nif ff.empty==False:\n  ff.drop(ff.columns[0], axis=1, inplace=True)\n  nmbrRows = ff.shape[0]\n  print(\"Number of rows in Salesforce \"+ str(nmbrRows))\nelse:\n  print('No records in Salesforce')<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">engine = create_engine(\"mssql+pyodbc:\/\/user:psw@dsn\")\nff.to_sql('Table', schema='dbo', con = engine, chunksize=100, method='multi', index=False, if_exists='replace')<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3402","post","type-post","status-publish","format-standard","hentry","category-allgemein"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3402","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3402"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3402\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3402"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3402"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3402"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}