4

I am new to Python and have been assigned the task to copy all the MS Access database files(we have five) into CSV format using Python. I have searched through lots of posts on Stack Overflow and sketched together this amateur snippet. I need to see the files I have in my MS Access database. Can someone please provide assistance.

Pyodbc Error - Python to MS Access

open access file in python

import pyodbc

conn_string = ("DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=T:\\DataDump\\7.18.2016 PCR etrakit.accdb") 

conn = pyodbc.connect(conn_string)

cursor = conn.cursor()

cursor.close()
conn.close()

print 'All done for now'
Community
  • 1
  • 1
  • Could it be the spaces in the filename? try rename the file from 7.18.2016 PCR etrakit.accdb to file.accdb and update the script too and try again? – davidejones Nov 15 '16 at 17:36
  • *copy all the MS Access database files into CSV format*... You do realize a relational database is not the same as a flat file text file? You should be more specific in *exporting all tables/queries in MS Access files to csv format*. – Parfait Nov 15 '16 at 20:15

2 Answers2

1

[UPDATED]Try running this 

conn_string = ("DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\T:\\DataDump\\7.18.2016 PCR etrakit.accdb")

use double backslash instead.

Shubham R
  • 7,382
  • 18
  • 53
  • 119
  • @Learner The backslash character means that the next character has special meaning in a string. So deciphering the double backslash... The first backslash tells python to prepare for a special string character, the second backslash is the special string character (which in this case is a backslash) – Shubham R Nov 15 '16 at 17:39
  • Yeah I tried the above suggestion you recommended and even edited the original post to reflect the change but it still throws the same error. –  Nov 15 '16 at 17:41
  • @Learner [check this](https://codedump.io/share/fsosLBxmg4bd/1/pyodbc-error---python-to-ms-access) – Shubham R Nov 15 '16 at 17:41
  • 1
    Yeah that's one of the posts I used to sketch together the script. –  Nov 15 '16 at 17:43
  • conn_string = ("DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\T:\\DataDump\\7.18.2016 PCR etrakit.accdb") – Shubham R Nov 15 '16 at 17:45
  • Thanks Ringne, still the same error. I do not know why is the snippet blowing up. –  Nov 15 '16 at 17:46
  • @Learner : can you rename your filename and remove the spaces in your filename? `7.18.2016_PCR_etrakit.accdb` – Shubham R Nov 15 '16 at 17:48
  • There was a typo it was the T directory not the C directory. Now I need to see the files in the MS database. –  Nov 15 '16 at 17:50
0

Per this post:

Try doing it as a single line

conn_string = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};C:\\T:\\DataDump\\7.18.2016 PCR etrakit.accdb;'

However, I am a bit confused by your file path. On the root of your C:\ drive, you have a directory named T:?

It may also be worth noting that file paths with spaces in the name are not always handled as expected. An alternate approach would be to try and escape the spaces in your file path:

conn_string = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};C:\\T:\\DataDump\\7.18.2016\ PCR\ etrakit.accdb;'
Community
  • 1
  • 1
Aaron St. Clair
  • 391
  • 1
  • 3
  • 12
  • Yeah thanks Aaron, there was a typo there. The C root should not be there. Now if you don't mind how can I see the files I have in that Access database. –  Nov 15 '16 at 17:50
  • So, to clarify, the above solution worked for you? Can you specify what worked? The first or second solution (with escaped spaces)? Or was it simply removing the C:\ portion of the file path? As for accessing the files, that is grounds for a second topic given that you are now connecting successfully. – Aaron St. Clair Nov 15 '16 at 17:59
  • 1
    Yeah needed to remove the C: root folder. –  Nov 15 '16 at 18:02