Jkuroda Week 6

Creating Tables

To begin the assignment, I first downloaded pgAdmin3 to my computer and created my own database with which I could work. I used the curl command in the terminal to download the FDA files to my directory in my.cs.lmu.edu then I set up the tables for both the application and product lists using the following commands:

create table application (id int primary key, applicationType varchar, sponsorApplicant varchar, 
mostRecentLabelAvailableFlag boolean, currentPatentFlag boolean, actionType varchar, chemicalType int, 
therPotential varchar, orphanCode varchar)

create table product (int references application(id), productNumber int, form varchar, dosage varchar, 
productMktStatus int, TECode varchar, referenceDrug int, drugName varchar, activeIngredient varchar)

SQL Insert Statements

Now I had to get the data from the FDA into these formatted tables, so I drew upon my experience from using the command line in previous weeks and came up with the following:

cat application.txt | sed "1D" | sed "s/\t/','/g" | sed "s/\r$/');/g" | sed "s/ *'/'/g" | 
sed "s//null/g" | sed "s/'//1" | sed "s/^/insert into
application(id,applicationType,sponsorApplicant,mostRecentLabelAvailableFlag,
currentPatentFlag,actionType,chemicalType,therPotential,orphanCode)/g" > ~/public_html/application.sql.txt