Want to import, extracted Salesforce report content into pandas and then save it

Question

Hi I connected to the salesforce using simple_salesforce module and targeted it to the report i wanted to extract. At this moment I have the desired data in csv comma seperated format and when I write d.content it shows the data in comma separated but I want to have the same data in pandas dataframe.

d = session.get("https://nax.salesforce.com/xxxxxxxxxx?export=1&enc=UTF-8&xf=csv".format('xxxxxxxxxx'), headers=sf.headers, cookies={'sid': sf.session_id})

then

in[22]: d.content

out[22]: "whole comma seperated data"

I want the above data to be in pandas or saved in csv

when I write:

pd.DataFrame(d)

it gives an error out as

PandasError: DataFrame constructor not properly called!

Please tell me how can I take it further to save the following data in either csv or insert in pandas to save it further.

Possible duplicate of [How to create a Pandas DataFrame from String](http://stackoverflow.com/questions/22604564/how-to-create-a-pandas-dataframe-from-string) — IanS, Apr 27 '16 at 12:12
no it is not as the data was taken out from sfdc report and do not exist in any csv file yet — Maneet Giri, Apr 27 '16 at 12:20
'd.content' is to check content in the fetched data from Salesforce — Maneet Giri, Apr 27 '16 at 12:21
so if you see the code `d = session.get("https://nax.salesforce.com/xxxxxxxxxx?export=1&enc=UTF-8&xf=csv".format('xxxxxxxxxx'), headers=sf.headers, cookies={'sid': sf.session_id})` there is a link of salesforce, which is ""https://nax.salesforce.com/xxxxxxxxxx" this is a report which was fetched using the above code and now it lies in 'd' I want this to be saved in csv or to put into pandas dataframe — Maneet Giri, Apr 27 '16 at 12:25
See here for documentation: http://docs.python-requests.org/en/master/ — IanS, Apr 27 '16 at 12:28
'"Party","Account Name","Alias Name","Account ID","Account Classification"\n"123456","XYZ Inc.","株式会社XYZ", and so on — Maneet Giri, Apr 27 '16 at 12:30

score 7 · Answer 1 · edited Jul 11 '19 at 16:10

I know this is more detailed than you need but as someone who searched all over for a full answer for how to download salesforce reports to excel using python I wanted to share my answer:

from simple_salesforce import Salesforce
import pandas as pd
import requests
import io

# login info must be a string in "quotes" 
sf = Salesforce(username= # "UserName"
                ,password= # "Password" 
                ,security_token= # "Security Token" (can be reset in your settings in salesforce and will be sent via email)
                )

sid=sf.session_id
print ("get sid "+ sf.session_id)


#ReportID can be found when you open the report in the browser address bar as follows: https://instance.salesforce.com/[Alphanumeric ReportID]
ReportID=  #Alphanumeric Report ID as string in "quotes"

response = requests.get("https://instance.salesforce.com/"+ReportID+"?export=1&enc=UTF-8&xf=csv",
                  headers = sf.headers, cookies = {'sid' : sid})

df=pd.read_csv(io.StringIO(response.text))
df=df[:-5] #takes out the confidential information warning and copywrite info

#if your first column is a number field instead of string (text) field you will need the next line(Delete the '#')
#df=df.astype({"[first column name]":'float'})


writer = pd.ExcelWriter('Salesforce Output.xlsx') #this will write the file to the same folder where this program is kept
df.to_excel(writer,index=False,header=True)

writer.save()

I made the comments very simple so that people who do not regularly use python or beginners can learn from my experience.

Welcome to Stack Overflow, and thanks for the well-commented answer. :) — Jeremy, Apr 16 '18 at 18:10

score 3 · Accepted Answer · edited May 23 '17 at 12:31

3

The data returned by the server is contained in the text attribute of the response: d.text (see documentation here).

You can then use the answer here to create the dataframe from a string:

from StringIO import StringIO
pd.DataFrame.from_csv(StringIO(d.text))

edited May 23 '17 at 12:31

Community

1
1

answered Apr 27 '16 at 12:32

IanS

15,771
9
60
84

Yes, it worked very well. Thank you for your help and time. – Maneet Giri Apr 27 '16 at 12:39

Want to import, extracted Salesforce report content into pandas and then save it

2 Answers2