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)?
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)
'\t' is a tab character for example, and '\\' is a backslash. Alternatively you could use a raw string, like r'\this\is\fine'.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
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.
