Python Forum
How to import data from database to excel
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'>
Reply
#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)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How to detect abnormal data in big database python vanphuht91 5 1,063 Jun-27-2023, 11:22 PM
Last Post: Skaperen
  Copy data from Excel and paste into Discord (Midjourney) Joe_Wright 4 1,923 Jun-06-2023, 05:49 PM
Last Post: rajeshgk
  Plot a pandas data fram via pyqtgraph with an modul import and qt designer widget Nietzsche 0 801 May-29-2023, 02:42 PM
Last Post: Nietzsche
  Database that can compress a column, or all data, automatically? Calab 3 1,119 May-22-2023, 03:25 AM
Last Post: Calab
Video doing data treatment on a file import-parsing a variable EmBeck87 15 2,665 Apr-17-2023, 06:54 PM
Last Post: EmBeck87
  Reading data from excel file –> process it >>then write to another excel output file Jennifer_Jone 0 1,046 Mar-14-2023, 07:59 PM
Last Post: Jennifer_Jone
  Python 3.11 data import question love0715 2 771 Mar-05-2023, 06:50 PM
Last Post: snippsat
  Import XML file directly into Excel spreadsheet demdej 0 798 Jan-24-2023, 02:48 PM
Last Post: demdej
  How to properly format rows and columns in excel data from parsed .txt blocks jh67 7 1,799 Dec-12-2022, 08:22 PM
Last Post: jh67
  Trying to Get Arduino sensor data over to excel using Python. eh5713 1 1,616 Dec-01-2022, 01:52 PM
Last Post: deanhystad

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020