Last weeks article used a bit of data mining to grab information on ISBN’s from Barnes and Noble and dumped the information into a text file. This week we’ll take those text files and format the data for input into a MySQL database. This tutorial assumes that you are familiar with MySQL.
The data that was written to the text files was in the following format:
Title: DNS and BIND, Third Edition
Author(s): Paul Albitz, Mike Loukides (Editor)
Publisher: O'Reilly Media, Incorporated
Pub. Date: September 1998
Format: Paperback, 480pp
Edition Number: 3
Edition Description: Older Edition
This can easily be parsed and formatted for insertion into a MySQL table.
Firstly a database has to be created and then a table structure has to be decided upon. Since this example already has the titles, I’ll just use simular ones for the field names.
Create a database called book_info:
mysqladmin -u root create book_info;
and now create a table within the book_info database that is to contain all of the data:
CREATE TABLE `books` (
`ISBN` INT( 10 ) NOT NULL ,
`title` VARCHAR( 50 ) NOT NULL ,
`author` VARCHAR( 80 ) NOT NULL ,
`publisher` VARCHAR( 50 ) NOT NULL ,
`pub_date` VARCHAR( 50 ) NOT NULL ,
`format` VARCHAR( 30 ) NOT NULL ,
`edition` INT( 2 ) NOT NULL ,
INDEX ( `ISBN` )
) ENGINE = MYISAM ;
This isn’t the best MySQL table structure ever, but it will do for the purposes of this artice and besides it can always be tuned later.
With a directory full of .txt’s files from the last issue of shell script sundays the following can be ran to create a text file called bookQuery.sql.
for i in $(ls); do
echo "INSERT INTO \`book_info\`.\`books\` (\`ISBN\`, \`title\`, \`author\`, \`publisher\`, \`pub_date\`, \`format\`, \`edition\`) VALUES ('$(cat $i | grep ISBN:)', '$(cat $i | grep Title | sed 'q' | sed "s/'/\\\\'/")', '$(cat $i | grep Author\(s\): | sed "s/'/\\\\'/")', '$(cat $i | grep Publisher: |sed "s/'/\\\\'/")', '$(cat $i | grep Date:| sed "s/'/\\\\'/")', '$(cat $i | grep Format: | sed "s/'/\\\\'/")', '$(cat $i | grep Edition\ Number: | sed "s/'/\\\\'/")');" >> bookQuery.sql; done;
In turn this file can be imported into the table that was created by running the following:
mysql -u root < bookQuery.sql
Whats happening is pretty simple, cat reads the file and grep is used to find the line of text we want to import. After that sed is used. It is used twice in the title field. The first time is to use the first title from the text file. It is also used on every other appropriate field to escape the string so that it does not break the query. This example does not take the titles out of the line, but this could be easily done with cut.
Its easy to import text files into MySQL with shell scripting but the language I feel is best suited for this task is PHP. Some time I’ll go over how to do this with PHP.