Just recently the library at the school where I work asked me to write a web application so that they could more easily manage their electronic resources. The resources they were tracking were not linked by ids, nor did their current solution provide for an intuitive means to track the data. In other words, the only person who could make sense of the data tables in all its glory was the user who had created it. So began the process of moving these flat files into a relational database model so that I could design a web application around it.
Exporting the data from MS Access
The first order of business was getting the data into a Comma Separated Value (CSV) format. I could recreate the wheel here, but instead, I’ll point you to a step by step process (look for “Converting from MDB to CSV in Access 2010”; screenshots are included). Once I had my text file ready, I just needed to design the schema and the corresponding lookup tables so that I could pull all necessary data into a web form with one robust JOIN.
Importing the data into MySQL
Since I knew the file would be a CSV file from a Microsoft product, I knew all data would be enclosed in double quotes. In this case MySQL’s Load Data Infile command was the simplest way to import the text files into their respective tables:
load data local infile "/home/mike/import.txt" into table import_table fields terminated by "," -- we know it's a CSV file enclosed by '"' -- MS tends to double quote all exports lines terminated by "\n"; -- it's a text file, with each packet of data on a new line
If importing to MySQL on a Windows machine you will have to double escape the backslashes to the text file (e.g. c:\\path\\to\\loadfile.txt, and most likely change the line terminator to “\r\n” so that the file will get parsed properly). MySQL Documentation.
The Web App
The library’s new and upcoming web site is built on WordPress, and we wanted to limit some resources based on user roles (this is a requirement stipulated by the resource publishers). Since I had already built a custom plugin to link the site into our Single Sign On (CAS/LDAP) architecture (blog entry with the plugin to come), it just made sense to build the forms to manage the data as a custom plugin as well. There is a separate article on that app (forthcoming).