Kzebrows Week 6

Question to Answer

Provide the DDL (create table) statements that you used for your application and product tables.

Application:

  create table Application (ApplNo int primary key, ApplType varchar, SponsorApplicant varchar, 
  MostRecentLabelAvailableFlag boolean, CurrentPatentFlag boolean, ActionType varchar, 
  Chemical_Type int, Ther_Potential int, Orphan_Code varchar)

Product:

  create table Product (ApplNo int, ProductNo int, Form varchar, Dosage varchar, ProductMktStatus 
  varchar, TECode varchar, ReferenceDrug int, drugname varchar, activeingred varchar)

Provide the sed command sequences that you used to convert the raw text files into sequences of SQL insert statements.

Command to convert Application (written by Anu with edits from Kristin)

  cat application.txt | sed "s/\t/~/g" | sed -r "s/( ){2}//g" | sed "s/ \~/~/g" | sed "s/\~ /~/g" | 
  sed "s/\~\~/~null~/g" | sed "s/\~\~/~null~/g" | sed "s/\~\r$/~null/g" | sed "s/~/,'/1" | sed "s/~/','/1" | 
  sed "s/~/',/1" | sed "s/~/,/1" | sed "s/~/,'/1" | sed "s/~/',/1" | sed "s/~/,'/1" | sed "s/~/','/1" |sed "s/~/,'/1" | 
  sed "s/\r$/'/g" | sed "s/'null'/null/g" | sed "s/'null/null/g" | sed "s/^/insert into application(ApplNo,ApplType,
  SponsorApplicant, MostRecentLabelAvailableFlag,CurrentPatentFlag,ActionType,Chemical_Type,Ther_Potential,
  Orphan_Code) values(/g" | sed "s/$/);/g" | sed 1D > ~/public_html/application.sql.txt

Command to convert Product (written by Kristin with edits from Anu)

  cat Product.txt | sed "s/\*\*Federal Register determination that product was not discontinued 
  or withdrawn for safety or efficacy reasons\*\*//g" | sed "s/\*\*Federal Register determination
  that product was not discontinued or withdrawn for safety or efficacy\*\*//g" | sed "s/\*\*Federal
  Register notice determination that product was not discontinued or withdrawn for safety or 
  efficacy reasons\*\*//g" | sed "s/\*\*Federal Registration determination that product was not discontinued
  or withdrawn for safety or efficacy reasons\*\*//g" | sed "s/See current Annual Edition, 1.8 
  Description of Special Situations, Levothyroxine Sodium//g" | sed "s/'//g" | sed "s/\t/*/g" |
  sed -r "s/( ){4}//g" | sed -r "s/( ){2}//g" | sed "s/ \*/*/g" | sed "s/\* /*/g" | sed "s/\*\*/*null*/g" | 
  sed "s/\*\*/*null*/g" | sed "s/\*/,/1" | sed "s/\*/,'/1" | sed "s/\*/','/1" | sed "s/\*/',/1" | sed "s/\*/,'/1"
  | sed "s/\*/',/1" | sed "s/\*/,'/1" | sed "s/\*/','/1" | sed "s/\r$/'/g" | sed "s/^/insert into    
  product(ApplNo,ProductNo,Form,Dosage,ProductMktStatus,TECode,ReferenceDrug,drugname,activeingred) 
  values(/g" | sed "s/$/);/g" | sed 1D > ~/public_html/Product.sql.txt