topic How to convert a Google Spreadsheet to a .csv file with Python in Python Questions

How to convert a Google Spreadsheet to a .csv file with Python

JosephGrove — Wed, 13 Nov 2013 23:32:05 GMT

I am working with a google spreadsheet that shows active big foot sightings data. What I am trying to do is convert the google spreadsheet to a .csv file so that I can then use geoprocessing tools placed in GIS Model Builder to take the .csv file and turn it into a table, and then a point shapefile showing the xy coordinates. That part is easy. It's just the first step that has me. I am receiving a syntax error when I try to run the python script. Here is what I have:

import csv
import gspread

g = gspread.login('skiesgoinggreen@gmail.com', '???')

docid = "0AgNp9UJ4CX93dHl3RW9GRXJDS3kxaXRJMGNqWmhQWVE"

spreadsheet = g.open_by_key(docid)
for i, worksheet in enumerate(spreadsheet.worksheets()):
    filename = docid + '-worksheet' + str(i) + '.csv'
    with open(filename, 'wb') as f:
        writer = csv.writer(f)
        writer.writerows(worksheet.get_all_values())

Thank you in advance for your help and suggestions.

Re: How to convert a Google Spreadsheet to a .csv file with Python

JasonScheirer — Sat, 11 Dec 2021 20:11:30 GMT

Please use the [#] button in the formatting toolbar on your code when you post. I can't see where your indentation is. Does it look like this?

import csv
import gspread

g = gspread.login('skiesgoinggreen@gmail.com', '???')

docid = "0AgNp9UJ4CX93dHl3RW9GRXJDS3kxaXRJMGNqWmhQWVE"

spreadsheet = g.open_by_key(docid)
for i, worksheet in enumerate(spreadsheet.worksheets()):
    filename = docid + '-worksheet' + str(i) + '.csv'
    with open(filename, 'wb') as f:
        writer = csv.writer(f)
        writer.writerows(worksheet.get_all_values())

Re: How to convert a Google Spreadsheet to a .csv file with Python

JosephGrove — Thu, 14 Nov 2013 23:44:15 GMT

What you changed it to is correct on what the script is supposed to look like. I apologize about the formatting mistake.

Re: How to convert a Google Spreadsheet to a .csv file with Python

JasonScheirer — Fri, 15 Nov 2013 13:55:52 GMT

Once I indented it like that I no longer got a syntax error, so problem solved?

Re: How to convert a Google Spreadsheet to a .csv file with Python

JosephGrove — Sat, 11 Dec 2021 20:11:33 GMT

Thank you Mr. Scheirer for sticking with me and continuing to provide advice and guidance. I ran the code using Python Win and then Python Idle 2.7 and am still receiving a syntax error on line 11 on the word open. With me being very new to Python I am not sure if I have to add anything extra to this script like specific file names or pathways as I am currently just running the code as seen below.

Thank you again for your patience and help.

import csv
import gspread

g=gspread.login('skiesgoinggreen@gmail.com', 'XXXXXXXXXXXX')

docid = "0AgNp9UJ4CX93dHl3RW9GRXJDS3kxaXRJMGNqWmhQWVE"

spreadsheet = g.open_by_key(docid)
for i, worksheet in enumerate(spreadsheet.worksheets()):
    filename = docid + '-worksheet' + str(i) + '.csv'
    with open(filename, 'wb') as f:
        writer = csv.writer(f)
        writer.writerows(worksheet.get_all_values())

Re: How to convert a Google Spreadsheet to a .csv file with Python

JasonScheirer — Fri, 15 Nov 2013 18:39:29 GMT

What version of ArcGIS are you on?

Instead of

    with open(filename, 'wb') as f:         writer = csv.writer(f)         writer.writerows(worksheet.get_all_values())

try

    writer = csv.writer(open(filename, 'wb'))     writer.writerows(worksheet.get_all_values())

Re: How to convert a Google Spreadsheet to a .csv file with Python

JosephGrove — Mon, 18 Nov 2013 10:27:42 GMT

Mr. Scheirer,

Thank you so much for the script modification suggestion. I am going to try to run the updated python script today. Currently I am using the trial version of arcgis 10.2.

Thank you again for your help.

Re: How to convert a Google Spreadsheet to a .csv file with Python

JosephGrove — Tue, 19 Nov 2013 07:53:04 GMT

This part of the script is working! Thank you Mr. Scheirer!