Difference between revisions of "Emilysimso Week 6"

Revision as of 04:01, 14 October 2015

Questions to Answer

DDL (create table) statements

• create table Applicationfile(ApplNo int, ApplType varchar, SponsorApplicant varchar, MostRecentLabelAvailableFlag boolean, CurrentPatentFlag boolean, ActionType varchar, Chemical_Type int, Ther_Potential varchar, Orphan_Code varchar)
• create table Productfile (ApplNo int, ProductNo int, Form varchar, Dosage varchar, ProductMktStatus varchar, TECode varchar, ReferenceDrug int, drugname varchar, activeingred varchar)

sed command sequences

Application

• cat application.txt | sed "s/^/insert into application3 (ApplNo, ApplType, SponsorApplicant, MostRecentLabel, AvailableFlag, CurrentPatent, Flag, ActionType) values (/g" | sed "s/\r$/);/g" | sed "s/values (......\t/&, /g" | sed "s/ N\t/'N', /g" | sed "s/\t, A/, 'A', /g" | sed "s/False\t/ 'False',/g" | sed "s/,AP\t/ 'AP', /g" | sed "s/S\t/ 'S'/g" | sed "s/P\t/ 'P'/g" | sed "s/'AP', 1\t/'AP', 1,/g" | sed "s/'AP', 3\t/'AP', 3,/g" | sed "s/'AP', 5\t/'AP', 5,/g" | sed "s/'AP', 7\t/'AP', 7,/g" | sed "s/, N\t/, 'N', /g" | sed "s/, N /, 'N', /g" | sed "s/\t, 'N',/, 'N',/g" | sed "s/\t,'N'/, 'N' /g" | sed "s/'N' ,/'N', /g" | sed "s/\t 'False',/, 'False',/g" | sed "s/'N',\t/'N', /g" | sed "s/\t, 'False'/, 'False'/g" | sed "s/'A', \t/'A', '/g" | sed "s/'N', /'N' '/g" | sed "s/'N'/'N',/g" | sed "s/\t//g" | sed "s/ //g" | sed "s/'A', /'A', '/g" | sed "s/, 'False'/', 'False/g" | sed "s/'False 'AP'/'False', 'AP'/g" | sed "s/'False,TA/'False', 'TA'/g"
  • This didn't work...
• Made a new command sequence
• cat application.txt | sed "s/^/insert into application (ApplNo, ApplType, SponsorApplicant, MostRecentLabel, AvailableFlag, CurrentPatentFlag, ActionType, Chemical_Type, Ther_Potential, Orphan_Code) values (/g" | sed "s/\r$/);/g" | sed "s/\t/', '/g" | sed "s/ //g" | sed "s/,/null,/g" | sed "s/, ');/);/g" | sed "s/, 'N '/, 'N'/g" | sed "s/', 'N'/, 'N'/g" | sed "s/, 'A '/, 'A'/g" | sed "s/', 'A'/, 'A'/g" | sed "s/'AP', '/'AP', /g" | sed "s/'TA', '/'TA', /g" | sed "s/', 'P'/, 'P'/g" | sed "s/', 'S'/, 'S'/g" | sed "s/, 'V)/, 'V')/g" | sed "s/1',/1,/g" | sed "s/3', /3, /g" | sed "s/8', /8, /g" | sed "s/5', /5, /g" | sed "s/2', /2, /g" | sed "s/6', /6, /g" | sed "s/4', /4, /g" | sed "s/', 'B'/, 'B'/g" | sed "s/0', /0, /g"
  • That also didn't work...
• Made a new sequence
  • I looked at Anu's for the first part because she explained some of it to me in class but then I didn't understand so I needed the visual....
  • cat application.txt | sed "s/\t/~/g" | sed -r "s/ ( ) {2}//g" | sed "s/\~/~/g" | sed "s/\~ /~/g" | sed "s/~~/~null~/g" | sed "s/~\r$/~null/g" | sed "s/~/,'/g" | sed "s/'N,/'N',/g" | sed "s/'A,/'A',/g" | sed "s/'False/'False'/g" | sed "s/'null/'null'/g" | sed "s/'TA,/'TA',/g" | sed "s/'AP,/'AP',/g" | sed "s/,'S,/','S',/g" | sed "s/,'P,/','P',/g" | sed "s/,'/','/3" | sed "s/^/insert into applicationfile (ApplNo,ApplType,SponsorApplicant,MostRecentLabelAvailableFlag,CurrentPatentFlag,ActionType,Chemical_Type,Ther_Potential,Orphan_Code) values (/g" | sed "s/$/);/g" > ~/public_html/application.sql.txt
  • Didn't work...
