Pulling csv report in python and summing column into new workbook

Question

I'm trying to get some python scripts working that will go out and pull a specific Salesforce report as a csv, and then take a sum of column 6 and put that value into a new workbook. I would also like to be able to define the file name of the new workbook file. Thanks for your time! Code below:

Pull report from Salesforce:

from simple_salesforce import Salesforce
import requests
import base64
import json
import sys

sf = Salesforce(username="userid"
        ,password="password"
        ,security_token="token")

#print "get sid " + str(sf.session_id)
sid = str(sf.session_id)
urlin = "https://na52.salesforce.com/" + sys.argv[1] + "?
view=d&snip&export=1&enc=UTF-8&xf=csv"

response = requests.get(urlin,
          headers = sf.headers, cookies = {'sid' : sid})

#response.contents
print response.content

Possible duplicate of [Openpyxl setting number format](https://stackoverflow.com/questions/12387212/openpyxl-setting-number-format) — Mel, Oct 05 '17 at 12:17
Why are you even trying to convert it. A CSV is basically in excel format already — Joe, Oct 05 '17 at 12:18
That is a good point, I guess there is no reason to convert it to an actual excel file. If I leave it as a csv file, how could I sum up column 6 and post that value to a new workbook? — andyf117, Oct 05 '17 at 12:50
You could use `pandas` built in methods `DataFrame.to_excel` [excel](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_excel.html) or you can use the built in method `DataFrame.to_csv` [csv](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_csv.html) to process the data. You can even save data to a new sheet with pandas — Albo, Oct 05 '17 at 12:55
@Joe I have edited this post based on the response you gave. — andyf117, Oct 05 '17 at 19:51
@andyf117 I wan to clarify. Are you trying to just create a new file with the sum of a specific column? — Joe, Oct 05 '17 at 19:53
@andyf117 is your `response.content` printing the csv file you want — Joe, Oct 05 '17 at 20:13
@Joe Yes that's the goal here, sum a column in a csv file that was pulled through earlier scripts, and create a new file with the value. — andyf117, Oct 06 '17 at 17:23
@Joe No nothing prints using these scripts, however I am just running these in a batch file that drop the new csv file into my working directory — andyf117, Oct 06 '17 at 17:24
@andyf117 ok thats really pretty easy. I'll write that in a couple hours when I'm done work and post the answer. — Joe, Oct 06 '17 at 17:24
@andyf117 ahhh... if nothing is printing then you have a whole new problem... — Joe, Oct 06 '17 at 17:25

score 0 · Accepted Answer · answered Oct 06 '17 at 20:16

Ok so here is a rough script that accomplishes what you want. You will need to tweak it to your needs but this basically takes a CSV file, sums the 6th column then writes that data to a new CSV file.

Replace "data.csv" with the name of your CSV file and it should work

import csv

new_data = []


def csv_parser():
    with open('data.csv', 'r') as file:
        reader = csv.reader(file)
        data = list(reader)
        for each in data:
            new_data.append(each[5])
        x = 0
        for part in new_data[1:]:
            x = x + int(part)
    with open('Sum_Data.csv', 'w') as final:
        final.write(new_data[0] + '\n')
        final.write(str(x))


csv_parser()

Pulling csv report in python and summing column into new workbook

1 Answers1