CODEWITHSUNDEEP
pythonpandasms-access
Basem
Basem

Reputation: 31

Export and import DataFrames to Access file (.mdb)

I have 7 tables which I want to read from an Access file (.mdb), then I need to change the values using pandas DataFrame, and then save them again in a new Access file. Do you have any suggestion on how to do that? I am relatively new in python, and any support is highly appreciated.

Upvotes: 3

Views: 12497

Answers (2)

ASH
ASH

Reputation: 20302

I think you should check this.

https://pypi.python.org/pypi/pyodbc/

Also, to read data from Access Table, try something like this.

# -*- coding: utf-8 -*-
import pypyodbc
pypyodbc.lowercase = False
conn = pypyodbc.connect(
    r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
    r"Dbq=C:\Users\Public\Database1.accdb;")
cur = conn.cursor()
cur.execute("SELECT CreatureID, Name_EN, Name_JP FROM Creatures");
while True:
    row = cur.fetchone()
    if row is None:
        break
    print(u"Creature with ID {0} is {1} ({2})".format(
        row.get("CreatureID"), row.get("Name_EN"), row.get("Name_JP")))
cur.close()
conn.close()

Or . . . just use VBA, if you are already using Access.

Dim outputFileName As String
outputFileName = CurrentProject.Path & "\Export_" & Format(Date, "yyyyMMdd") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", outputFileName , True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table2", outputFileName , True

This could be an options too . . . 

strPath = "V:\Reports\Worklist_Summary.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryEscByDate", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryCreatedByDate", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryClosedByDate", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryCreatedByUsers", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qrySummaries", strPath

Or . . . run some VBA scripts . . .

Option Compare Database
Option Explicit
Private Sub Command2_Click()
  Dim strFile As String
  Dim varItem As Variant

    strFile = InputBox("Designate the path and file name to export to...", "Export")

    If (strFile = vbNullString) Then Exit Sub

    For Each varItem In Me.List0.ItemsSelected
        DoCmd.TransferSpreadsheet transferType:=acExport, _
                                  spreadsheetType:=acSpreadsheetTypeExcel9, _
                                  tableName:=Me.List0.ItemData(varItem), _
                                  fileName:=strFile
    Next

    MsgBox "Process complete.", vbOKOnly, "Export"
End Sub
Private Sub Form_Open(Cancel As Integer)
  Dim strTables As String
  Dim tdf As TableDef


    For Each tdf In CurrentDb.TableDefs
        If (Left(tdf.Name, 4) <> "MSys") Then
            strTables = strTables & tdf.Name & ","
        End If
    Next
    strTables = Left(strTables, Len(strTables) - 1)

    Me.List0.RowSource = strTables
End Sub

When all data is exported, do your transformations, and load (back to Access or another destination).

I'll bet you don't even need the export step. You can probably do everything you need to do in Access, all y itself.

Upvotes: 1

William Knighting
William Knighting

Reputation: 113

This may be some help: https://pypi.python.org/pypi/pandas_access

Everything should be straight forward after you're able to load the tables into pandas data frame. Then do the data manipulations you need to and send back to Access.

Upvotes: 1

Related Questions