• Made another sequence
• 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/~/,'/g" | sed "s/'N,/'N',/g" | sed "s/'A,/'A',/g" | sed "s/'False,/'False',/g" | sed "s/'AP,/'AP',/g" | sed "s/'TA,/'TA',/g" | sed "s/'null/null/g" | sed "s/,'/','/3" | sed "s/,'/','/7" | sed "s/'S,/'S',/g" | sed "s/'P,/'P',/g" | sed "s/^/insert into applicationfile(ApplNo,ApplType,SponsorApplicant,MostRecentLabelAvailableFlag,CurrentPatentFlag,ActionType,Chemical_Type,Ther_Potential,Orphan_Code) values (/g" | sed "s/$/);/g" > ~/public_html/application.sql.txt
  • This also didn't work
• But then....
• 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/~/,'/g" | sed "s/'N,/'N',/g" | sed "s/'A,/'A',/g" | sed "s/'False,/'False',/g" | sed "s/'AP,/'AP',/g" | sed "s/'TA,/'TA',/g" | sed "s/'null/null/g" | sed "s/,'/','/3" | sed "s/,'/','/7" | sed "s/'S,/'S',/g" | sed "s/'P,/'P',/g" | sed "s/P','V/P','V'/g" | sed "s/S','V/S','V'/g" | sed "s/P\*,'V/P\*','V'/g" | sed "s/P\*,/P\*',/g" | sed "s/S\*,/S\*',/g" | sed "s/S\*','V/S\*','V'/g" | sed "s/,/',/7" | sed "s/null'/null/g" | sed "s//'/g" | sed "s/null,'V/null,'V'/g" | sed "s/'B,'/'B','/g" | sed "s/^/insert into applicationfile(ApplNo,ApplType,SponsorApplicant,MostRecentLabelAvailableFlag,CurrentPatentFlag,ActionType,Chemical_Type,Ther_Potential,Orphan_Code) values (/g" | sed "s/$/);/g" > ~/public_html/application.sql.txt
  • THIS ONE WORKED!!!

Product

• cat Product.txt | sed "s/^/insert into Product2 (ApplNo, ProductNo, Form, Dosage, ProductMktStatus, TECode, ReferenceDrug, drugname, activeingred) values (/g" | sed "s/\t/', /g" | sed "s/TABLET,/TABLET/g" | sed "s/', /', '/g" | sed "s/,/null,/g" | sed "s/', '00/, 00/g" | sed "s/00.',/&',/g" | sed "s/',',/,/g" | sed "s/'.',/'&,',/g" | sed "s///g" | sed "s/',,',/,/g" | sed "s/00., '/&'/g" | sed "s///g" | sed "s/ SODIUM; /SODIUM/g" | sed "s/;//g" | sed "s/\r$/');/g" | sed "s/ 0.., /&'/g" | sed "s/', '0/, 0/g" | sed "s/, 0..',/&'/g" | sed "s/','/,/g" | sed "s/5'/5/g" |sed "s/, 0...MG/'&/g" | sed "s/0...MG/'&/g" | sed "s/, 0..MG/'&/g" | sed "s/0..MG/'/g" | sed "s/, 0....MG/'&/g" | sed "s/0....MG/'&/g" | sed "s/EQ '/EQ /g" | sed "s/%,/%',/g" | sed "s/%','/%',/g" | sed "s/..Federal/', '&/g" | sed "s//null/g" | sed "s/D.H.E. 45,/D.H.E. 45',/g" | sed "s/, 0.'\/ML/', '0.\/ML/g" | sed "s/MG'0/MG0/g" | sed "s/ML'0/ML0/g" | sed "s/'0'\//0\//g" | sed "s/'\//\//g" | sed "s/\/ML',/'ML',/g" | sed "s/UNITS'ML'/UNITS\/ML'/g" | sed "s/BASE'ML'/BASE\/ML'/g" | sed "s/MG'ML'/MG\/ML'/g" | sed "s/IU'ML/IU\/ML/g" | sed "s/UNIT'ML/UNIT\/ML/g" > ~/public_html/Product.sql.txt
• Made a new command sequence
  • Once again, Anu referenced something in class, so I looked at her sequence to understand
• cat Product.txt | sed "s/\*\*Federal Register determination that product was not discontinued or withdrawn for safety efficacy reasons\*\*//g" | sed "s/\*\*Federal Register determination that product was not discontinued or withdrawn for safety 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/\t/*/g" | sed "s/'//g" | sed "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/'null'/null/g" | sed "s/^/insert into Productfile(ApplNo,ProductNo,Form,Dosage,ProductMktStatus,TECode,ReferenceDrug,drugname,activeingred) values (/g" | sed "s/\r$/');/g" > ~/public_html/Product.sql.txt
  • This sequence worked - I feel like I understood doing this sequence much better after working on the first one