0

I have a database with huge tables. Loading and processing them via pyodbc is taking ages, so no option.

Currently I am manually exporting my updated tables from MS Access into a .csv and then loading them into python.

Now I want to automate this process via python. I have seen a similar problem here

My code:

    import os
    import win32com.client

    current_PATH = os.getcwd()
    db_PATH = current_PATH+"\\database.accdb;"

    oApp = win32com.client.Dispatch("Access.Application")
    oApp.OpenCurrentDatabase(db_PATH)

    acExportDelim = 2
    oApp.DoCmd.TransferText(acExportDelim, None, "table_to_export", 'Output.csv', True)

    oApp.DoCmd.CloseDatabase
    oApp.Quit
    oApp = None

But all I get in response is:

    Traceback (most recent call last):
    File "C:\Users\...\export_script.py", line 9, in <module>
    oApp.OpenCurrentDatabase(db_PATH)
    File "<COMObject Access.Application>", line 3, in OpenCurrentDatabase
    File "C:\Anaconda3\lib\site-packages\win32com\client\dynamic.py", line 282, in _ApplyTypes_
    result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args)
    [Finished in 6.9s with exit code 1]

What is the correct way to export a table to a .csv?

Edit: That is how my test table looks like: test_table

Community
  • 1
  • 1
Ali
  • 761
  • 1
  • 5
  • 24
  • 1
    Please post entire traceback as an error message is outputted usually at the end. Also, remove the semicolon in `db_path`. – Parfait Mar 29 '16 at 12:53
  • This is the entire traceback I get. I did remove the semicolon, but i still get the same Traceback. – Ali Mar 29 '16 at 15:26
  • Did you remove the semicolon? This might be due to your IDE or Anaconda install with win32com. Can you run it a command line (PowerShell or Cmd prompt): `python c:\path\to\script.py`. – Parfait Mar 29 '16 at 18:01
  • I did remove the semicolon. When I run it via CMD I get an additional information: It says: `pywintypes.com_error (-2147352567,'Ausnahmefehler aufgetreten.', (0, None, 'Das Feldtrennzeichen für die angegebene Textdatei entspricht dem Dezimaltrennzeichen oder Texttrennzeichen.','jeterr40.chm', 5003411, 2146824847), None)` It is german and tells me that the delimiter is the same as the decimal separator. But I did not set a custom delimiter so it should take the system default one. – Ali Mar 29 '16 at 19:27
  • Can you include first few rows of data for reproducible example? Also, try manually exporting from you tables using ribbon on Access (External Data tab / Text file export - arrow out). Does error re-occur? – Parfait Mar 29 '16 at 19:49
  • Manually exporting is working as intended. I just created a random table, called: 'test_table' with an 'ID' and 'street' attribute. I created one row. Manually exported, but when I run the script I get the same error as above. – Ali Mar 29 '16 at 21:21
  • Can't help without reproducible example. You might have an encoding issue in data. – Parfait Mar 30 '16 at 17:00
  • How can I include my sql table in Stack Overflow? – Ali Mar 30 '16 at 17:01
  • Add the first few rows in same code formatting like you did the python code. – Parfait Mar 30 '16 at 17:02
  • I edited my original post including the table. – Ali Mar 30 '16 at 17:33
  • It might be the German column name with special chars like accents. In [TransferText](https://msdn.microsoft.com/en-us/library/office/ff835958.aspx) the last argument codepage allows encoding [types](https://msdn.microsoft.com/en-us/library/office/aa432511%28v=office.12%29.aspx) (e.g., UTF-8=65001). Try different ones. – Parfait Mar 30 '16 at 17:57
  • Once you've already moved from pyodbc, consider using [ADO](https://msdn.microsoft.com/en-us/library/ms675532(v=vs.85).aspx) with `win32com` to read data into a CSV. You can create an ADO recordset with SQL, and write the entire string using the [GetString method](https://msdn.microsoft.com/en-us/library/ms676975(v=vs.85).aspx) on the [Recordset](https://msdn.microsoft.com/en-us/library/ms681510(v=vs.85).aspx) object. – Zev Spitz Oct 18 '16 at 21:20

0 Answers0