Research Notes

Converting MS Access mdb files to sqlite | mdb2sqlite

I was doing some research into reading mdb-files on Linux. After evaluating the unixODBC  MS Access driver from Easysoft, which didn’t really work correctly with XpressMP, and was also very expensive, I settled with sqlite. I found out that I could fairly easily convert an mdb to sqlite with mdb-tools, and with a small shell script, it is a breeze. The sed magic is not extensive, though, so you may have to add some magic dust yourself.

The hints I needed I mostly found here and here.

mdb2sqlite <mdb-filename&ht; <sqlite-filename>

# Inspired by:
# http://nialldonegan.me/2007/03/10/converting-microsoft-access-mdb-into-csv-or-mysql-in-linux/
# http://cltb.ojuba.org/en/articles/mdb2sqlite.html

# Use temporary files for sql statements to ease debugging if something goes wrong

# Export schema from mdb:

mdb-schema $1 postgres \
| sed "s/Int8/INTEGER(8)/" \
| sed "s/Int4/INTEGER(4)/" \
| sed "s/Float8/FLOAT(8)/" \
| sed "s/Float4/FLOAT(4)/" \
| sed "s/Bool/BOOLEAN/" \
| sed "s/Char /VARCHAR/" \
| grep -v "^--" \
> create.sql

# Import schema to sqlite3
sqlite3 $2<create.sql

# Delete old import data (adding to exising file later)
# Vast speed improvement with BEGIN..COMMIT
echo "BEGIN;">import-data.sql

# Export each table and replace nan and inf with NULL
for table in `mdb-tables $1`
mdb-export -I $1 $table |sed -e 's/)$/)\;/'\
| sed "s/-inf/NULL/mg" \
| sed "s/inf/NULL/mg" \
| sed "s/-nan/NULL/mg" \
| sed "s/nan/NULL/mg" \

echo "COMMIT;">>import-data.sql

# Import data to sqlite3
sqlite3 $2<import-data.sql

Converting Xpress lp format to Cplex lp format

I was doing a little digging to see if I could find a tool for converting between the non-compatible lp formats of Cplex and XpressMP. It turns out that the open source lpsolve does the trick with the external language interfaces enabled for CPLEX and XPRESS.

I had to download and compile it myself, and symlink the .so files to the /usr/lib/ directory, and it seems to work with the command line:

lpsolve -rxli xli_XPRESS myXpressFile.lp -wxli xli_CPLEX myCplexFile.lp

Solving LPs in Sage (sagemath)

I was playing around with LPs in sage, and implemented one of the Mosel examples to see how it worked out:

#Blending (from Mosel user guide §2.2)

ROres = range(2)
REV = 125
COST = [85,93]
AVAIL = [60,45]
GRADE = [2.1,6.3]

#Setting up problem
p = MixedIntegerLinearProgram(maximization=True)
x = p.new_variable()

#Set objective
p.set_objective(sum([(REV-COST[o])*x[o] for o in ROres]))

#Lower bounds on ore quality
p.add_constraint(sum([(GRADE[o]-MINGRADE)*x[o] for o in ROres]),min=0)
#Upper bounds on ore quality
p.add_constraint(sum([(MAXGRADE-GRADE[o])*x[o]for o in ROres]),min=0)

#Upper bounds on variables
for o in ROres:
    p.add_constraint(x[o] <= AVAIL[o])


#Integer version: uncomment this
x_var = p.get_values(x)
for sol in x_var:
    print x_var[sol]