Linux Blog

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:

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:

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.