RSS
 

Export a Microsoft Access Database to JSON (Northwind Example)

15 Jul

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.

C:\utilities>exportaccessdbtojson

ExportAccessDBToJSON v1.0.0.2 –  August 6, 2010
Please visit http://www.divconq.com for more information.

Usage:
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.

Example:
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”:

C:\utilities>exportaccessdbtojson NorthwindTest

If all is well you will see many lines of text stream by ending with something like this:

“Phone” : “(514) 555-2955″,
“Fax” : “(514) 555-2921″,
“HomePage” : “”
}
}
}=-=-=-= END JSON =-=-=-=
22:00:26 Completed OK.

If not, you may see an error like this:

C:\utilities>exportaccessdbtojson BadNorth
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:

C:\utilities>exportaccessdbtojson NorthwindTest -justjson > NorthwindJSON.txtC:\utilities>notepad NorthwindJSON.txt

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.

Next Steps

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.

Troubleshooting

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.

About Jonathan Lampe

Author of 44 articles on this blog.

I have about 12 years of experience designing secure, partially distributed systems (e.g., web farm with some extra load in one remote data center), often in industries such as finance and defense. My solutions and software are currently deployed in mission-critical roles at about 1000 enterprises worldwide. In the last 2-3 years I have turned my technical attention toward more geographically distributed systems and heterogeneous environments (e.g., a mix of operating systems or on-premises and cloud deployments) while maintaining my focus on good UX and great security.

Published on Thursday July 15, 2010 at 10:15pm

 
Comments Off

Posted in Beginner, JSON, Northwind

 

Comments are closed.