python - Storing xlsx sheet in sql server -
i trying store xlsx sheet sql server using python , error:
sql server error traceback (most recent call last): file ".\script_to_extract_mh_data.py", line 104, in m_flag = sheet.cell(r, 22).value file "c:\python27\lib\site-packages\xlrd\sheet.py", line 401, in cell self._cell_types[rowx][colx], indexerror: array index out of range
import xlrd import pymssql book = xlrd.open_workbook("c:/users/talha/documents/maharashtradata.xlsx") sheet = book.sheet_by_index(0) # establish mssql connection servername = "192.168.1.100" database = "imddata" username = "saaa" password = "abcd@aassapla_2012" conn = pymssql.connect(server=servername, user=username, password=password, database=database) cursor = conn.cursor() #cursor = database.cursor() query = """insert forecast_db (srno, state, statecd, district, issuedon, day, n_rainfall, n_tempmax, n_tempmin, n_cloudcover, n_rhmax, n_rhmin, n_windspeed, n_winddirection, m_rainfall, m_tempmax, m_tempmin, m_cloudcover, m_rhmax, m_rhmin, m_windspeed, m_winddirection, m_flag) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""" r in range(1, sheet.nrows): srno = sheet.cell(r,0).value state = sheet.cell(r,1).value statecd = sheet.cell(r,2).value district = sheet.cell(r,3).value issuedon = sheet.cell(r,4).value day = sheet.cell(r,5).value n_rainfall = sheet.cell(r,6).value n_tempmax = sheet.cell(r,7).value n_tempmin = sheet.cell(r,8).value n_cloudcover = sheet.cell(r,9).value n_rhmax = sheet.cell(r,10).value n_rhmin = sheet.cell(r,11).value n_windspeed = sheet.cell(r,12).value n_winddirection = sheet.cell(r, 13).value m_rainfall = sheet.cell(r, 14).value m_tempmax = sheet.cell(r,15).value m_tempmin = sheet.cell(r, 16).value m_cloudcover = sheet.cell(r, 17).value m_rhmax = sheet.cell(r, 18).value m_rhmin = sheet.cell(r, 19).value m_windspeed = sheet.cell(r, 20) m_winddirection = sheet.cell(r, 21).value m_flag = sheet.cell(r, 22).value # assign values each row values = (srno, state, statecd, district, issuedon,day, n_rainfall, n_tempmax, n_tempmin, n_cloudcover, n_rhmax, n_rhmin, n_windspeed, n_winddirection, m_rainfall, m_tempmax, m_tempmin, m_cloudcover, m_rhmax, m_rhmin, m_windspeed, m_winddirection, m_flag) # execute sql query cursor.execute(query, values) # close cursor cursor.close() # close database connection database.close()
where doing wrong?
Comments
Post a Comment