Bottom Page

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 How to import data from database to excel
#1
I am using microsoft access database.
I want to copy data from the table with column names and paste it into a excel file.
I try to copy but I got some errors

import openpyxl as op
import pyodbc

con = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path;')
cr = con.cursor()



loc = (r'path of the excel file')

wb = op.load_workbook(loc)
sheet = wb.create_sheet(0)
sheet.title="Trial"

cr.execute('select * from  detail')
result=list(cr.fetchall())

sheet.append(cr.column_name)

for row in cr.columns(table='detail'):
    sheet.append(row.column_name)

for row in result:
    sheet.append(row)


Error:
Traceback (most recent call last): File "C:/Users/Nick/PycharmProjects/py/venv/excel.py", line 21, in <module> sheet.append(rows.column_name) File "C:\Users\Nick\PycharmProjects\py\venv\lib\site-packages\openpyxl\worksheet\worksheet.py", line 661, in append self._invalid_row(iterable) File "C:\Users\Nick\PycharmProjects\py\venv\lib\site-packages\openpyxl\worksheet\worksheet.py", line 792, in _invalid_row type(iterable)) TypeError: Value must be a list, tuple, range or generator, or a dict. Supplied value is <class 'str'>
Quote
#2
for row in result:
sheet.append(row)

In that, you need to make a row as List as follows:
for row in result:
listrow = list(row)
sheet.append(listrow)
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Get database used data space from pyodbc susja 0 47 Yesterday, 04:24 AM
Last Post: susja
  sqlite3 database does not save data across restarting the program SheeppOSU 1 170 Jul-24-2020, 05:53 AM
Last Post: SheeppOSU
  Import CSV data into array and turn into integers DoctorSmiles 5 236 Jul-16-2020, 10:47 AM
Last Post: perfringo
  data frame excel cell calulation buunaanaa 1 344 Jul-04-2020, 06:00 PM
Last Post: buunaanaa
  Importing data from a text file into an SQLite database with Python macieju1974 7 362 Jun-29-2020, 08:51 PM
Last Post: buran
  Exporting data from python into excel Zankawah 5 294 Jun-02-2020, 03:17 AM
Last Post: buran
  Joining Excel data into existing Excel file RemFreeman 0 186 May-08-2020, 04:01 PM
Last Post: RemFreeman
  How can I copy and paste data from text file into an Excel sheet? IrishOrigi 14 849 Mar-19-2020, 07:25 PM
Last Post: Larz60+
  Filtering Excel Document Data Based On Numerical Values eddywinch82 30 3,135 Feb-25-2020, 06:08 PM
Last Post: eddywinch82
  Insert data into sql after joining two excel data from python NeerajYadav 2 344 Jan-29-2020, 02:29 PM
Last Post: parthi1705

Forum Jump:


Users browsing this thread: 1 Guest(s)