Python Read In Google Spreadsheet Using Pandas

Question

I have file in Google sheets I want to read it into a Pandas Dataframe. But gives me an error i don't know what's it. this is the code :

import pandas as pd
sheet_id = "1HUbEhsYnLxJP1IisFcSKtHTYlFj_hHe5v21qL9CVyak"
df = pd.read_csv(f"https://docs.google.com/spreadsheets/d/{sheet_id}/export? 
gid=556844753&format=csv")
print(df)

And this is the error :

File "c:\Users\blaghzao\Documents\Stage PFA(Laghzaoui Brahim)\google_sheet.py", line 3, in <module>
    df = pd.read_csv(f"https://docs.google.com/spreadsheets/d/{sheet_id}/export?gid=556844753&format=csv")
  File "C:\Users\blaghzao\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\util\_decorators.py", line 311, in wrapper
    return func(*args, **kwargs)
  File "C:\Users\blaghzao\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\parsers\readers.py", line 680, in read_csv
    return _read(filepath_or_buffer, kwds)
  File "C:\Users\blaghzao\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\parsers\readers.py", line 581, in _read
    return parser.read(nrows)
  File "C:\Users\blaghzao\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\parsers\readers.py", line 1255, in read
    index, columns, col_dict = self._engine.read(nrows)
  File "C:\Users\blaghzao\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\parsers\c_parser_wrapper.py", line 225, in read
    chunks = self._reader.read_low_memory(nrows)
  File "pandas\_libs\parsers.pyx", line 805, in pandas._libs.parsers.TextReader.read_low_memory
  File "pandas\_libs\parsers.pyx", line 861, in pandas._libs.parsers.TextReader._read_rows
  File "pandas\_libs\parsers.pyx", line 847, in pandas._libs.parsers.TextReader._tokenize_rows
  File "pandas\_libs\parsers.pyx", line 1960, in pandas._libs.parsers.raise_parser_error
pandas.errors.ParserError: Error tokenizing data. C error: Expected 89 fields in line 3, saw 243

Sounds a lot like a data alignment issue. Pandas is for data with a consistent number of columns while spreadsheet programs can be filled arbitrarily. Looks like you have 89 columns on your first line but 243 on the third. — Joooeey
– Joooeey, Commented Aug 4, 2022 at 9:08

Nibras Ahmed · Accepted Answer · 2022-08-04 09:21:01Z

5

Remove gid from code

import pandas as pd
sheet_id = "1HUbEhsYnLxJP1IisFcSKtHTYlFj_hHe5v21qL9CVyak"
df = pd.read_csv(f"https://docs.google.com/spreadsheets/d/{sheet_id}/export?format=csv")
print(df)

Click on link for sample image

edited Aug 4, 2022 at 9:21

answered Aug 4, 2022 at 9:20

Nibras Ahmed

514 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brahim Laghzaoui Over a year ago

No doesn't work. and gid i want it to specify witch spreadsheet.

Brahim Laghzaoui · Accepted Answer · 2022-08-04 09:32:19Z

3

I found the answer, the problem it's just with access permissions of the file.

enter image description here

edited Aug 4, 2022 at 9:32

answered Aug 4, 2022 at 9:31

Brahim Laghzaoui

811 silver badge6 bronze badges

Comments

Lanre · Accepted Answer · 2022-08-04 09:21:46Z

1

As far as I know this error rises using comma delimiter and you have more commas then expected.

Can you try with below read_csv() method to avoid them;

df = pd.read_csv(f"https://docs.google.com/spreadsheets/d/{sheet_id}/export? gid=556844753&format=csv", on_bad_lines='skip')

This will avoid bad lines so you can identify problem depending on skipped lines. I believe your csv format export is not matching with what pandas read_csv() expects.

answered Aug 4, 2022 at 9:21

Lanre

3302 silver badges6 bronze badges

Comments

L Tyrone · Accepted Answer · 2024-01-29 21:11:06Z

0

You can publish the particular sheet to the web and read the web link as csv. Make sure the sheet is accessible to anyone who has the link.

enter image description here

Then read the web link to pandas csv

import pandas as pd
pd.read_csv('published_link')

edited Jan 29, 2024 at 21:11

L Tyrone

8,36123 gold badges34 silver badges47 bronze badges

answered Jan 27, 2024 at 11:58

Tiamiyu

11 bronze badge

Collectives™ on Stack Overflow

Python Read In Google Spreadsheet Using Pandas

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related