Access to csv exporter with UCanAccess.

access2csv.py

"""
This script exports all tables in a MSAccess file to a bunch of CSV's.
It depends on UCanAccess binaries, which can be downloaded from here: 
http://ucanaccess.sourceforge.net/site.html
It supports MSAccess 2016 and previous versions.
Make sure to set pathUCA to the path of the UCanAccess binaries. 
"""

from subprocess import Popen, PIPE, STDOUT
import io
import sys
from fcntl import fcntl, F_GETFL, F_SETFL
from os import O_NONBLOCK, read
from time import sleep

pathUCA = "/opt/UCanAccess-4.0.4-bin"

# Run UCanAccess console mode.
# Unfortunately, I couldn't get JPype and JayDeBeApi to work with UCanAccess jars in python3 
# Using JDBC will be much better than this, but for now, this does the trick.
p = Popen(['java', 
           '-classpath', 
           pathUCA+'/lib/hsqldb.jar:'+
           pathUCA+'/lib/jackcess-2.1.11.jar:'+
           pathUCA+'/lib/commons-lang-2.6.jar:'+
           pathUCA+'/lib/commons-logging-1.1.3.jar:'+
           pathUCA+'/ucanaccess-4.0.4.jar',
           'net.ucanaccess.console.Main'], bufsize=0, stdout=PIPE, stdin=PIPE, stderr=STDOUT)

# Set non-blocking mode, to interact without hanging
flags = fcntl(p.stdout, F_GETFL) # get current p.stdout flags
fcntl(p.stdout, F_SETFL, flags | O_NONBLOCK)

pathInput = sys.argv[1]
sleep(2)

output = p.stdout.read()
print(output.decode())
p.stdin.write((pathInput+"\n").encode())
output = ""
for i in range(1,10):
    sleep(2)
    temp_output = p.stdout.read() 
    if temp_output is not None:
        output += temp_output.decode()
    if output.endswith("UCanAccess>"):
        break;

print(output)
if i == 9: 
    print("Loading file took too long.")

lines = output.split("\n")
flag = False
tables = []
for line in lines: 
    if flag:
        tables = [x.strip() for x in line.split(",")]
        break
    if line.startswith("Loaded Tables"):
        flag = True

for table in tables:
    p.stdin.write(('export -d , -t "%(t)s" "%(t)s.csv";\n' % {"t":table}).encode())
    sleep(1)
    print("Exported table ", table)
    
    
"""
You can use this to convert all MSAccess files in a directory with the following bash script:

files=`find ./ -name *.accdb -o -name *.mdb`
while read -r filename; do
    echo "CONVERTING FILE $filename"
    _basedir=`pwd`
    _basename="${filename%.*}"
    mkdir -p "$_basename"
    cd "$_basename"
    python3 /opt/access2csv.py  "$_basedir/$filename" 
    cd "$_basedir"
done <<< "$files"
"""