How do I make python read a CSV file that has been turned into an excel file?

Question

I was trying to make a program that would read a CSV file that I downloaded from the WorldBank about every countries GDP over time. I expected it to be able to first open the file, then read it, and then retrieve the Data. However, a giant error message appeared (so big in fact, I will place a separation between it and the code), and when I tried to solve those errors by searching answers on this website, there would only be another error. Here is the code. Thank you for anyone willing and able to help out.

import csv
from openpyxl import Workbook
wb = Workbook()

    with open(r'C:UsersMiguelDownloadsAPI_NY.GDP.PCAP.CD_DS2_en_csv_v2_1429392.zip') as csvfile:
    ws = wb.active
    ws.title = "Country Analyzer" #title of the program
    ws.sheet_properties.tabColor = "FFFFFF" #RGB values
sheet = wb.get_sheet_by_name('API_NY.GDP.PCAP.CD_DS2_en_csv_v2_1429392')
tuple(sheet['A1':'C3'])

---

Warning (from warnings module):
  File "C:UsersMiguelAppDataLocalProgramsPythonPython37-32I'm so tired of this Work.py", line 14
sheet = wb.get_sheet_by_name('API_NY.GDP.PCAP.CD_DS2_en_csv_v2_1429392')
DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]).
Traceback (most recent call last):
  File "C:UsersMiguelAppDataLocalProgramsPythonPython37-32I'm so tired of this Work.py", line 14, in <module>
    sheet = wb.get_sheet_by_name('API_NY.GDP.PCAP.CD_DS2_en_csv_v2_1429392')
  File "C:UsersMiguelAppDataLocalProgramsPythonPython37-32libsite-packagesopenpyxlcompat\__init__.py", line 38, in new_func1
    return func1(*args, **kwargs)
  File "C:UsersMiguelAppDataLocalProgramsPythonPython37-32libsite-packagesopenpyxlworkbookworkbook.py", line 247, in get_sheet_by_name
    return self[name]
  File "C:UsersMiguelAppDataLocalProgramsPythonPython37-32libsite-packagesopenpyxlworkbookworkbook.py", line 273, in __getitem__
    raise KeyError("Worksheet {0} does not exist.".format(key))
KeyError: 'Worksheet API_NY.GDP.PCAP.CD_DS2_en_csv_v2_1429392 does not exist.'

Ps- pls don't judge the name of the file

Answer 1

The file you are trying to open is a zip file. You are going to have to either first unzip the contents or if you want to automate this process, use Python to unzip the file for you - see here or below:

import zipfile
with zipfile.ZipFile(path_to_zip_file, 'r') as zip_ref:
        zip_ref.extractall(directory_to_extract_to)

I'd suggest starting by unzipping the file manually before trying to automate the unzip process.

As Nick.McDermaid mentioned, you will then need to determine what format the file is in. to ensure openpyxl can work with it. Per their documentation, "openpyxl is a Python library to read/write Excel 2010 xlsx/xlsm/xltx/xltm files".

If you believe your file is in one of these formats, try this simple code snippet to ensure you can open your file using openpyxl and read from it:

from openpyxl import load_workbook
wb = load_workbook('test.xlsx')
print wb.sheetnames

If that now works, you're ready to continue on with what you were trying to do. Check out the full documentation for reading from a file using openpyxl here: https://openpyxl.readthedocs.io/en/stable/usage.html#read-an-existing-workbook