How to Convert Access (accdb) Table to a CSV in Python?

by
5/5 - (2 votes)

To convert a Microsoft Acces table (.accdb) to a CSV file in Python, use the following four steps:

  1. Establish database connection,
  2. Run the SQL query to select data,
  3. Store the data in the CSV using basic Python file I/O and the csv module, and
  4. Close the database connection.

Here’s a specific example with additional annotations and simplified for clarity:

import pyodbc
import csv


# 1. Example establish database connection
connection = pyodbc.connect("your connection string")

# 2. Run SQL query
cursor = connection.cursor()
cursor.execute('select * from XXX;')

# 3. Store the contents in "cursor" in the CSV using file I/O
with open('my_file.csv','w') as f:
    writer = csv.writer(f)
    writer.writerows([x[0] for x in cursor.description])
    writer.writerows(cursor)

# 4. Close the database connection
cursor.close()
conn.close()

Basically, as soon as you have mastered the art of pulling data into the cursor variable using the pyodbc module, you can use all different ways to write that data into the CSV using basic Python file I/O — such as the open() function or context managers — or even Pandas function to write DataFrames to CSV files.

If you’ve landed on this article, you’ll probably struggle with one or both of those issues:

  • Understanding basic Python features and functions—or knowing about them in the first place. In that case, join my free email academy for an infinite stream of learning content.
  • Understanding the database handling provided by Python’s Access interface. In that case, check out our in-depth tutorial on pyodbc on the Finxter blog.

I hope you learned something out of today’s tutorial, my friend! Here’s a joke to round things out:

Nerd Humor

Oh yeah, I didn’t even know they renamed it the Willis Tower in 2009, because I know a normal amount about skyscrapers. — xkcd (source)