CODEWITHSUNDEEP
pythonsqlsql-serverms-accesspandas
anonymous
anonymous

Reputation: 895

How to convert a Microsoft Access Database to a SQL database (and then open it with pandas)?

Let's say I have the following Microsoft Access Database: random.mdb.

The main thing I'm trying to achieve is to use read_sql() from pandas so that I can work with the data I have using python. How would I approach this? Is there a way to convert the Microsoft Access database to a SQL database... to eventually pass in to pandas (all in python)?

Upvotes: 0

Views: 4191

Answers (3)

anonymous
anonymous

Reputation: 895

Figured out a simple way to do this with pyodbc (I'm going to type an arbitrary example below)!

import pandas as pd
import pyodbc

For some reason the MDB path needs double backslashes in place of each backslash.

MDB = 'C:\\Some\\random\\path\\here.mdb'
DRV = '{Microsoft Access Driver (*.mdb, *.accdb)}'
con = pyodbc.connect('DRIVER={};DBQ={}'.format(DRV, MDB))

query = """select * from [Some Table Name] where Sector = 'Some Sector'"""
dataframe = pd.read_sql(query, con)

Upvotes: 2

Victor Santiago
Victor Santiago

Reputation: 30

Download the Microsoft SQL Server Migration Assistant v6.0 for Access

Make sure if your computer is 64 bit then the migration assistant tool will be 64 bit so your access software needs to be 64 bit too. Just open the SQL Server Migration Assistant tool and select the access .mdb database or where all the tables are store if you have them split and import them then select the connection to the SQL Server and just click next to the end and that will create the database, all tables, and migrate all the data to your SQL Server.

Upvotes: 0

Vishmay Shah
Vishmay Shah

Reputation: 21

use sql server import export module to convert, but you will need table structure ready in sql server or there may be many other utilities

Upvotes: 1

Related Questions