Ok, time to get back to an old topic that I need to finish. My kids school needs a library system. We have built one using Excel and want to upgrade it to Application Express and Express Edition of the database.
The first problem that we need to look at is how to get data on books without having to pay for a database with all books that has a monthly update fee. Fortunately, I found a web site, http://www.librarything.com, that allows you to create on on-line library catalog system with a small donation. To date we have entered over 6000 books into the system. The data required to enter the book is an ISBN number which is typically printed on the book. For example, if we have the book “A Fly Went By” by Mike McClintock, we can get the ISBN number of 9780394800035 printed on the back cover. If we type this number into librarything, we get a field to enter Tags, a review, a rating, and a summary. The librarything fills in the Title, Author, Other Authors, Date, LD Call Number, Dewew Number, Plublisher, and languages. When we enter the data the date Acquired is auto-entered. This information has proved to be very valuable. From it we can create a card catalog and list of books in our library. We chose to use the Comments field to list the price of the book (needed if a student looses the book) and the Tags field (to list which age group can read this book).
The data entry of the book is simple. There is a tab on library thing to add books. It has a search area, tag to add, and place to search (Amazon.com or Library of Congress). If you enter the ISBN number in the search field the web site goes to the search location to populate the data for the book. This helps because it allows us to add old and new books without having to subscribe to a book depository that provides all of this data.
Once we have the data entry finished, we need to export this data so that we can use it in a Excel spreadsheet or database. To export this data we go to the tools tab and export the data as a tab-delimited text. We could export it as a CSV but this does not contain all of the data. The format of the data exported to a tab delimited spreadsheet is
- book id
- sequence number in librarything. This number is arbitrary but if there are multiple books with the same title and author the book id is different thus can be use to identify a book unqiuely.
- title
- title of book
- author (last, first)
- author (first, last)
- publication
- publishing company that released the book. This contains things like the address of the publisher as well as edition of the book if it has been released multiple times.
- date
- date of publication. If this is a new edition it has a different publication date than the previous edition.
- ISBN
- series
- comment area to catagorize it as part of a collection. We do not use this field.
- source
- this identified where the information came from. Typically it is Amazon.com or the Library of Congress. It does not need to be filled in.
- language 1
- the primary language of the book
- language 2
- original language
- LCC
- Library of congress catalog index, we do not use this
- DCC
- Dewey Decimal system, we use this for non-fiction books
- BCID
- this field is not used
- date entered
- this is the date that the book was entered into the librarything system. We typically use this as an index for printing labels for the book.
- date 2
- this is used if the book information is modified. We typically don’t use this field
- date 3
- we don’t use this field
- date 4
- we don’t use this field
- stars
- we don’t use this field
- your tags
- this field is used to catagorize the type of books. Typical entried are non-fiction, fiction, everybody, smiley. These tags can be followed by any other tag that describes the information contained in the book. This information is very useful when trying to search for a book. For example, we might have a book about the history of England during World War II. In the tag you would find “non-fiction, history, Winston Churchill, World War II, England”.
- review
- this is intended to be a comment section to help catagorize the book. For example, if it contains violence or sexual situations we put the comments here. This allows us to figure out if a book should be high school only or good for middle school readers.
- summary
- not used
- comments
- this includes the price of the book. There was not really any
other place to put this data and this field is not shared with other
people in the librarything so it can be treated as a private field. - encoding
- not used
When we download this page, we can load it into an Excel spreadsheet or import it into a database. Initially we loaded this data into an Excel workbook titled Books. We also load another workbook that contains a list of students. The student workbook contains the following data
- ID
- student id, this is unique for all students, staff, and volunteers
- Class
- this is used to communicate to the teachers which kids have overdue books. The valid entries change each year and is provided by the school.
- LastName
- FirstName
Once we have the books and students we can create another worksheet that correlates books checked out and who has them checked out. The library workbook contains the following elements
- date_out
- this is a manual entry of when a book is checked out
- book_id
- this is the book id. It correlates to the bookid from the librarything. We print this number in barcode format so that the book can be scanned with a barcode reader
- student_id
- this is the ID from the student workbook.
- date_in
- typically this field is blank until the book is returned.
- Book
- we use this field to verify that the book id correlates to the book that the student is checking out. This field is populated when the book_id is entered. The value is filled in with the equation
- if (b2=”” ,””, LOOKUP(B2,Books!$A$2:$A$10000,Books!$B$2:$B$10000))
- It is important to note that this limits the number of books to 10,000 books in the library. If we exceed this number, this field needs to be changed to a larger number. This also assumes that the book id is located in column A and the book title is located in column B of the Books worksheet
- Student
- we use this field to verify that the student id correlates to the student that is checkign out the book. This field is polulated when the student_id is entered. The value is filled in with the equation
- =IF(C2=””,””,LOOKUP(C2,Students!$A$1:$A$510,Students!$D$1:$D$510)&” “&LOOKUP(C2,Students!$A$1:$A$510,Students!$C$1:$C$510))
- It is important to note that this limits the number of students to 510. This typically is not a problem but will need to change as the population of the student body changes.
- Overdue?
- we use this field to find books that have been checked out for longer than 14 days. If the book is overdue this field will have the contents “Over Due”. If the book is still out but not overdue it will contain “Checked Out”. If the book has been returned, this field will be empty.
- =IF(A2=””,””,IF(D2<0.0001,IF(G2>13,”Over Due”,”Checked Out”),””))
- It is important to note that there is a hidden column that is typically no seen. This field contains the number of days that a book has been checked out. =IF(A2=””,””,IF(D2<0.0001,TODAY()-A2,D2-A2))
Some notes on using Excel as a library system
- If you resort the student workbook, you break the checking/checkout system
- If you type something into the Book or Student field, it will override the calculated values and potentially lead to corruption
- The student field is not protedcted so a volunteer can change a student name or class.
- If someone corrupts the data and saves the spreadsheet, it becomes very difficult to recover from the corruption
- if the book list has not been updated but the label has been put on a book, the last book in the spreadsheet will be listed as the book checked out. It will appear that a bunch of books have the same name but multiple ids. This isn’t true but can be fixed as long as no one tries to fix it manually. The solution is to import the new books into the Books workbook at the end and have the Library workbook reference these new values.
- If someone changes the date_out values it can corrupt the overdue calculation. The same is true for the date_in.
In summary, using an Excel spreadsheet works. You can create a library system using it but you can not give the students access to any of the data. You can not use an Excel system for a card catalog because it is difficult to lock and unlock values yet let students look up book or author names. The system is also vulnerable to corruption by volunteers that do not know how to use a computer or are uncomfortable with a computer. What we need is to create a web based system that does not allow for editing of values other than the student_id and book_id for checkout and date for checkin.
Total cost of this system is the cost of the librarything – $25, and a Windows desktop with Excel. This is a difficult price to estimate because the school provided this so there was effectively zero cost to the library. In reality this is the most expensive component since a laptop is about $500 and Excel is about $200. Fortunately, this was not a cost that we had to pay for.
Next topic, how to print labels for books and how to import the data that we have in Excel spreadsheets into a database.