Excel to Access Using Python Pandas

Migrating data with Python and Pandas is a snap, once you learn how to use the to_sql dataframe method. This is great for big systems like SQL Server and Oracle, but it won’t work for Microsoft Access until you add a small library to support the Access dialect.

Today we’ll migrate data from Excel to a Pandas dataframe, then to an Access database using the to_sql method, after first installing sqlalchemy-access to support our Access operations.

You can use this migration for all kinds of different data that you need in your MS Access database. While not nearly as fast as a native MS Access / VBA import procedure, it is handy for those who need to script operations for overnight or other scheduled tasks. It also saves you from the headache of having to write a loop to insert rows one-by-one.

Related Videos:
How to Install Pandas in Python
How to Filter and Sort a Pandas DataFrame
How to Create Summary Statistics in Pandas
How to Append Two Pandas DataFrames Together
How to Merge Pandas Dataframes in Python
Excel to Access Using Python Pandas
You are watching this video now!
How to Group Pandas Data with Totals
CSV to Microsoft Access Using Python and Pandas

Follow us on social media:

Get Microsoft Office including Access:

Got a YouTube Channel? I use TubeBuddy, it is awesome. Give it a try:

excel to access
insert excel data into access tables using python
copy excel data into access using python
migrate from excel to access
migrate data
pandas sqlalchemy access
pandas to_sql access
dataframe to accdb
Migrate Data Using Panda
sqlalchemy python access
dataframe to ms access
sqlalchemy python
sqlalchemy access relationship
sqlalchemy access
data analysis
sean mackenzie

[myersanthonya]

THIS IS AWESOME!!! Sorry for the shouting. This I have wanted to do. WOW! Thank you!

[DataAnalysiswithPython]

Hello Sean, thanks for your lecture. I got an error :NoSuchModuleError: Can't load plugin:
Can you please help me how i can resolve it?

[digvijaygujale]

Thanks for your video, you resolve my big issue . :)

[prashanth.gnayak]

Hello Sean, let's say I have 300 columns in my existing access database and I have only 60 columns of data in excel at that time I want to append the data based on the column header. how can I achieve that?

[DataAnalysiswithPython]

When I am running:
pip install sqlalchemy-access
I got this error:
ERROR: Could not find a version that satisfies the requirement sqlalchemy-access (from versions: none)
ERROR: No matching distribution found for sqlalchemy-access
I thing the main problem is with this error!

[danielgasparrodriguez]

Thanks Sean, this is great! I ran into the issue that the access database could not be deleted by hand. A lingering .ldb file remained on the folder preventing me from editing the database in access. I was able to solve this problem by adding this line at the end of my code: acc_engine.dispose()

[ClaudeNova]

Great vid thanks so much. How are data type taken care of?

[KeithBaggett]

What is the advantage of this over using the import function built into Access? Is it faster?

[Genesis-uvej]

Hii very informative thanks for the vedio.How to increase the speed of inserting dataframs into Oracle. It is taking very long time to insert dataframs.

[kelvingandhi]

Thank you. It was very helpful video. I wanted to ask you if we have like 1-2 Millions records in pandas data frame, it takes like 2-3 hours to write to access db. Do you know how could I improve performance? Any other way to write data. Or change in approach ?

[naderramsis]

Hello Sean, I receive this error:

pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Any quick way to solve it? I read online I must redownload access database engine from microsoft but first uninstall 360 office then download engine then reinstall 360 which is hectic and out of possibility I cannot do this to a work pc. Is there no easy way to do fix this error?

[sunilkasoju]

Hi, I am very much new to python and tried this code.
I am gettin ( pyodbc.InterfaceError)...
Can you help me with this

[garethwoodall]

Thanks Sean, these videos are very helpful. I seem to have lots of issues setting up the access ODBC connections, and in this attempt in particular I had to do another step after installing xlrd, needed openpyxl. Is there any chance you could go over the connection options and how to config a windows installation in a video as I appreciate you nail all this on your own machines (there is complications when Office is 32/64bit and python is different bitrate for example?

[TheVicks]

Hi, when I try this, i got table already exits, how should I avoid it.

[tankistnikiandy]

Hello, I keep getting this error when connecting to database, do you know why?
Traceback (most recent call last):
File "D:\Documents\Desktop\aaa\main.py", line 6, in <module>
mydb = pyodbc.connect(
pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

[sachinkumaras]

Hi
Sean MacKenzie, Thankyou for the Informative Video. I require a suggestion from you on Pandas. I have 2 excel file once has 16 digit number and another file has the same number but it's 10 digits. how can I map 16 digit number in front of 10 digit number

[josuebribiescagutierrez]

hi, i got this message Can't load plugin: sqlalchemy.dialects:access
why??

[duncreg]

From Excel to Access... out of the frying pan, into the fire. :-( How about taking your data out of Excel... and then saving it into a real database? Heck, the sqlite database embedded in Python would be much better than putting it into Access and its 2GB limit. Much faster too. I mean, you now have your data in Python... you get to see a dataframe in action, an ORM, the option of version control and unit testing, freedom from Windows and proprietary control... why then stick that data into Access and lose all of those things?