populating my postgres with openpyxl

[willy Hakizimana]

I am new to django/python and have been stuck.

What is wrong with my script?


from openpyxl import Workbook, load_workbook
import os
import psycopg2
import pycountry

#Make the dictionary of countries
country_dict = {}
country_list = list(pycountry.countries)
for country in country_list:
    country_dict[country.alpha2] = country.name




BASEDIR="/home/william/Documents/myfiles/hsdata/Importsbynumber/02 Chapter"


###open the db
try:
    conn = psycopg2.connect("dbname='mydb' user='yyy' password='xxx'")
except:
    print "This sucks, I failed to open the db."
   

#Initiate the cursor
cursor =conn.cursor()

#Get the file
for dirName, subdirList, fileList in os.walk(BASEDIR):
    #print('Found directory: %s' % dirName)
    for fname in fileList:
        #work on the file with openpyxl
        wb = load_workbook(filename = fname, use_iterators=True)
        ws = wb.active
       
        hsstring = str(ws['A2'].value)

        #print hsstring ###Test
        hsnumber = hsstring[9:13]
        product_description = hsstring[14:]
       
        #INSERT INTO Products table
        country_id = ''
       
       
        for row in ws.range('A15:F225'):   
            country_name = str(row[0].value)
            imports08 = float(row[1].value *1000)
            imports09 = float(row[2].value * 1000)
            imports10 = float(row[3].value * 1000)
            imports11 = float(row[4].value * 1000)
            imports12 = float(row[5].value *1000)
           
            for key, value in country_dict.iteritems():
                if country_name == value:
                    country_id = key
                    query ="INSERT INTO Imports(hs_number_id, country_id_id, imported_value2008, imported_value2009, imported_value2010, imported_value2011,        imported_value2012)VALUES(%s, %s, %s, %s, %s, %s, %s);"
                    data = (hsnumber, country_id, imports08, imports09, imports10, imports11, imports12)
                    cursor.execute(query, data)
                    conn.commit()
   

I keep getting this error, saying that there is 0207.xlsx is not there, but it is in the directory.

File "hsdata.py", line 34, in <module>
    wb = load_workbook(filename = fname, use_iterators=True)
  File "/home/william/.virtualenvs/hsdata/local/lib/python2.7/site-packages/openpyxl/reader/excel.py", line 125, in load_workbook
    raise InvalidFileException(unicode(e))
openpyxl.shared.exc.InvalidFileException: [Errno 2] No such file or directory: '0207.xlsx'

[Thomas Lockhart]

I keep getting this error, saying that there is 0207.xlsx is not there,
but it is in the directory.

File "hsdata.py", line 34, in <module>
wb = load_workbook(filename = fname, use_iterators=True)
File
"/home/william/.virtualenvs/hsdata/local/lib/python2.7/site-packages/openpyxl/reader/excel.py",
line 125, in load_workbook
raise InvalidFileException(unicode(e))
openpyxl.shared.exc.InvalidFileException: [Errno 2] No such file or
directory: '0207.xlsx'

It looks like os.walk() needs you to add the starting point (BASEDIR) to
the resulting file name.

Something like

... load_workbook(filename = os.path.join(BASEDIR, fname),...)

hth

- Tom