This article shows how to use a command-line utility to export an existing Microsoft Access database to a text file containing a single JSON structure.
If you do not know what JSON is, please see my earlier article on that subject.
The Microsoft Access database we are going to export today is the (in)famous “Northwinds” example database which millions of Microsoft programmers have been exposed to.
Download and Install the “Northwind” Access Database
First, download a copy of the Northwind Access database from here.
Save the ZIP file and then unpack the “Northwind.mdb” file into a local directory on your Windows file system. (Optional: open the database with Microsoft Access – note that the utility described below doesn’t need Microsoft Access to work.)
Now open your Windows Control Panel, get into “Administrative Tools” and then open the “Data Sources (ODBC)” panel. Go to the “System DSN” tab and add a new “Microsoft Access Driver (*.mdb)” entry. Select your local copy of “Northwind.mdb” when prompted and name your DSN (“Data Source Name”) something like “NorthwindTest”.
Download and Install the “Access to JSON” Export Utility
We wrote a command-line utility to help you export your Access database as a single piece of JSON. Download a copy of this here.
Save the ZIP file and then unpack the enclosed files into a local directory on your Windows file system. To make sure the utility unpacked OK, please open a command prompt, CD into the directory where the executables are and then run “exportaccessdbtojson” from the command line. The expected output is shown below.
ExportAccessDBToJSON v220.127.116.11 – August 6, 2010
Please visit http://www.divconq.com for more information.
ExportAccessDBToJSON NameOfDSN [-JustJSON] [ID4Tables] [> OutputFilename]
…where ID4Tables is a comma-delimited list of tables to which you
want the utility to add an incremented AutoID field.
To export an existing Access database defined to your system
with an ODBC DSN of “Northwinds” into a file that will contain
a mix of log messages and JSON output, use the following command:
ExportAccessDBToJSON Northwinds > JSONandLog.txt
To export only the JSON output in the output file, use this instead:
ExportAccessDBToJSON Northwinds -Quiet > JustJSON.txt
Export The Access Database (With Log Output)
Start by trying to execute the following command (with the name of your actual DSN in place of “NorthwindTest”:
If all is well you will see many lines of text stream by ending with something like this:
“Fax” : “(514) 555-2921″,
“HomePage” : “”
}=-=-=-= END JSON =-=-=-=
22:00:26 Completed OK.
If not, you may see an error like this:
22:01:02 ERROR: Could not connect to DSN=BadNorth! System.Data.Odbc.OdbcExce
ption: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found
and no default driver specified
If things are going poorly, please see the “Troubleshooting” section below. However, if things went well, please proceed to the next section.
Export The Access Database As A Single JSON File
Now, let’s turn off the extraneous log messages and the superflous “START JSON” and “END JSON” tags. Let’s also capture our output into a file so we can look at using our favorite text editor.
Use these commands to perform the export and view the output, again substituting in the correct name of your DSN and any output filename you wish:
If you would like to just see the expected JSON output from the Northwind database, please download the Zip file that contains that data as “NorthwindJSON.txt” here.
The next article talks about how to fit a simple relational database structure into the form needed by Cassandra today. The article after that expands on the concept to rearrange the Northwind JSON structures you just exported into a Cassandra-friendly format. Then, in a future articl,e I will show how to import the converted JSON into Cassandra. Stay tuned!
How the Utility Works
This utility connects to the named Access database, lists all the tables, assumes the first column as the key in each database, and then exports the complete contents of each database as a series of string-based name value pairs, collected on a row-by-row basis by the value of the key column. The “ID4Tables” argument is used to add unique IDs to tables that lack them: e.g., the “Order Details” table in Northwinds. JSON structures created will include escaped special characters (e.g., “\\” to represent a backslash) but are not passed through a full JSON validation step before they are written so they are not 100% guaranteed to work in your JSON parser. (These structures do, of course, work with the Northwind Access database.)
This utility can be used on databases other than the Northwind sample database but has not been extensively tested against many others – your mileage may vary.
Most problems with this process are caused by typos in your command-line invocation or in your DSN name in the ODBC setting. Also make sure you set up a System, not a User DSN entry.
Also, please avoid DSNs with spaces or other characters. The utility wasn’t tested against these.