pandas 将 DataFrame 导出和导入到 Access 文件 (.mdb)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43098003/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Export and import DataFrames to Access file (.mdb)
提问by Basem
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.
我有 7 个表，我想从 Access 文件 (.mdb) 中读取它们，然后我需要使用 Pandas DataFrame 更改这些值，然后将它们再次保存在一个新的 Access 文件中。你对如何做到这一点有什么建议吗？我在 python 中相对较新，非常感谢任何支持。
回答by William Knighting
This may be some help: https://pypi.python.org/pypi/pandas_access
这可能会有所帮助：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.
在您能够将表加载到 Pandas 数据框中后，一切都应该是直接的。然后执行您需要的数据操作并将其发送回 Access。
回答by ASH
I think you should check this.
我想你应该检查一下。
https://pypi.python.org/pypi/pyodbc/
https://pypi.python.org/pypi/pyodbc/
Also, to read data from Access Table, try something like this.
另外，要从 Access Table 读取数据，请尝试这样的操作。
# -*- 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.
或者 。. . 如果您已经在使用 Access，只需使用 VBA。
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 . . .
或者 。. . 运行一些 VBA 脚本。. .
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).
导出所有数据后，进行转换并加载（返回到 Access 或其他目标）。
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.
我敢打赌你甚至不需要导出步骤。您可能可以在 Access 中完成您需要做的所有事情，包括它本身。
