Linking a table in Microsoft Access to MySQL table

MySQL is my favorite DBMS but it lacks a nice reporting interface. There are some reporting systems designed to generate customized reports out of MySQL databases but I want to show you a Microsoft Access based solution for using its awesome tools.

First you need to prepare your system by installing necessary drivers:

1-  Depends on the architecture of the MS-Access installed on the system (x86/x64) download and install proper edition of MySql ODBC driver for windows from http://dev.mysql.com/downloads/connector/odbc/

2-  Open ODBC data source administrator and Create a system DNS:

  1. For x32 ms-access: c:\windows\sysWOW64\odbcad32.exe
  2. For x64 ms-access: C:\Windows\System32\odbcad32.exe

3- Goto System DNS and click on Add and select MySQL ODBC x.x ANSI Driver

Image

4- Fill up the fields and use an account on MySQL to connect and finally test the connection

2

5- At the end you should be able to see your record in system DNS

3

The next phase is to actually linking the MySQL database:

1-  Create a blank ms-access db and name it mysql_link.accdb

2-  Goto “External Data” tab and select “ODBC Database” to get the external data linked in ms-access

3- Click on the link to data source

a4

4- In machine data source tab you should be able to see the System DNS you created previously. Select it and click OK

a5

5- Then chose the table you want to link (in our example: “Database_Name”)

a6

6- Chose a record identifier (usually primary key)

a7

Now, you can see a table in list of table which simply linked to MySQL database ( to the table and field you have defined before). It is possible to design forms, queries and reports for this linked table just like a native table.

P.S. Just for security reasons, it is recommended to define a separate user in MySQL and limit the access to tables and fields of desire; and use that user for your link.

Converting Refer/BibIX to Excel format

As I promised, I come up with a clean code for converting the Refer/BibIX ( also Zotoro exported collection ) to a clear Excel format.

#! /usr/bin/env python

import sys
import os

def index_2d(myList, v):
	output = ""
	for i, x in enumerate(myList):
		if v in x:
			output += x[3:]
	return output

txt = ""
xls = ""

directory = sys.argv[1]

if len(sys.argv) < 1:
	sys.exit('Usage: ./zotoro.py directory')

if not os.path.exists(directory): 
	exit('Directory not found!.\nUsage: ./zotoro.py directory')

files = os.listdir(directory)

for theFile in files:
	with open(directory+'/'+theFile) as infile:
		for line in infile:
			txt += line

TagsMap = [[0,'%0','ReferenceType'], [1,'%A','Author'], [2,'%T','Title'], [3,'%8','Date'], [4,'%B','BookTitle'], 
[5,'%D','Year'], [6,'%N','Issue'], [7,'%P','Pages'], [8,'%U','URL'], [9,'%V','Volume'], [10,'%X','Abstract'], 
[11,'%I','Publisher'], [12,'%J','JournalName'], [13,'%K','Keywords'], [14,'%E','Editor'], [15,'%F','Label'], 
[16,'%C','PlacePublished'], [17,'%S','TertiaryTitle'], [18,'%Y','Translator'], [19,'%Z','Notes'], 
[20,'%6','NumberofVolumes'], [21,'%L','CallNumber'], [22,'%M','AccessionNumber'], [23,'%7','Edition'], 
[24,'%9','TypeofWork'], [25,'%?','SubsidiaryAuthor'], [26,'%@','ISBN|ISSN'], [27,'%!','ShortTitle'], 
[28,'%[','OriginalPublication'], [29,'%]','ReprintEdition'], [30,'%*','ReviewedItem'], [31,'%+','AuthorAddress'], 
[32,'%^','Caption'], [33,'%1','Custom1'], [34,'%2','Custom2'], [35,'%3','Custom3'], [36,'%4','Custom4'], 
[37,'%#','Custom5'], [38,'%$','Custom6'], [39,'%&','Section']]

Records = txt.split('\r\n\r\n')

# adding the titles of the columns
for i,tag,title in TagsMap:
	if i<len(TagsMap)-1: xls += TagsMap[i][2] + '\t'
	else: xls += TagsMap[i][2] + '\n'

for record in Records:
	Fields = record.split('\r\n')
	for i,tag,title in TagsMap:
		if i<len(TagsMap)-1: xls += index_2d(Fields,tag) + '\t'
		else: xls += index_2d(Fields,tag) + '\n'

f = open(directory+'/output.xls','w')
f.write(xls)
f.close()

exit("now you can open the output.xlswith excel")

Exporting Zotoro to excel

Zotoro, a reference management software, like most of reference management tools has a compatibility problem of exporting content to a non-reference style (like excel style).

I used some python code to do this, but before getting to python you need to manually export all the collections in Zotoro to “Refer/BibIX” format; which will create a txt file containing references. Create a directory and move all files there.

This script accept the directory path as input:

#! /usr/bin/env python

import sys
import os

directory = sys.argv[1]

if len(sys.argv) < 1:
	sys.exit('Usage: ./zotoro.py directory')

if not os.path.exists(directory): 
	exit('Directory not found!.\nUsage: ./zotoro.py directory')

files = os.listdir(directory)

for theFile in files:
	with open(directory+'/'+theFile) as infile:
		for line in infile:
			txt += line

myMap = [('%0 ', ''), ('%T ', '\t'), ('%V ', '\t'), ('%N ', '\t'), ('%P ', '\t'), ('%U ', '\t'),
('%J ', '\t'), ('%A ', '\t'), ('%D ', '\t'), ('%X ', '\t'), ('%K ', '\t'), ('%B ', '\t'),
('\r\n\t', '\t'), ('\r\n\r\n','***'), ('\r\n',''), ('***','\r\n')]

for k, v in myMap:
	txt = txt.replace(k, v)

f = open(directory+'/output.xls','w')
f.write(txt)
f.close()

exit("now you can open the output.xls with excel")

Well, I know there are some problems with this code such as tags that are not included and may mess up the position of the field in excel column.
Even though this code is just for demonstration of the idea, I am currently working on fully functional code to convert Zotoro (Refer/BibIX) to excel format ASAP.