5 Best Ways to Convert Python CSV to MDB

by

💡 Problem Formulation: Data conversion is a common requirement in software development. Consider a scenario where you have a CSV file with data that you want to migrate into an MDB (Microsoft Access Database) format. The input is a CSV file containing structured data, and the desired output is an MDB file with the same data organized in tables.

Method 1: Using pandas and pyodbc Libraries

This method involves the utilization of the pandas library for data manipulation and analysis, and the pyodbc library for ODBC database connectivity. Together, they can be used to read a CSV file into a DataFrame and then export that DataFrame to an MDB file.

Here’s an example:

import pandas as pd
import pyodbc

# Step 1: Load CSV into DataFrame
df = pd.read_csv('your_data.csv')

# Step 2: Connect to an MDB file (assume it is already created)
conn_str = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path_to_your_mdb_file.mdb;'
conn = pyodbc.connect(conn_str)

# Step 3: Write DataFrame to MDB
for table_name in df.table_names:
    df.to_sql(table_name, conn, if_exists='replace', index=False)

conn.close()

The output will not be displayed, but the MDB file will now contain the tables filled with CSV data.

This snippet first reads the CSV file into a pandas DataFrame, then establishes a connection to the MDB file with pyodbc. After the connection is made, it writes the DataFrame’s data to the MDB using the to_sql method within a loop for each table name contained in the DataFrame.

Method 2: mdbtools Suite and pandas

The mdbtools suite provides a set of tools for interacting with MDB files on Unix. You can use these tools in combination with pandas to convert a CSV file into an MDB format. First, generate the necessary SQL statements using pandas, then apply them using the mdb-tools.

Here’s an example:

import os
import pandas as pd

# Step 1: Load CSV into DataFrame
df = pd.read_csv('your_data.csv')

# Step 2: Generate the SQL commands
command = ""
for index, row in df.iterrows():
    values = ", ".join(f"'{value}'" if isinstance(value, str) else str(value) for value in row)
    command += f"INSERT INTO your_table VALUES({values});\n"

# Write SQL commands to a file
with open("commands.sql", "w") as file:
    file.write(command)

# Step 3: Use mdb-tools to execute SQL commands
os.system("mdb-sql -d ';' -Fp your_mdb_file.mdb < commands.sql")

The output will be a sequence of SQL commands written to a file and then processed with mdb-sql.

This code converts a CSV file into a bunch of INSERT statements written to a file. Then, the unix command os.system() is used to execute these commands against the MDB file with mdb-sql. This is a somewhat lower-level approach and requires mdbtools to be installed.

Method 3: Accessing Microsoft Access through COM (Windows)

On Windows, Python can interact with Microsoft Access through the COM API using the pywin32 package, enabling one to create an MDB file directly from a CSV file.

Here’s an example:

import pandas as pd
import win32com.client

# Step 1: Load CSV into DataFrame
df = pd.read_csv('your_data.csv')

# Step 2: Access Microsoft Access via COM
access_app = win32com.client.Dispatch("Access.Application")
access_app.NewCurrentDatabase('path_to_new_mdb_file.mdb')

# Step 3: Import CSV data into the MDB file
access_app.DoCmd.TransferText(
    TransferType=win32com.client.constants.acImportDelim,
    TableName="your_table",
    FileName="path_to_your_csv_file.csv"
)

The output is a new MDB file with data imported from the CSV file.

This script creates a new MDB file and uses a COM object to execute Access’s TransferText command, which imports CSV data into the new MDB file. This method is Windows-specific and requires Microsoft Access installed on the machine.

Method 4: Using SQL Alchemy with a Python-to-Access Bridge such as pypyodbc

SQLAlchemy is a SQL toolkit and Object-Relational Mapping (ORM) library for Python that can be used in combination with a bridge library like pypyodbc to write CSV data to an MDB file.

Here’s an example:

import pandas as pd
import sqlalchemy as sa
import pypyodbc

# Step 1: Load CSV into DataFrame
df = pd.read_csv('your_data.csv')

# Step 2: Create SQL Alchemy engine for Access DB
connection_string = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path_to_your_mdb_file.mdb;'
engine = sa.create_engine('access+pyodbc:///?odbc_connect={}'.format(connection_string))

# Step 3: Write DataFrame to MDB
df.to_sql('your_table', con=engine, if_exists='replace', index=False)

The output will be data written directly into an Access database.

This script reads the CSV file into a pandas DataFrame, creates an SQLAlchemy engine that connects to an Access database using pypyodbc, and then writes the DataFrame to the database using the to_sql function from pandas.

Bonus One-Liner Method 5: Using MS Access’ Built-in “Get External Data” Feature

This isn’t a Python solution, but it’s worth mentioning. Use Microsoft Access’ built-in import feature, where in just a few clicks you can import a CSV file.

Here’s an example:

Click 'External Data' > 'Text File' in MS Access and follow the wizard prompts.

No output code snippet here, as this is a UI-based operation. Once completed, you will have the data from the CSV inside your MDB.

This method requires no coding at all. It is a simple and reliable way for users to create an MDB file from a CSV by using Microsoft Access’s own GUI import wizard, making it very accessible to those without programming skills. However, it is manual and cannot be automated.

Summary/Discussion

  • Method 1: pandas with pyodbc. Strengths: Fully programmable and quite flexible. Weaknesses: Requires ODBC setup and pandas may not handle very large datasets efficiently.
  • Method 2: mdbtools and pandas. Strengths: Suitable for Unix systems, can be wrapped in scripts for automation. Weaknesses: Requires UNIX environment and additional mdbtools setup.
  • Method 3: COM API with pywin32. Strengths: Direct interaction with Access, no ODBC needed. Weaknesses: Windows and MS Access specific, may be complex to automate.
  • Method 4: SQLAlchemy with pypyodbc. Strengths: Utilizes the power and flexibility of SQLAlchemy. Weaknesses: Less direct than using pandas with pyodbc, and setup can be more complex.
  • Bonus Method 5: MS Access’ “Get External Data”. Strengths: Very user-friendly, no coding required. Weaknesses: Not automatable, requires manual operation each time.